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 itIf it does not exists it will check if Great sheet exitsIf it does exists it will check if Victory range exists if it does It will read data from just Victory rangeIf 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

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