Read entire row or column from Excel

[Skydyvr]

I've been using the following attachment to inport data from excel. This works if I want the whole sheet. However I have an application that has 185 columns by 125,000 rows (260 MB file). If I try to read that file, I get an out of memory error. How can I read a single column and/or single row from an excel file?

And no, purchasing an add-on toolkit is not an option.

[Liz Pérez Aquino]

Hello Skydyvr

Why don't you try this http://zone.ni.com/devzone/cda/epd/p/id/2231

I hope this can help you 

Regards

[smercurio_fc]

You need to set the range if you want to read a subset of the sheet. Replace the UsedRange property node with an Invoke node, and select the "Range" method. This has to parameters for the two cells defining the range. The rest of the code can stay as is.

You may want to peruse the Excel thread for lots of examples, as well as a user-written toolkit. NOTE: DO NOT POST QUESTIONS IN THE EXCEL THREAD.

[Skydyvr]

Thanks Liz, that does what I want. However, it is not as quick as what I would have hoped, with it reading in only 1 row. Using my file, it takes 1 min 18 seconds to read in a single row (or col). Way too long of a delay when the idea is to plot various columns against each other on a graph.

[smercurio_fc]

If you read only one row at a time it will be glacial because of the slowness of needing to go through ActiveX. Did you try the approach I suggested and setting a range so you simply read out a smaller chunk?

Also, is the data you're reading numerical, or string? If it's numerical, then don't use string arrays as your datatype. Use a numeric array.

[Charles_CLA]

Keep in mind that using ActiveX to get this data actually opens Excel and transfers the data between the programs. This is evident by opening task manager and looking for the Excel.exe process. I presume with that large of an Excel file that the time delay you are seeing is the time for Excel to open the file and not the time for the transfer of data from Excel to LabVIEW. Pulling data via ActiveX into LabVIEW is HIGHLY error prone and should be avoided if possible. Have you considered using SQL and querying the data without using ActiveX? I showed our local Archtects forums this as part of my demonstration for the January meeting. The code can be found here:

https://decibel.ni.com/content/docs/DOC-22031

Thanks,

Charles

[Skydyvr]

Did you try the approach I suggested and setting a range so you simply read out a smaller chunk?

Yes, I believe that is what the vi Liz suggested accomplished. I suspect as Charles says that the delay is due to Excel having to open the file before extracting the data. I haven't timed how long it takes Excel to open the file, but it is a long time. In my naive world, I was hoping that if I just asked for a single row or column, I would not have to open the whole file and somehow it knew how to extract just that information.

Also, is the data you're reading numerical, or string? If it's numerical, then don't use string arrays as your datatype. Use a numeric array.

It is both. The first row is string (column headers), but the rest of the file is numerical (data).

Have you considered using SQL and querying the data without using ActiveX?

No, I'm not very familiar with SQL. I'll check that out when I get time. I'm working on this project in my spare time.

Thanks everyone for your help.