topic JSL - Pulling in files with headers in different rows in Discussions

JSL - Pulling in files with headers in different rows

AmandaStone — Fri, 09 Jun 2023 16:10:26 GMT

I am trying to write JSL to pull in multiple files and concatenate them together, but I have some files where the headers start in row 2 and some files where the header starts in row 3. I am unaware of which files have headers in different rows and I am trying to avoid having to open them all to find out. Is there a way to write JSL to look for the first column header (Time) and start reading there?

Re: JSL - Pulling in files with headers in different rows

Craige_Hales — Fri, 19 May 2023 14:12:26 GMT

CSV-like files? Can the extra lines at the beginning be easily recognized? All blank? Standard keywords? You could use a script to read the first N bytes of the file and decide how many lines CSV import should skip. Look at the LoadTextFile function to do the preliminary open.

Re: JSL - Pulling in files with headers in different rows

AmandaStone — Fri, 19 May 2023 14:15:11 GMT

Yes, they are CSV files. Some have the first line with a date, second line with a VIN, then headers start. Others have first line with date, then headers start. Can you post an example of the script you are referring to?

Re: JSL - Pulling in files with headers in different rows

Craige_Hales — Fri, 19 May 2023 14:47:20 GMT

Something like this.

// pick a test case...
f = savetextfile("$temp\deleteme.csv","1jan2000\!ntime,x,y,z\!n101,1,2,3\!n101,4,5,6");
//f = Save Text File( "$temp\deleteme.csv", "1jan2000\!nVIN\!ntime,x,y,z\!n102,1,2,3\!n102,4,5,6" );

text = Load Text File( f );
// count newlines before "time"

newline = "\!n\!r" | "\!r\!n" | "\!n" | "\!r";
n = 0;
Pat Match( // there are a lot of ways you could count newlines before "time"
	text, // use the pattern matcher to loop through the text 
	Pat Pos( 0 ) + 
	Pat Repeat(
		("time" + Pat Rem()) // found "time", skip to the end
		| // OR...skip to the end of the line and count it
		(Pat Break( "\!n\!r" ) + newline + Pat Test( n += 1; 1; ))
	)
);
Show( n );
HEADERLINE = n + 1;
FIRSTDATALINE = n + 2;
Open(
	f,
	columns(
		New Column( "time", Numeric, "Continuous", Format( "Best", 12 ) ),
		New Column( "x", Numeric, "Continuous", Format( "Best", 12 ) ),
		New Column( "y", Numeric, "Continuous", Format( "Best", 12 ) ),
		New Column( "z", Numeric, "Continuous", Format( "Best", 12 ) )
	),
	Import Settings(
		End Of Line( CRLF, CR, LF ),
		End Of Field( Comma, CSV( 0 ) ),
		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( HEADERLINE ),
		First Named Column( 1 ),
		Data Starts( FIRSTDATALINE ),
		Lines To Read( "All" ),
		Year Rule( "20xx" )
	)
);

Re: JSL - Pulling in files with headers in different rows

AmandaStone — Fri, 19 May 2023 17:20:12 GMT

Thank you so much! This worked perfect for me and saved me so much time opening all the CSV files to edit them!