How can I greatly improve the speed of reading Excel files when programming with labview

[夜神]

When I read Excel files with labview，the speed is very slow,is there some good solutions to improve the speed.

[Bob_Schor]

Without a clue to what you are doing, how you are doing it, a sample of your data, and the actual VI you are using that is "slow", it is very difficult to make suggestions.

One particular area of confusion is the word "Excel".  I'd estimate that at least 75% of the Posts on this Forum that talk about "Excel files" mean "Comma-separated Values" (.csv) files, ones that have an "Excel-appearing" icon courtesy of Microsoft, and that are pure Text files easily read and written by LabVIEW.  A "real" Excel file has the extension .xlsx (or, for very old Excel files, .xls), and require either the Report Generation Toolkit or Third Party software to read.

For more help, do all of the steps in the first paragraph, please.

Bob Schor

[winterishere8]

Are you using the add-on tool-kit, or you using Read from Spreadsheet from files palette?

[Ben_Manthey · Accepted solution]

Newer Excel (.xlsx) are really compressed xml files. You can change the extension from .xlsx to .zip and unzip them with any unzipping tool. Inside you'll find a bunch of directories of xml files. The problem with writing to or from these files is that they need to be decompressed and then recompressed for every write or read. This is, as you might imagine, very slow. There really isn't any way to speed this up. 

There is the older .xls file format which is a binary format that doesn't use any sort of compression and is much faster to read and write. The main problem with it is that can only have ~65,000 rows of data per sheet. LabVIEW reads and writes these files just fine with the Report Generation Toolkit. If you don't need more that 65,000 rows I recommend this file format.

There is a third and much less well know solution. .xlsb files were introduced at the same time a .xlsx files. .xlsb is a binary format like .xls files so it's nice and fast to read and write to and it supports ~1,000,000 rows of data per sheet. Unfortunately it's not very well supported by anybody. If you want to use this format you'll have to write custom ActiveX code that uses this format. 

[夜神]

Thank you very much,I think the xlsb file may be a good solution.I will try to write ActiveX code to use this format.

[夜神]

Thank you.I use ActiveX code.I mean read the xlsx files.

[夜神]

Thank you for your advise.I mean read xlsx files.I'm not very good at expressing，I'll describe the problem more clearly next time.

[TroyK · Accepted solution]

This was annoying me too. I had a reasonably fast solution that worked ok using ADODB (treat the excel file like a database) but after an update to MS office it stopped closing the reference to the file properly or something and if a user tried to open a file again it would crash the LabVIEW application very ungracefully.  I tried everything to fix it and gave up.

Here is a link to my example code to read xlsx files directly.  It's fairly basic but it does what I need it to.

Directly read .xlsx files using unzip to byte stream and xml processing

[夜神]

I use your “Excel-Read-XML_direct.vi” to read my xlsx files,It is really very very fast! Thank you so much.I will read the vi program carefully,it's a good solution.