Transforming Excel data can be a complex task this article describes how the Advanced ETL Processor works with Excel data.
Reading EXCEL data
Data can be extracted from the entire Sheet or just from the data range.
Advanced ETL Processor assumes that "Sheet name" and "Range name" a delimited by a dot.
Users can still call Sheets as a "Great.Victory"
The logic as follows:
Advanced ETL Processor will check if Great.Victory sheet exits then it will read it
If it does not exist it will check if the Great sheet exits
If it does exists it will check if Victory range exists if it does It will read data from just Victory range
If it does not it will read the data from Sheet "Great"
Please make sure that the range names are not duplicated within the file
Writing EXCEL data
Advanced ETL Processor gives the user various options to generate Excel files.
Creating a new file
Sheet Name | Create a new file? | Add data starting from | Sheet Exists? | Range Exists? | Notes |
---|---|---|---|---|---|
SheetName | Yes | Top | No | No | New file is created with the Sheet called "SheetName", data is inserted from the top left corner |
SheetName | Yes | Cell | No | No | New file is created with the Sheet called "SheetName", data is inserted from the Cell |
SheetName | Yes | Last Row | No | No | New file is created with the Sheet called "SheetName", data is inserted from the top left corner |
SheetName.RangeName | Yes | Top | No | No | New file is created with the Sheet called "SheetName", data is inserted from the top left corner, a new range called "RangeName" is created with the size equal to the populated data |
SheetName.RangeName | Yes | Cell | No | No | New file is created with the Sheet called "SheetName", data is inserted from the Cell, a new range called "RangeName" is created with the size equal to the populated data |
SheetName.RangeName | Yes | Last Row | No | No | New file is created with the Sheet called "SheetName", data is inserted from the top left corner, a new range called "RangeName" is created with the size equal to the populated data |
Adding/ updating data in the existing EXCEL file
Sheet Name | Create a new file? | Add data starting from | Sheet Exists? | Range Exists? | Notes |
---|---|---|---|---|---|
SheetName | No | Top | No | No | New Sheet is created called "SheetName", data is inserted from the top left corner |
SheetName | No | Cell | No | No | New Sheet is created called "SheetName", data is inserted from the Cell |
SheetName | No | Last Row | No | No | New Sheet is created "SheetName", data is inserted from the top left corner |
SheetName.RangeName | No | Top | No | No | New Sheet is created "SheetName", data is inserted from the top left corner, a new range called "RangeName" is created with the size equal to the size of populated data |
SheetName.RangeName | No | Cell | No | No | New Sheet is created "SheetName", data is inserted from the Cell, a new range called "RangeName" is created with the size equal to the size of populated data |
SheetName.RangeName | No | Last Row | No | No | New Sheet is created "SheetName", data is inserted from the top left corner, a new range called "RangeName" is created with the size equal to the size of populated data |
SheetName | No | Top | Yes | No | Within Sheet "SheetName", data is replaced starting from the top left corner, the rest of the data within Sheet is left intact. |
SheetName | No | Cell | Yes | No | Within Sheet "SheetName", data is replaced starting from the cell, the rest of the data within Sheet is left intact |
SheetName | No | Last Row | Yes | No | Within Sheet "SheetName", data is inserted starting from the last empty row, the rest of the data within Sheet is left intact. |
SheetName.RangeName | No | Top | Yes | No | Within Sheet "SheetName", data is replaced starting from the top left corner, the rest of the data within Sheet is left intact, then a new range called "RangeName" is created with the size equal to the size of populated data |
SheetName.RangeName | No | Cell | Yes | No | Within Sheet "SheetName", data is replaced starting from the cell, the rest of the data within Sheet is left intact, then new range called "RangeName" is created with the size equal to the size of populated data. |
SheetName.RangeName | No | Last Row | Yes | No | Within Sheet "SheetName", data is inserted starting from the last empty row, the rest of the data within Sheet is left intact, then new range called "RangeName" is created with the size equal to the size of populated data. |
SheetName.RangeName | No | Top | Yes | Yes | Within Sheet "SheetName", data is replaced starting from the top left corner of the range "RangeName", the rest of the data within Sheet is left intact, then range "RangeName" size is set to the size of newly populated data. |
SheetName.RangeName | No | Cell | Yes | Yes | Within Sheet "SheetName", data is replaced starting from the cell, the rest of the data within Sheet is left intact, then range "RangeName" size is set to the size of newly populated data. |
SheetName.RangeName | No | Last Row | Yes | Yes | Within Sheet "SheetName", data is inserted starting from the last row of the range "RangeName" +1, the rest of the data within Sheet is left intact, then range "RangeName" size increased by the size of newly populated data |
We would like to thank Charles O. Hall for providing us with useful feedback