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.

Transforming Excel data

Creating a new file

Sheet NameCreate a new file?Add data starting fromSheet 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 NameCreate a new file?Add data starting fromSheet 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

Direct link, no registration required.
Star InactiveStar InactiveStar InactiveStar InactiveStar Inactive
 
 
Xerox
Swiss banking
Bank Of Oklahoma
Red Cross
Alta Pacific bank
Copeinca
Gas alberta
NHS
Royal Brunei
First Oklahoma bank
Noresco
Iqvia

Testimonials

What customers say about us

  • swissbanking

    I used Advanced ETL Processor in 2 Enterprises for many business processes and Business automation (outside finance department). I did not find any other tool with so many functions and broad flexibility for that Price! If you need support for bugs or solution design you will get it very fast. Best Support I have ever seen.

    Lionel Albrecht
  • iqvia

    IQVIA and DB Software Laboratory (DBSL) partnered in 2010 and have been working in close cooperation ever since. Over this period of time, DBSL software components formed an integral part of a large number of IQVIA applications currently used by over 20 UK NHS Trusts (Hospitals).

    Dmitry Dorsky,
    Director
  • xerox

    The product is easy to learn and once a developer understands the ETL way for solving the problem at hand, the developer's productivity will increase. Even our DBAs now uses the ETL software to quickly create solutions instead of SSIS or SQL jobs.

    Daniel Fung
    Solutions Architect

 

This site uses cookies. By continuing to browse the site, you are agreeing to our use of cookies