Choose Language Hide Translation Bar
Level II

Read/omit particular columns from a .csv file


I would like to read in particular columns from a .csv file. The .csv files (which I currently read into jmp and concatonate) have hundreds of columns and hundreds of thousands of lines so the time to read the files into JMP becomes very large and also the JMP file becomes so big that it eventually freezes. As not all of the columns in the CSV are required for analysis I was wondering if a JSL script could target specific columns and therefore reduce the load time (and JMP file size). I have included a very much  simplified example below and simply would like to read in the .csv file below but omit the "sample_no" and "scene__max" columns so the jmp table only has 3 columns. (In reality I will be looking to exclude ~100 columns by name)

Any inputs on this would be much appreciated.


Level VIII

Re: Read/omit particular columns from a .csv file

                   In my experience, it is always better to read the file into JMP and perform filtering out of columns or rows after the data is in a JMP data table. This would be especially true when the data you want to exclude is not in a squential order. Most programming languages have optimized csv/xlsx readers built in. Unless you are willing to re-shape your table in such a way that , you can move all the columns to be filtered to one end and read the columns on the other side, I don't think filtering out alternate columns might be really helpful in this case. 

                  Additionally, when you are opening the .csv file using the Open() are you making it a private data table ? Making a data table private while opening a file makes it a lot faster. 

Super User

Re: Read/omit particular columns from a .csv file

I looked but did not find anyway to pick and choose which columns to read in from and Excel workbook.  There are options where you can select the starting column and ending column  number, and starting and ending row number

Data Starts on Row( 2 ), Data Starts on Column( 1 ), Data Ends on Row( 0 ), Data Ends on Column( 0 )
Level II

Re: Read/omit particular columns from a .csv file


Thank you for your feedback I may have to look also at some pre-processing. 


Re: Read/omit particular columns from a .csv file

If you could generate .XLSX instead of .CSV files, maybe there's a chance to use an ODBC connection to Excel and use JMP's Query Builder (and SQL) to use a subset of the columns for importing?

And depending on what the possibilities are for restructuring the data (if any), you should be aware of the virtual join in JMP (if you are not already).

Staff (Retired)

Re: Read/omit particular columns from a .csv file

from the text import wizard for CSV files, on the 2nd page, mark the column excluded:

red slash circlered slash circle

When you import, the script has the code you need to do it again:

Omitted ColumnOmitted Column

Article Labels

    There are no labels assigned to this post.