JSL Table import - Selecting correct row as headers when desired row number is variable

SamKing

I have a script that uses an input list to pull files from a directory.  The file format is unusual so it takes some manipulation and each file is on the order of 60Mb so imports can take more time than I would want.  My issue is that most files have the desired header row on row 15 but about 10% of the files have a header that could be anywhere from row 10-14 and there is no way to know until i open the file.

 

My current solution is to have the script open the first 20 rows, search for the row that contains the headers and assign that row number to a variable, close that dt, then reopen but using the variable as the header and variable + 1 as the first line.  It works but it means I'm opening these files twice, though one is a limited preview only.  

 

1) Is there a better way to do this? 

2) And while I'm asking, is there a way to import a table but only import the columns that I want, based on name?  My files have about 1000 columns and I only need about 15 of them.

Craige_Hales

1) I think two imports is probably the easiest way and can be made faster...

2) You'll need to specify more than just the columns to import, but the speedup will be worth it...

 

use the text import wizard to mark the unwanted columns

 

 

Grab the source script, it has the JSL to omit columns

(for 1000 omitted columns it might be easier to edit the JSL than exclude with the GUI.)

 

Now, you can tweak the source script. Below it reads the first 100 lines. It is fast because (1) everything about the columns is specified and (2) it stops after 100 lines. For this data I should have specified Labels(0) and DataStarts(1).

 

 

Open(
	"f:\logOriginalAntenna.txt",// 100MB file takes about 2 seconds to load 100 lines
	columns(
		Omitted Column( . ),// use the import wizard to
		Omitted Column( . ),// get a source script with
		Omitted Column( . ),// omitted columns
		New Column( "c000004",
			Numeric,
			"Continuous",
			Format( "yyyy-mm-ddThh:mm:ss", 23, 3 ),
			Input Format( "yyyy-mm-ddThh:mm:ss", 3 )
		),
		New Column( "c000005", Character, "Nominal" )
	),
	Import Settings(
		End Of Line( CRLF, CR, LF ),
		End Of Field( Tab, Comma, CSV( 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( 1 ),
		Data Starts( 2 ),
		Lines To Read( 100 ),// specify the number of rows
		Year Rule( "20xx" )
	)
)

If your files always have the same column ordering, then specifying the format explicitly (above) will mean JMP does not need to study the file before importing it. I think JMP still studies the entire file even if you stop at 100 lines, so this could reduce the processing time, a lot.

 

If the files don't always have the same number of columns or order of columns, this won't work. You might be able to import the table, fix up the column names and data types, delete rows you don't want...using a single import.