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

Question

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"
);

caojianjack · Answer

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.

briancorcoran · Answer

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.

Brian Corcoran

JMP Development