LabVIEW

cancel
Showing results for 
Search instead for 
Did you mean: 

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

Solved!
Go to solution

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

0 Kudos
Message 1 of 9
(349 Views)

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

0 Kudos
Message 2 of 9
(337 Views)

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


Kudos are the best way to say thanks Smiley Happy
0 Kudos
Message 3 of 9
(286 Views)
Solution
Accepted by 夜神

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. 

—Ben
Prevent your computer from sleeping programmatically!
Use Power Requests
Download from GitHub

Message 4 of 9
(260 Views)

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.

0 Kudos
Message 5 of 9
(239 Views)

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

0 Kudos
Message 6 of 9
(235 Views)

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.

0 Kudos
Message 7 of 9
(231 Views)
Highlighted
Solution
Accepted by 夜神

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

Troy - CLD "If a hammer is the only tool you have, everything starts to look like a nail." ~ Maslow/Kaplan - Law of the instrument
Message 8 of 9
(200 Views)

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.

0 Kudos
Message 9 of 9
(156 Views)