Excel Get Data doesn't work with arrays

[Bob_Albern]

I can't get the Excel Get Data polymorphic function to work with arrays. It works fine with strings, float's and int's. I am using LV 7.1 Full Development System.

[Travis_M.]

Hello Bob,

I found some related information to this issue on the web:

"In the Report Generation Toolkit for Microsoft Office, the Excel Get Data VI allows you to retrieve data from Excel. As shown in the Excel Get Data image attached below, one of the required inputs is the data type.

The data type input for the Excel Get Data VI is used to define the data type (string, DBL, I32, etc.) to be retrieved as well as the size of the data to be retrieved. Valid inputs are therefore singular values or an array. If you are going to be retrieving anything more than a single cell of data with this VI, you also have to use the name input (the unnamed input at the bottom of the VI as seen in the attached image) to tell Excel the range of cells that you will be reading in.

Therefore, if you are getting either a single row/column of data or a "table" array of data back from Excel, you will need to wire a 2D array of the data type you will be receiving into the data type input and use the name input to define the range of cells you want to read into LabVIEW. "

You can read the whole article, and see an example by going to: http://digital.ni.com/public.nsf/websearch/2738B89F1134827386256D87006941FB?OpenDocument

Please let us know if you have any other questions,

Travis M
Applications Engineer
National Instruments

[Bob_Albern]

Travis,
Thanks for your suggestion. Unfortunately, it doesn't work. I have attached an example of the problem. If you wire the integer constant to the data input, all is OK. If you wire either of the arrays to the input, an error occurs. Both the VI help file and the app note you referenced indicate this should work.

Bob

[Travis_M.]

Hello Bob,

The issue here is how the data is returned from the Excel Get Data VI, and the necessity of wiring in a 2-D array into the data type input. In this case, the VI returns a 2-D array of data, so you will need to wire a 2-D array into the "data type" VI. (The reasons for this are outlined here: http://digital.ni.com/public.nsf/websearch/2738B89F1134827386256D87006941FB?OpenDocument). I have not examined your code and spreadsheet, but I created an example that will read a data from a row, column, and a table from an Excel file. The return type is a 2-D array, but you can do some array operations to extract the data you wish to use. Take a look at this example, and let me know if you have any other questions on this issue.

Travis M
Applications Engineer
National Instruments

[Bob_Albern]

Travis,
The problem turns out to be the number of dimensions of the array I am trying to read. A 1D array is not allowed. The input needs to be either a scalar or a 2D array. A 1D array needs to be treated as a degenerate 2D array. This is not clearly spelled out in the help files. The only mention is in the note at the end of the help file and it is related to an excel spreadsheet with mixed data types. I have now fixed the problem and the program is working.

Thanks,
Bob

[Linda22]

I'm using 2013, and I'm having a problem connecting arrays of strings to the "Excel Get Data" vi. I can't wire a 2D array of strings to it, but I can a 1D array of strings. I was under the impression that the vi adapts to the input. I have a co-worker that doesn't have a problem with it. Could this be a link issue with a previous version of a vi used in the get data vi?

Linda