865507_en-US

liliconcom

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

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.

Basic Data Analysis and ModelingData AccessRe: Importing Data from txt file with lines of data every 10th row

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?

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

I posted the sample file. Thanks for the reply

 

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

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="">= 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 )
	);
);</PLACE>

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

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

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

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

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

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

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

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 = //"

"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 );