Open part of CSV with filter

tom_abramov

Hi,

What is the way to open a filtered part of CSV?

I have 300 Mb CSV but need only 10 Mb from it.

Please provide an example for Big Class.csv where height > 50 and Age = 15;

Thanks.

Craige_Hales

You can do it with an ODBC driver. It is complicated, and I'm not sure what kind of performance you can expect. The JSL first, then the complicated setup steps. I made big class.csv in the samples\import directory; the bigclass.txt did not work on my first try, probably because of the extension or not having commas. There might be a way to fix that in the connection string.

New SQL Query(
	Connection(
		"ODBC:DSN=CSV;DBQ=C:\SPACE\SAS\JMP\15\SAMPLES\IMPORT DATA;DefaultDir=C:\SPACE\SAS\JMP\15\SAMPLES\IMPORT DATA;DriverId=27;FIL=text;MaxBufferSize=2048;PageTimeout=5;"
	),
	QueryName( "Big Class.csv" ),
	Select(
		Column( "name", "t1" ),
		Column( "age", "t1" ),
		Column( "sex", "t1" ),
		Column( "height", "t1" ),
		Column( "weight", "t1" )
	),
	From( Table( "Big Class.csv", Alias( "t1" ) ) ),
	Where(
		Custom( "t1.age=15 and t1.height>50", UI( Custom( Base( "Continuous" ) ) ) )
	)
) << Run

You'll have to set up a DSN on your computer. You probably don't have the driver. Download and install.

I found those by searching for the Microsoft Access ODBC CSV Driver

Install the 64 bit exe:

This took a minute

Set it up. Type ODBC in the windows start menu to find the 64 bit admin tool.

Use it in JMP. I stumbled about, some of the pictures may be less correct; the good stuff is at the end. File->Database is the starting point.

I think the file needs a CSV extension, but I didn't do enough experiments. With a CSV extension, and using commas, and NOT using quotation marks around numbers, it works. It looks like quotation marks will import strings, but there might be a way to fix that too.