¶ Description
Reads a Microsoft Excel .xlsx file.
¶ Parameters
¶ Standard tab
Parameters:
- File(s) Name(s) from pin
- Input file
- The first row inside the user-selected range contains the column names
- Sheet selection
- Sheet id
- Columns selection
- Rows selection
¶ Advanced tab
Parameters:
- Merging mode of columns when multiple files/sheets
- Output file names and sheet names as additional columns
- Abort if missing file
- Replace content of erroneous cells (#N/A) with
- Timeout before error when a file is unreadable
¶ About
Each cell in a .xlsx file can have a different data type (e.g., String, Double, Date, etc.). Furthermore, there is no reliable way to a priori determine the most common data type used in a specific column of an Excel sheet. For these reasons, all cells in an Excel file are imported into ETL as simple strings. You need to “manually” use the ChangeDataType action to set a specific type for a column.
NOTE :
You can drag&drop a .xlsx file from a MS-File-Explorer-Window into an ETL-Pipeline-Window: This will directly create the corresponding readExcel action inside the ETL pipeline.
Excel uses two different methods to store dates in .xlsx files: dates can be stored either as strings or as integer numbers. Dates saved as integers are imported into ETL as numeric values. To convert these dates to “normal” ETL dates, you can use the “to String from Elapsed Time” option of the ChangeDataType action. Use these parameters:
- Reference Time: 18991230 00:00:00
- Elapsed Time Unit: day.
A screenshot is provided below for clarity:
