0022 Loading data from files with different fields' order
A practical Advanced ETL Processor example for building, testing, and adapting one automation workflow.
Use this example to build one working automation package, test it safely, and then adapt it to your own files, folders, or data rules. Start small. Automation is helpful; automated mistakes are just faster mistakes.
Question from the customer
A file is downloaded from a third-party reporting portal.
Two reports came down with slightly different formats. The data is basically the same, but the report engine added a couple of extra columns into the middle of one report.
The source still arrives as a simple CSV file. How do we load it without the column order breaking the transformation?
Basic data example
Answer
The best fix is to make the source file format consistent. If that is not realistic, configure the reader to use field names instead of trusting column position.
Add dummy fields if the problem file has more fields than the original. That gives the reader somewhere to put the surprise columns, which is kinder than letting them wander through the transformation.
Note: warning in the log
To view the example follow the steps below
- Download and install Advanced ETL Processor [Link]
- Download and unzip the example [Link]
- Create a new transformation and open the .ats file
- Double-click the Reader object and amend the source file path
- Double-click the Writer object and amend the target file path
- Run the transformation by pressing the green arrow.
Related Advanced ETL Processor resources
For more automation examples, review the Advanced ETL Processor tutorials, read the WIKI, or download the Advanced ETL Processor Enterprise trial.