readtable successful on .xlsx file but fails on .xlsb file

readtable works fine reading an .xlsx file, but hangs when reading an .xlsb file.

The following works perfectly:

dat = "C:\DataDirectory\DataFile.xlsx";
T = readtable(dat);

I read in the documentation for readtable that for large Excle files reading the .xlsb format is recommended, so I saved the Excel file in .xlsb format and I tried:

dat = "C:\DataDirectory\DataFile.xlsb";
T = readtable(dat);

but this opens the Excel file and then freezes both Excel and Matlab. The only way to recover was to end the Excel process via TaskManager.

Matlab version is 2019b.

Excel version: Microsoft® Excel® for Microsoft 365 MSO (Version 2302 Build 16.0.16130.20374) 32-bit

I can continue to work with the .xlsx format, but I'm still curious if I'm doing something wrong, or if there is a known issue. It would be nice to use the .xlsb format if it in fact loads faster.

Answer 1

[Cris LaPierre]

No, you are not doing anything wrong. *.xlsb files are officially supported in readtable in R2019b.

readtable determines the file format from the file extension:

• .txt, .dat, or .csv for delimited text files
• .xls, .xlsb, .xlsm, .xlsx, .xltm, .xltx, or .ods for spreadsheet files

How were your xlsb files created? Can you attach one to your post for others to test?

You might consider adding the 'UseExcel' flag.

dat = "C:\DataDirectory\DataFile.xlsb";
T = readtable(dat,'UseExcel',true);

[RM]

Cris, thank you for the quick response.

The .xlsb file was created from the .xlsx file from within Excel by resaving as type .xlsb.

Using 'UseExcel',true did not resolved the issue.

I can't send the original Excel files due to containing non-public information, but I did create a smaller test data set. With those smaller tables both the .xlsx and .xlsb files were read by readtable so from those the problem was not reproduced. I will have to dig further to see if I can create a test data set that reproduces the problem, however, since using the .xlsx file works, I will use that and digging further probably won't be a high priority.

For reference, the one difference between reading the .xlsx and .xlsb file types in the smaller test data was that readtable still opened the .xlsb files in Excel, but did not open the .xlsx files in Excel. When opened in Excel there was a prompt dialog regarding enabling macros or not. Perhaps that is part of the issue, but that dialog appears in both the original data set and the test sets, (Excel auto-opening a file a macros... which is known and expected), so it seems it's probably not the issue.

Thanks again for your earlier quick response and thoughts.

[Cris LaPierre]

If your information is confidential, then I suggest working directly with support. You can reach out to them here: https://www.mathworks.com/support/contact_us.html

[RM]

Thanks. I'll do that.