Reading Excel Spreadsheet Files in LabVIEW

Issue Details

I have an Excel spreadsheet file (.xls or .xlsx file), and I would like to read its contents in LabVIEW. What are the different ways I can do this?

Solution

There are several ways to read an Excel file in LabVIEW. Here are the most common approaches:

Convert to Text File

You can convert the spreadsheet from its .xls or .xlsx format into a text file that can be read in LabVIEW. To do this, follow these steps:

1. Select File > Save As in Excel.
   
2. In the Save As dialog, change the “Save as type” option to one of the following options:
   1. Text (Tab delimited) – Saves the data with a Tab character as the column delimiter.
   2. CSV (Comma delimited) – Saves the data with a comma character as the column delimiter. The CSV (comma-separated values) format is incredibly common in test applications.

Note: If your Excel file contains multiple sheets, you can only save the contents of one sheet to a single text or CSV file.

3. Use the Read Delimited Spreadsheet VI in LabVIEW to read the contents of the new file. By default this VI uses a Tab character as a delimiter. You will need to wire a comma to the ‘delimiter’ input if you are reading a CSV file. Here is some sample code that performs this operation:
   
   Note: The Read Delimited Spreadsheet VI supports reading string, integer, or double values from the CSV file. You can only read the data as a single type with this VI. For example, in the code above, the Double instance of the VI is selected, which means all the data in the CSV file will be returned as double precision floating-point numeric values.

Report Generation Toolkit

The Report Generation Toolkit is available with LabVIEW Professional on Windows. The toolkit provides VIs that make direct calls to Microsoft Excel via ActiveX technology. This approach does not require converting the Excel file to another format, but it does require Microsoft Excel to be installed on the machine.

1. Use the Create Report VI to create a new report. You can pass in the path to an existing Excel file (.xls or .xlsx file) to access its data.
2. Use the Excel Get Worksheet VI to specify the sheet you want to read into LabVIEW.
3. Use the Excel Get Data VI to read the data from a specified sheet. You can wire a value to the ‘data type’ input to specify which type of data you want to read. You can only read the data as a single type with this VI, and the supported types are string, integer, and double.
4. Use the Dispose Report VI to close the report.
5. Here is some sample code that performs this operation:
   

NI Read XLSX File.vi

There is an open-source VI available on github that can read the contents of an Excel .xlsx file without requiring Microsoft Excel to be installed. This VI also works on all LabVIEW platforms. Note that this VI only works for Excel .xlsx files, and does not work for the legacy Excel .xls file format. The NI Read XLSX File VI is not officially supported by NI. Bug reports and feature requests for the VI should be submitted to the Issues page of the github repo.

1. Download the VI with this link:
   https://github.com/dnattinger/read-xlsx-file/raw/main/source/NI Read XLSX File.vi
2. Specify the path to the .xlsx file you want to read and run the VI:
   
   Note 1: The VI will return the contents of all sheets in the .xlsx file.
   Note 2: The VI always returns the data as string values.
   Note 3: Time data (date and/or time) is returned as a number of days elapsed since January 1, 1900. Hour/minute/second information will be returned as a fraction of a day (24 hours).

Additional Information

Third-party LabVIEW Toolkits

The following third-party LabVIEW toolkits support reading Excel data in LabVIEW:

• Viewpoint Xlsx
• XLR8
• CLAUDIE_xlsx

Note that these toolkits are not supported by NI. Contact the toolkit author for support.