633269_en-US

liliconcom

JSL - Pulling in files with headers in different rows

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?

Data Blending and CleanupRe: JSL - Pulling in files with headers in different rows

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

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

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

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