Read an Excel file into a 2D Array and a Table with unknown number of columns and rows

[emw129]

The example Excel read in LabView loads an Excel file and fills it with numbers into a 2D Table, however the Table size is predetermined and it is overwriting the data read from the Excel file.

I want to read an excel file and create a 2D Table (2D Array) from that Excel file with an unknown number of columns and rows.   So I must some how extract the number of columns and rows before I can do the conversion, and not screw up the incoming Excel spreadsheet data.  I can't seem to find the Excel API call that is capable of extracting the number of columns and rows in an imported Excel Spreadsheet.

I also need to be able to extract a specific worksheet in a given workbook, by name and (or) number.

The Report generating Excel tools, seem to only create a spreadsheet, not import an already existing spreadsheet.

[Artem.SPb]

use Excel Get Last Row VI to determine number of rows and then read data.

for working with existing book use filename as timplate when you create new report.

[ben64]

To get the last column you can ask the vi I posted in this link. To get a specific worksheet use Excel Get Worksheet.vi from the Excel General palette.

Ben64

[Bob_Schor]

---

@emw129 wrote:

The Report generating Excel tools, seem to only create a spreadsheet, not import an already existing spreadsheet.

---

That's because NI has "disguised" this Feature.  When you use the New Report (Excel) function, wire your existing Excel Workbook into the "Template" input.  It will open in Excel, and you can use the Excel Get Data function to get all of the data into a 2D array (without worrying about how many rows or columns are present).  Once you have the Array, do an Array Size to reveal the number of rows and columns.

Note that if you want to change entries and save the results, you have to (re-)specify the Output Filename when you do a Save Report to File, so to overwrite the old file, branch its wire going into New Report and send it to Save Report to File.

I do this all the time.

Bob Schor

[emw129]

I seemed to of run into a problem using the Excel Example in LabVIEW, for some odd reason it seems to quit after 819 rows, with no clear reason why:

See the attached LabVIEW VI and try to load a spreadsheet that is 12 columns wide and 3000 rows deep, and see if your max number of rows doesn't stop at 819 rows.

819 seems to be a pretty odd number.

[ben64]

I don't have LV2017 installed on this machine so I can't check your vi but did you check if row 819 of your worksheet is an empty row? Can you also attach the Excel file you're using.

Ben64 

[Bob_Schor]

---

@emw129 wrote:

819 seems to be a pretty odd number.

---

It may be odd, but it's not prime, and not that pretty ...

Bob Schor

[emw129]

---

@ben64 wrote:

I don't have LV2017 installed on this machine so I can't check your vi but did you check if row 819 of your worksheet is an empty row? Can you also attach the Excel file you're using.

Ben64 

---

There are no empty rows, I'll attach the spreadsheet that I'm using.  I think you will get the same result if you use the LabView 2016 Example as well.   Assuming it isn't too big to up load or down load.   Use any sheet that has more than 819 rows...

[ben64]

Well, the Powerball sheet has 819 rows and if I run your code on any other sheet I still get the good number of rows: LOTTO -> 2620, ...Texas Two Step -> 1732, ... (using LV2017)

Ben64

[emw129]

yes, but does the Date of that drawing match the last date in the spreadsheet?  Or are some of the rows missing?

I'm also running into another problem, the spreadsheet isn't being released by the LabView Code, it is somehow keeping it in memory and not permitting access to the spreadsheet after the program finishes.  Any Explanation why?

The Problem goes away after rebooting my computer, no other operations clears up this spreadsheet.

See attached files

  1) Screen capture of attempted access to Excel spreadsheet after program finishes

  2) LabView code that reads spreadsheet(s)

---