¶ Description
Reads a Microsoft Excel .xlsx file.
¶ Parameters
¶ Standard tab
Parameters:
- File(s) Name(s) from pin
- Input file
- The first line contain columns names
- Sheet selection
- Sheet id
- Columns selection
- Rows selection
¶ Advanced tab
Parameters:
- Columns with dates
- parameters.readExcel.dateFormat
- Ignore empty lines
- Memory buffer size
- Writing filename and sheet in a column
- Replace content of erroneous cells (#N/A) with
¶ About
Each different cell inside a .xlsx file can have a different Data Type (i.e. it can be String, Double, Date, etc.). Furthermore, there is no way to a-priori “guess” the most common type used inside a specific column in an Excel sheet. For these reasons, all cells inside 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 your local machine 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 inside .xlsx files: dates can be stored either as Strings or as integer numbers. Dates stored as integer numbers are imported into ETL as numeric values.
To convert these values into standard ETL date formats, 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
Here is a screenshot:
