read data from xlsx data file

[afh]

How do I read data from an Excel 2010 spreadsheet with the .xlsx extension?  The data I want is on one of 10 tabs in the file, I need to pick that programatically.  I have the Report Generation Toolkit for LV2012, but all the vis that come with it seem geared around writing data to an Excel spreadsheet and making graphs and whatnot.  I want to *read data from* an Excel spreadsheet.  Searching for answers leads to a lot of "open it and save it as tab delimited text", which I can't do because I need data from the various tabs, and because this file is pretty dynamic with lots of users opening it and adding to it at various times.  In order to save the tab I want as a tab delimited text file programatically, I'm back a square one, how do I open it?

Ideally my solution would fill in the blanks of: spreadsheet.xlsx---->___________________-----> 2 or 3D array of data.

thanks,

adam

[nyc_(is_out_of_here)]

If you need to write to Excel sheets, then you need to learn how to work with ActiveX.

tab delimited means using the tab character \t to separate values

tab in this context does not mean the individual sheets aka "tabs" of an Excel file.

[afh]

Yes, I understand.  Sheets are what I was calling tabs.  I need to be able to *open* an xlsx file, which has a much more complicated format than a typical tab delimited file.  The data in the .xlsx file is on multiple 'sheets', I need to access all of them.  I need to know how, using the report generation toolkit 2012, to go from .xlsx file with multiple sheets, to a 3D array of data.

Adam

[ben64]

The Report Generation Toolkit is for generating reports, not for reading Excel workbooks. To do what you want you will have to use ActiveX. Search the forum for "Excel ActiveX" and you will find a lot of examples.

Ben64

[Dennis_Knutson]

Well, you do have the Get Worksheet and the Get Data functions. Have you tried putting them in a loop by creating an array of worksheet indexes/names?

[ben64]

---

@Dennis_Knutson wrote:

Well, you do have the Get Worksheet and the Get Data functions. Have you tried putting them in a loop by creating an array of worksheet indexes/names?

---

Good call Dennis.

To add to Dennis post the Excel Workbook Properties.vi can be used to get the worksheet count. Wire this output to a for loop count terminal.

Ben64

[afh]

I see these two .vis and they seem to be integral to the solution to the problem.  One question: each takes a "report in" with a data type that's specific to the RGT.  How do I generate a reference to a report that I'm not writing/is already written.  I.e. where is "open report"?  I only see "new report" to generated a report reference from nowhere.  

Thanks,

Adam

[afh · Accepted solution]

This solves it:

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

[afh]

Never mind, doesn't solve it.  This reads from multi-sheet .xls files, but errors on .xlsx, even though a recent example.

[remywizard]

This worked for me. You just have to make sure you are parsing the correct kind of data.

"

This solves it:

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

"