topic Read a CSV and text file with JSL in Discussions

Read a CSV and text file with JSL

RV1 — Fri, 14 Jun 2019 00:23:07 GMT

I have a CSV file that contains parameter names and tolerances. I have another text file that contains raw data. I am trying to write a script in JSL to read both CSV and text files and then apply the screening criteria in CSV file to the data in text file.

CSV file format is liek below:

# Parameter Tolerance

1 Height 30

2 Diameter 50

TXT file format is

ID Height Diameter

1AA 50 20

1BB 20 30

I want to create a text file with ID that failed the screening criteria.

For example in the above case 1AA fails Height screening as the value 50 is greater than tolerance 30. I want a text file created with 1AA written in it.

Any guidance will be highly appreciated.

So far I only know how to open a CSV or text file but not able to figure out how to save tolerances in a variable and apply the screening condition to data.

txt_path = Pick File( "Pick a file" );

dt_screen = Open(
	txt_path, 

	Column Names are on line( 1 ),
	End of Field( "Comma" ), 

	Data Starts on Line( 2 ),
	End Of Line( "CRLF" )
);

Re: Read a CSV and text file with JSL

txnelson — Fri, 14 Jun 2019 01:51:14 GMT

Here is a script that works with your sample data, and should work with your larger data tables.

Names default to here(1);

// Open the data file
txt_path = Pick File( "Pick a Data File" );
dt_data = Open(
	txt_path,
	columns(
		New Column( "ID", Character, "Nominal" ),
		New Column( "Height", Numeric, "Continuous", Format( "Best", 12 ) ),
		New Column( "Diamiter", Numeric, "Continuous", Format( "Best", 12 ) )
	),
	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( "All" ),
		Year Rule( "20xx" )
	)
);

// Open the Tolerance data
txt_path = Pick File( "Pick a Tolerance File" );
dt_Tolerance = Open(
	txt_path,
	Worksheets( "Sheet1" ),
	Use for all sheets( 1 ),
	Concatenate Worksheets( 0 ),
	Create Concatenation Column( 0 ),
	Worksheet Settings(
		1,
		Has Column Headers( 1 ),
		Number of Rows in Headers( 1 ),
		Headers Start on Row( 1 ),
		Data Starts on Row( 2 ),
		Data Starts on Column( 1 ),
		Data Ends on Row( 0 ),
		Data Ends on Column( 0 ),
		Replicated Spanned Rows( 1 ),
		Replicated Spanned Headers( 0 ),
		Suppress Hidden Rows( 1 ),
		Suppress Hidden Columns( 1 ),
		Suppress Empty Columns( 1 ),
		Treat as Hierarchy( 0 ),
		Multiple Series Stack( 0 ),
		Import Cell Colors( 0 ),
		Limit Column Detect( 0 ),
		Column Separator String( "-" )
	)
);

// Get the column names in the data file
colNames = dt_data << get column names(string,continuous);

// Loop across the columns and get the Tolerances and then go through all
// of the rows in the data table and select the ones that fail
For( theCol = 1, theCol <= N Items(colNames), theCol++,
	theToleranceRow = dt_Tolerance << get rows where( colNames[theCol] == dt_Tolerance:Parameter);
	If( N Rows( theToleranceRow ) >0,
		// Change theToleranceRow from a matrix to a scaler value
		theToleranceRow = theToleranceRow[1];
		For(theRow = 1, theRow <= N Rows( dt_data ), theRow++,
			If( column(dt_data,colNames[theCol])[theRow] > dt_Tolerance:Tolerance[theToleranceRow],
				current data table(dt_data);
				Row State( theRow ) = Selected State( 1 );
			)
		)
	)
);

// Create the text string to save to the text file for all failing IDs
failedRows = dt_data<<get selected rows;
failedText = "";
If( N Rows( failedRows ) > 0,
	failedText = dt_data:ID[failedRows[1]];
	For( selectedRow = 2, selectedRow <= N Rows(failedRows), selectedRow++,
		failedText = failedText || "\!n" || dt_data:ID[failedRows[selectedRow]]
	)
);

// Save the failed text string to a file
Save Text File( "path\to\failed.txt", failedText );

Re: Read a CSV and text file with JSL

Craige_Hales — Fri, 14 Jun 2019 02:12:32 GMT

Its late, but here's what I came up with. Jim caught the part about a text file output, I missed that...

csvfile = Save Text File("$temp/x.csv",
"#     Parameter    Tolerance
1     Height           30
2     Diameter       50
"
);
 
txtfile = Save Text File("$temp/y.txt",
"ID    Height    Diameter
1AA    50          20
1BB     20         30
1cc     20         90
"
);

dtparms = Open(csvfile,
    Import Settings(
        End Of Line( CRLF, CR, LF ),
        End Of Field( Spaces, Space, CSV( 0 ) ),
        Labels( 1 ),
        Column Names Start( 1 ),
        Data Starts( 2 )
    )
);

// get the parameters
parameterNames = dtparms[0, "parameter"]; // {"Height", "Diameter"}
parameterValues = dtparms[0, "tolerance"]; // [30, 50]

dtdata = Open(txtfile,
    Import Settings(
        End Of Line( CRLF, CR, LF ),
        End Of Field( Spaces, Space, CSV( 0 ) ),
        Labels( 1 ),
        Column Names Start( 1 ),
        Data Starts( 2 )
    )
);

For( iPname = 1, iPname <= N Items( parameterNames ), iPname += 1,
    col = Column( parameterNames[iPname] ); // Column( "Diameter" )
    val = parameterValues[iPname]; // 50
    dtdata << selectwhere( dtdata:col > val, current selection( "extend" ) );
);
dtdata<<select columns("ID");
dtresult = dtdata << subset( selected rows( 1 ), selected columns(1) );

// you might want...
//close(dtdata,nosave);
//close(dtparms,nosave);
;

Re: Read a CSV and text file with JSL

RV1 — Fri, 14 Jun 2019 09:00:39 GMT

Thank you so much. Your solution works perfectly, just the way I needed. I am trying to use it on a larger dataset with more rows and columns. Hopefully I will be able to quickly modify the script. Greatly appreciate your help.