Generating Excel files

Transforming Excel data can be a complex task this article describes how Advanced ETL Processor works with Excel data.

Reading Excel data

Data can be extracted from entire Sheet or just from the data range.
Advanced ETL Processor assumes that "Sheet name" and "Range name" a delimited by dot.

Users can still call Sheets as a "Great.Victory"

The logic as follows:

Advanced ETL Processor will check if Great.Victory sheet exits than it will read it
If it does not exists it will check if 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 with in the file

Writing Excel data

Advanced ETL Processor gives the user various options to generate Excel files.

Transforming Excel data

Creating new file

Sheet Name Create 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, new range called "RangeName" is created with the size equlal 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, new range called "RangeName" is created with the size equlal 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, new range called "RangeName" is created with the size equlal to the populated data


Adding/ updating data in the existing Excel file

Sheet Name Create 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, new range called "RangeName" is created with the size equal to the the size of populated data
SheetName.RangeName No Cell No No New Sheet is created "SheetName", data is inserted from the Cell, new range called "RangeName" is created with the size equal to the 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, new range called "RangeName" is created with the size equal to the 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 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, than new range called "RangeName" is created with the size equlal to the 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, than new range called "RangeName" is created with the size equlal to the the size of populated data.
SheetName.RangeName No Last Row Yes No Within Sheet "SheetName", data is is inserted starting from the last empty row, the rest of the data within Sheet is left intact, than new range called "RangeName" is created with the size equlal to the 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, than range "RangeName" size is set to the 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, than range "RangeName" size is set to the the size of newly populated data.
SheetName.RangeName No Last Row Yes Yes Within Sheet "SheetName", data is is inserted starting from the last row of the range "RangeName" +1, the rest of the data within Sheet is left intact, than 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

Visit Support Forum Learn More Download Buy It

I am continually Amazed by the power and versatility of the ETL tool and the Support Team.

Thank You!

http://www.dbsoftlab.com/advanced-etl-processor/2475-de-duplication-of-addresses.html

Our customers

BP

BBC

HSBC

Orange

CAT

SEPA
emc
NHS
Australian Rugby Union
Databases we work with