Subscribe Bookmark RSS Feed

How to Open() only specific columns

djhanson

Community Trekker

Joined:

Jun 23, 2011

Hi, if I have a large JMP data table is there a way I can send a message to Open() that only loads in specific column(s)?  This would greatly reduce file loading time for a large file.  For example if I have this file:

Open("c:\temp\fruit.jmp");

Lets say fruit.jmp contains 200 columns of different fruit types and I may need access to all of these, but for the moment I only need load in one column "Apple" thereby reducing file load time.  Doing a subsequent Subset() defeats the purpose, as the large data file needs to be opened first and the objective here is to reduce file load times by only loading in what column(s) are needed.  I was hoping something like the following would work, which doesn't of course:

Open("c:\temp\fruit.jmp",columns("Apple"));

suggestions?  thx... dj

1 ACCEPTED SOLUTION

Accepted Solutions
Solution

Ok, I think I may have figured this out.  I eventually found a helpful post from the past.  I searched all of the Open() attributes in the scripting guide and there is no way to do this without using the Open Database() command instead.  Basically, just need to convert the source .jmp to an .xls file first.  Just wanted to post this in case it helps anyone else out.  dj

http://communities.sas.com/message/50379#50379

So first I convert the "fruit.jmp" into a "fruit.xls".  That way I can have JSL use the Excel ODBC DSN connection and a little bit of SQL code to pull only the column(s) I need.  So lets say my Excel data table has a worksheet "Sheet1" and within it a column called "Apple" and I only want to load this column and none of the others in this worksheet in order to save time.  This code below does the trick:

dt=Open Database("DSN=Excel Files;DBQ=c:\temp\fruit.xls;DriverId=790;MaxBufferSize=2048;PageTimeout=5;","SELECT Apple FROM `Sheet1$`" , "Fruit Table" );

1 REPLY
Solution

Ok, I think I may have figured this out.  I eventually found a helpful post from the past.  I searched all of the Open() attributes in the scripting guide and there is no way to do this without using the Open Database() command instead.  Basically, just need to convert the source .jmp to an .xls file first.  Just wanted to post this in case it helps anyone else out.  dj

http://communities.sas.com/message/50379#50379

So first I convert the "fruit.jmp" into a "fruit.xls".  That way I can have JSL use the Excel ODBC DSN connection and a little bit of SQL code to pull only the column(s) I need.  So lets say my Excel data table has a worksheet "Sheet1" and within it a column called "Apple" and I only want to load this column and none of the others in this worksheet in order to save time.  This code below does the trick:

dt=Open Database("DSN=Excel Files;DBQ=c:\temp\fruit.xls;DriverId=790;MaxBufferSize=2048;PageTimeout=5;","SELECT Apple FROM `Sheet1$`" , "Fruit Table" );