Subscribe Bookmark RSS Feed

Open Database() problem on large Excel file with more than 65536 rows

caojianjack

New Contributor

Joined:

Nov 30, 2016

I use Open Database() function to load data from Excel file, but I encounter difficulty to load large Excel file. I found that when the row number in the Excel exceeds 65536, the script cannot read the whole data set. My script is like below. Do I miss something here?

 

dt = Open Database(
"DSN=Excel Files;
DBQ=testing.xlsm;
DefaultDir=C:\Users\jicao\Desktop\;",
"SELECT * FROM `Sheet1$`",
"Output testing"
);

2 REPLIES
briancorcoran

Joined:

Jun 23, 2011

If you are really importing all the data, you would be much better off using the direct open (File Open, or JSL open) for Excel files built into JMP.
If you really need to use the ODBC driver, I would make sure that it is set to a modern workbook format.

 

Capture.PNG

 

Brian Corcoran

JMP Development

 

 

 

caojianjack

New Contributor

Joined:

Nov 30, 2016

Thanks Brian! Your post just solved my issue! I need to use Open Database() because I need some SQL statement to filter the column and rows in the first place. Setting up a modern workbook format (excel 12.0) load all the rows which is beyond the 65536 limitation for pre-2007 excel version.