LabVIEW

cancel
Showing results for 
Search instead for 
Did you mean: 

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

Solved!
Go to solution

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.

0 Kudos
Message 1 of 16
(5,903 Views)

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.

0 Kudos
Message 2 of 16
(5,866 Views)

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

0 Kudos
Message 3 of 16
(5,842 Views)

@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

0 Kudos
Message 4 of 16
(5,813 Views)

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.

0 Kudos
Message 5 of 16
(5,419 Views)

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 

0 Kudos
Message 6 of 16
(5,414 Views)

@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

0 Kudos
Message 7 of 16
(5,405 Views)

@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...

0 Kudos
Message 8 of 16
(5,384 Views)

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

0 Kudos
Message 9 of 16
(5,378 Views)

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)

 

 

 


 

0 Kudos
Message 10 of 16
(5,375 Views)