Importing Excel files

v21.4.12 of the TabularCsv plugin adds support for parsing one worksheet from an Excel workbook (*.xls, *.xlsx, and *.xlsm files are all supported).

Excel workbooks can contain more than one worksheet and the TabularCsv plugin must choose only one worksheet to parse. By default, the first worksheet in the workbook will be parsed. The Configuration object (the "top-level" of your TOML file) has two configurable properties to override the default workbook.

• If the SheetNumber property is set to a positive number, then the n-th worksheet will be parsed.
• If the SheetName property is set, then the first worksheet that matches the name (case-insensitively) will be parsed.
• If both properties are set, SheetNumber takes precedence and SheetName is ignored.
• These two properties have no effect when parsing a plain text file like a CSV.

An example of parsing by sheet name:

SheetName = 'Field Data' # Parse the sheet named 'Field Data', no matter the order it occurs in the workbook

# Other Configuration properties
Location = '@The Location'
Time = '@The Time'
#...

An example of parsing by sheet number:

SheetNumber = 2 # Parse the 2nd sheet in the workbook, no matter what it's sheet name is.

# Other Configuration properties
Location = '@The Location'
Time = '@The Time'
#...

The selected sheet gets converted into an in-memory CSV

TabularCsv's Excel support comes as a pre-processing trick. If the input file is an Excel file, and a matching SheetNumber or SheetName is found, the sheet is converted into an in-memory CSV file, and then TabularCSV parses the in-memory CSV using your TOML configurations as usual.

The conversion of worksheet-into-CSV uses the exact same conversion rules as the ExcelCsvExtractor.exe tool. If you use the Test Drive page and select an Excel file, the selected sheet will be displayed in its converted CSV form, so you can see the shape of the CSV and use that to build your TOML configuration.

Converted dates and times are always ISO8601

One side effect of converting from Excel to CSV is that every cell that is a date or time will be converted into an ISO8601 timestamp using the yyyy-MM-ddTHH:mm:ss.fffffff pattern, irrespective of how it was formatted in Excel.

This Timestamp pattern is supported automatically with every locale, so usually your TOML parsing code will not need to include a specific format pattern.

• If an Excel cell is just a date, the converted time component will always be T00:00:00.0000000. Your TOML should use one of the DateOnly properties to parse the date and ignore the time component.
• If an Excel cell is just a time, the converted date component will always be 1899-12-31. Your TOML should use one of the TimeOnly properties to parse the time and ignore the date component.