Read very very large excel file into 2d array (strings or varient)

[Maddox731]

Hi all,

Long time user, first time poster on these boards.

Looking at the copius amounts of great info related to reading Excel data from .xls files into labview, i've found that every one i've found from various people use the ActiveX method (WorkSheet.Range) which two strings are passed, namely excel's LetterNumber format to specify start and end.

However, this function does not work when trying to query huge amounts of information. The error returned is "Type Mismatch, -2147352571" I have a very large excel sheet i need to read data from and then close the excel file (original file remains unchanged). However this file is gigantic (don't ask me, I didn't make it and I can't convince them to use something more appropriate) with over 165 columns at 1000 rows of data.I can read a large number of columns, but only a handful of rows, or vice versa.

Aside from creating a loop to open and close the excel file over and over reading pieces of it at a time, is there a better way to read more data using ActiveX? Attached is code uploaded by others (with very minor modification) as an example.

Thanks,

[Yamaeda]

Well you dont need to open and close the file, just put the Range select and convert in a loop and it should work. Apart from that i cant really think of anything.

/Y

[Maddox731]

I'll see how that implementation works, thanks for the suggestion. Sad that I can't call more cells from Excel...

[DFGray]

Actually, you probably do not want to read all the cells at once.  Check out the tutorial Managing Large Data Sets in LabVIEW for the details of why.

[Maddox731]

Thanks for the link to large datasets. I'll read through this.

Thought I would share my current implementation and what i've found thus far. I decided not to use the Value2 property but instead the FormulaR1C1 property of Range. It seems that Excel was attempting to compute the evalulation of every cell within the range whenever it was accessed, however if that execution time was too long, it would return a Type Mismatch error. It wasn't a limit of cells but rather the fact that many of the cells had formulas in them.

This method doesn't seem to have a limit, however i'm currently reading the excel file row at a time and taking the needed data from each row and then creating my final data set to parse.

Thanks

[GGT]

Hi Maddox731,

I've only had a very quick glance through your thread, and I must admit I haven't really thought it through properly yet. Sounds like you've come up with your own solution anyway. That said I thought I'd take a bit of a scatter gun approach and attach some stuff for you regradless. Please forgive my bluntness.

You'll find my ActiveX Excel worksheet reader, which may or may not contain the problem you've come across. I've never tried it with the data size you are dealing with, so no promises. I've also attached my ADO/SQL approach to the problem. This was something I moved onto when I realised the limitations of AX. One thing I have noticed is that ADO/SQL is much faster than AX, so there may be some gains for you there with large data sets if you can implement it.

I should add that I'm a novice to all this and my efforts are down to bits I've gleamed from MSDN and others' LV examples. I hope it's of some use, if only to spark discussion. Your ctiticism is more than welcome, good or bad.

Regards.

[Maddox731]

Thanks GGT,

Sorry I haven't responded to this thread in awhile, I'm having trouble converting the LV2010 files you attached into 2009 so I can run them. In any case my currentl solution is 'working' but occasionally the users will fill a field in with simple formulas like =2+3 instead of typing 5 and of course that messes up my routine. I've settled for reading one row at a time in a loop and parsing each row one at a time rather than importing everything and parsing everything.

Thanks.

[Wayne.C]

Maddox731,

In reference to your problem with cells that contain formulas.  This is a way of getting the displayed/formatted text from a cell instead of actual contents.  Be aware that this could cause problems elsewhere with cells that were formatted to display a reduced number of decimal places.  You might start loosing some accuracy.