how to read big excel file faster?
47 次查看(过去 30 天)
显示 更早的评论
Hi
I have a big excel file like 200MB and I am using xlread(). It is easier to use xlread(). But as the number of variables in the code increases over the time. It takes really long to open the excel file and close again using xlread(). The excel format is attached.
Is there other alternative way to read big excel file faster? Thank you so much. I appreciate it.
5 个评论
bim
2023-1-1
It keeps Excel open in the background while you read data from multiple files.
It has the limitation of only reading contiguous tables and only columns A-Z.
Walter Roberson
2023-1-1
https://www.mathworks.com/matlabcentral/fileexchange/22365-function-for-faster-data-transfer-matlab-excel?s_tid=srchtitle -- reading caching the connection
https://www.mathworks.com/matlabcentral/fileexchange/10465-xlswrite1 -- writing caching the connection
But in sufficiently new versions of MATLAB (r2017b-ish) xlsread() and xlswrite() were modified to cache the connection so these functions are only needed for older releases (or for the case where you want to get ahold of the activex handle for fancier operations.)
采纳的回答
roudan
2018-2-1
更多回答(1 个)
Walter Roberson
2018-2-1
If this is just a one-time read of the whole file, and you are doing this on MS Windows with Excel installed, then xlsread() is about as fast as you can get. xlsread() does have some overhead for matters such as figuring out worksheet names, so it is possible to set up for reading a bit faster by hard-coding that kind of information, but once that is set up, the ActiveX connection works about as fast as could be.
You could also experiment with readtable(). For xls files the binary format is examined and parsed somewhat efficiently, but the code is at the MATLAB level so using ActiveX would typically be more efficient because Excel is compiled. For xlsx files when Excel is not available, readtable() uses regexp() to parse the text after having to go through a series of set-up steps, and although regexp() is one of the faster operations in MATLAB, this is still going to be slower than using ActiveX to Excel.
另请参阅
类别
在 Help Center 和 File Exchange 中查找有关 Spreadsheets 的更多信息
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!