Looking for JSL Scripting suggestions on how to import a .CSV file with multiple embedded headers.

Question

Hello,

I'm creating a JMP JSL script to import a specific format of .CSV and graph the data.  The problem I have is that the .CSV has header lines interspersed with the data.  Think of it as multiple CSV's concatenated together.  The good news is that all of the column names are the same.  What is the recommendation on how to import this type of CSV and remove the extra headers?  Is there a way to remove these rows during import?  The lines can also be removed with a RowSelection and DeleteRows after import.  The problem with the RowSelection/DeleteRows method is that the data types for all the columns are set to Nominal (String data type) during import.  I would prefer not to have to then specifically name all of the columns (> 100) that need to be numeric.

I'm using JMP version 18 on Windows.

What is the best practice for something like this?

--Jeff

jthi · Answer

Usually you have two options:

* Handle it as text
* Or handle it as JMP table

Both will have the same idea: remove the extra lines to build final table. If your data has good quality otherwise and it is loaded nicely to JMP, I would first try to handle it as a table and just remove the extra rows and then fix column data/modelling types (you can perform a check to see what they should be). If you can provide small example, we can give proper examples.

Scripters Club 2024: Session 2 - Preparing Unstructured Incoming Data for Analysis might also give some ideas and it might even cover case like this.

txnelson · Answer

Someone will probably have a better solution, but the way I have approached this in the past, is that I read in the .csv file as one column per line.

Then  find and delete all of the embedded header line.

Save the modified table to a temporary .csv file

and then read the .csv file back in, specifying line 1 as the headers