Importing Data from txt file with lines of data every 10th row

[#1]

New jmp user here. I have a large text file with data on every 10th row starting on line 6. My column headers are on line 4 and I want to use fixed width fields, but it shifts the first 3 headers to the right when I select my own column width.

[#2]

Given your data I suspect it would be easier to just specify headers starting on row 3 and data on row 6 and then let JMP defaults read the data in.  After that, you should be able to use JMP's data manipulating capabilities to get rid of unnecessary rows and then to convert the columns to numeric.

Could you provide a sample csv file so the Community can see what they can come up with?

[#3]

I posted the sample file. Thanks for the reply

[#4 · Accepted solution]

Here is a simple script that reads in the data 

Names Default To Here( 1 );
dt = Open(
	"<place the path to your txt file here",
	columns(
		New Column( "SampleID", Character, "Nominal" ),
		New Column( "Density", Character, "Nominal" ),
		New Column( "Weight", Character, "Nominal" ),
		New Column( "Gage", Character, "Nominal" ),
		New Column( "Peak", Character, "Nominal" ),
		New Column( "Perm", Character, "Nominal" ),
		New Column( "Pc@10", Character, "Nominal" ),
		New Column( "kG", Character, "Nominal" ),
		New Column( "Pc@13", Character, "Nominal" ),
		New Column( "kG 2", Character, "Nominal" ),
		New Column( "Pc@15", Character, "Nominal" ),
		New Column( "kG 3", Character, "Nominal" ),
		New Column( "Pc@17", Character, "Nominal" ),
		New Column( "kG 4", Character, "Nominal" ),
		New Column( "Pc@18", Character, "Nominal" ),
		New Column( "kG 5", Character, "Nominal" ),
		New Column( "Pz@10", Character, "Nominal" ),
		New Column( "kG 6", Character, "Nominal" ),
		New Column( "Pz@13", Character, "Nominal" ),
		New Column( "kG 7", Character, "Nominal" ),
		New Column( "Pz@15", Character, "Nominal" ),
		New Column( "kG 8", Character, "Nominal" ),
		New Column( "Pz@17", Character, "Nominal" ),
		New Column( "kG 9", Character, "Nominal" ),
		New Column( "Pz@18", Character, "Nominal" ),
		New Column( "kG 10", Character, "Nominal" ),
		New Column( "FF@10", Character, "Nominal" ),
		New Column( "FF@13", Character, "Nominal" ),
		New Column( "FF@15", Character, "Nominal" ),
		New Column( "FF@17", Character, "Nominal" ),
		New Column( "FF@18", Character, "Nominal" ),
		New Column( "PcPl", Character, "Nominal" ),
		New Column( "10", Numeric, "Continuous", Format( "Best", 10 ) ),
		New Column( "PcPl 2", Character, "Nominal" ),
		New Column( "13", Numeric, "Continuous", Format( "Best", 10 ) ),
		New Column( "PcPl 3", Character, "Nominal" ),
		New Column( "15", Numeric, "Continuous", Format( "Best", 10 ) ),
		New Column( "PcPl 4", Character, "Nominal" ),
		New Column( "17", Numeric, "Continuous", Format( "Best", 10 ) ),
		New Column( "PcPl 5", Character, "Nominal" ),
		New Column( "18", Numeric, "Continuous", Format( "Best", 10 ) ),
		New Column( "PzPl", Character, "Nominal" ),
		New Column( "10 2", Numeric, "Continuous", Format( "Best", 10 ) ),
		New Column( "PzPl 2", Character, "Nominal" ),
		New Column( "13 2", Numeric, "Continuous", Format( "Best", 10 ) ),
		New Column( "PzPl 3", Character, "Nominal" ),
		New Column( "15 2", Numeric, "Continuous", Format( "Best", 10 ) ),
		New Column( "PzPl 4", Character, "Nominal" ),
		New Column( "17 2", Numeric, "Continuous", Format( "Best", 10 ) ),
		New Column( "PzPl 5", Character, "Nominal" ),
		New Column( "18 2", Numeric, "Continuous", Format( "Best", 10 ) ),
		New Column( "Grain", Character, "Nominal" ),
		New Column( "Direction", Character, "Nominal" ),
		New Column( "Time", Character, "Nominal" )
	),
	Import Settings(
		End Of Line( CRLF, CR, LF ),
		End Of Field( Spaces, Space, CSV( 0 ) ),
		Treat Leading Zeros as Character( 1 ),
		Strip Quotes( 1 ),
		Use Apostrophe as Quotation Mark( 0 ),
		Use Regional Settings( 0 ),
		Scan Whole File( 1 ),
		Treat empty columns as numeric( 0 ),
		CompressNumericColumns( 0 ),
		CompressCharacterColumns( 0 ),
		CompressAllowListCheck( 0 ),
		Labels( 1 ),
		Column Names Start( 4 ),
		First Named Column( 1 ),
		Data Starts( 6 ),
		Lines To Read( "All" ),
		Year Rule( "20xx" )
	)
);

dt << select where( Is Missing( Num( :sampleID ) ) == 1 | Is Missing( Num( :density ) ) );
dt << delete rows;

colNames = dt << get column names( string );

For( i = N Items( colNames ), i >= 1, i--,
	col = colNames[i];
	Column( col ) << set datatype( numeric ) << set modeling type( continuous );
	If( Col Number( As Column( col ) ) == 0,
		dt << delete columns( col )
	);
);

[#5]

You're the goat. This example will help teach me how to use script in this application.

[#6]

Could I have another row of column headers under the first header for units? units are on line 5. Thank you!!

[#7]

I have modified the JSL that after the data table is created as it was in my initial response, I then reread the .txt file, and to read the units.  It then add the units as Column Properties to the columns in the full data table.

Note: The units are not part of the column name.  They are a column property.  However, the units will be displayed automatically with the column.

Names Default To Here( 1 );

theFile = //"<place the path to your txt file here",

"L:\Documents\Discussion Group/cschultz32.txt";

dt = Open( theFile,

Import Settings(

End Of Line( CRLF, CR, LF ),

End Of Field( Spaces, Space, CSV( 0 ) ),

Treat Leading Zeros as Character( 1 ),

Strip Quotes( 1 ),

Use Apostrophe as Quotation Mark( 0 ),

Use Regional Settings( 0 ),

Scan Whole File( 1 ),

Treat empty columns as numeric( 0 ),

CompressNumericColumns( 0 ),

CompressCharacterColumns( 0 ),

CompressAllowListCheck( 0 ),

Labels( 1 ),

Column Names Start( 4 ),

First Named Column( 1 ),

Data Starts( 6 ),

Lines To Read( "All" ),

Year Rule( "20xx" )

)

);

dt << select where( Is Missing( Num( :sampleID ) ) == 1 | Is Missing( Num( :density ) ) );

dt << delete rows;

colNames = dt << get column names( string );

For( i = N Items( colNames ), i >= 1, i--,

col = colNames[i];

Column( col ) << set datatype( numeric ) << set modeling type( continuous );

If( Col Number( As Column( col ) ) == 0,

dt << delete columns( col )

);

);

// Read in the units and apply them

dtUnits = Open( theFile,

Import Settings(

End Of Line( CRLF, CR, LF ),

End Of Field( Spaces, Space, CSV( 0 ) ),

Treat Leading Zeros as Character( 1 ),

Strip Quotes( 1 ),

Use Apostrophe as Quotation Mark( 0 ),

Use Regional Settings( 0 ),

Scan Whole File( 1 ),

Treat empty columns as numeric( 0 ),

CompressNumericColumns( 0 ),

CompressCharacterColumns( 0 ),

CompressAllowListCheck( 0 ),

Labels( 1 ),

Column Names Start( 4 ),

First Named Column( 1 ),

Data Starts( 5 ),

Lines To Read( "All" ),

Year Rule( "20xx" )

)

);

dtUnits << select where(row()>1);

dtUnits << delete rows;

colNames = dt << get column names( string, numeric );

For Each( {col}, colNames,

units = Column( dtUnits, col )[1];

If( Is Missing( units ) == 0,

units = Substitute( units, "(", "", ")", "" );

Eval( Eval Expr( Column( dt, col ) << set property( "units", Expr( units ) ) ) );

);

);

close( dtUnits, nosave );

[#8]

I would do something like txnelson did suggest, pull in data like this

Then use JMP to clean it up. You could for example create new column with a formula

Mod(Row(), 10)

right click and select all rows with 1, invert selection and then delete rows

After invert

Then remove empty columns and change data formats to numeric continuous as needed.

This would be the basic idea (unless you wish to create JSL for it) but there seems to be some fixing to be done to get correct headers