Speed up data transfer from Excel

[Rkll]

Hey!

In my VI, I need the contents of 3 Excel files. The VI then takes in filter criteria and uses them to filter all the data and produce graphs. Problem is all 3 Excel files are over 8000 rows deep and 40 colums wide. It takes super long to get the data from one of these spreadsheets, never mind 3.

Im only starting out this project so I wanted to find the best way to approach this.

Is there any way to rapidly transfer all the data back from Excel to LabVIEW?

If I read them once and then wrote them to a binary file for quick access, would that be any better?

I'm running LabVIEW 2009 on Windows XP with the Report Generation Toolkit installed too.

Rkll!

[LV_user1]

you can always read the data from the excel and then stored it in either binary files or array also...u can access these arrays in the rest of the program insted of opening and closing excel again and again

[chilly_charly]

1/ how do you read your data from the files : cell wise in a loop or block wise ?

2/ a way to increase speed would be to use tab-delimited files instead of Excel files as source for your data. 

[Rkll]

The data is orginally written to Excel and these Excel files are stored on the server. I then use the Report Toolkit to open them as templates and the get data VI to retrieve the data.

If i manually save the files into the new Excel 2007 binary workbook format (.xlsb), getting the data is quicker but I don't know of a way to automagically save them to a new format.

[aeastet]

There is an option where you can read the entire contents of a sheet using XML inside Excel. It can come out in a few different XML foramts. You will have to convert the XML to string data or interger data but here is what we do.

[krivan]

Hi,

you can save your files in Excel [Save As...] into text files and then you use the "Read from Spreadsheet" or "Read Text File" VIs. It does even make more sense if you don't use Excel but only text format. In Notepad++ for me the opening a more than 1.5 million rows deep text file does not take longer than 5 seconds and the LabVIEW can handle it straight away...

Take a look at this, I hope it helps.

http://forums.ni.com/t5/LabVIEW/read-and-average-n-data-from-text-file/m-p/1194819#M515175

[Rkll]

krivan,

You're right, it would be alot easier if I didn't use Excel in the first place but that option is not available to me.

The operator saves the data in this excel sheet. An engineer will then open this program input some filters and it will output some graphs. It would be much better if the engineer didn't have to go anywhere near the actual Excel files. Hence why i want this process to be automated.

aeastet,

Would that be faster? Could you perhaps post a VI or a snippet as I don't know how to replicate that code!

[krivan · Accepted solution]

So...if this is the case have you seen either 

this

http://zone.ni.com/devzone/cda/epd/p/id/2231

or 

this

http://zone.ni.com/devzone/cda/epd/p/id/3409

?

[aeastet]

---

aeastet,

Would that be faster? Could you perhaps post a VI or a snippet as I don't know how to replicate that code!

---

This is all active X. It would be very fast. This is how we read all of our Excel files. You can look through the Excel forum in the Breakpoint section the the forum. It will give you ideas on how to do this. Active X is complicated. I gave you the components to do what you need. Look at the Excel open examples that ship with LabVIEW and then you can plug the rest in.

[Rkll]

Thanks guys,

I gave the solution to kirvan cus I actually used the VI's from one of the links posted. But kudos to you too, aeastet, for pointing me down the Active X path too.. Definately going to learn more about it!