This entry will guide you through on how to define fields and column names in Excel files and also exclude columns in Excel files using the ETL module. Attached is the xlsx file used for this guideline.
Prerequisite
Save the sample.xlsx at ./data/in folder under the Ambience server installation directory.
E.g : ./data/in/sample.xlsx
Data inside the sample.xlsx as below
- Inside the ETL module, create a new ETL and leave the Copy Form value as a default
- Under Chains section, add new chain and leave the Copy Form value as default
- Under Steps section, add a new step “XLSX Definition”. On the Edit Step, enter the sample.xlsx path under the URL field and enter the sheet name under the sheet field. Leave the rest field as a default value.
- Next is to add the “XLSX Reader” step and on the Edit Step proceed to save it.
- Then after that add the “Restructure” step. This will allow you to define the field and column name in the excel file. E.g column Value1 will be rename as Gender while column Value2 will rename as Age.
- Next is to add the “XLSX Writer” step. This step will allow you to exclude columns inside the excel file. In this case we want to exclude the “Age” column. List down all the columns except the Age column inside the Columns field as below
- Lastly add the “File Writer” step. This allows you to save the result on a specific location based on the path provided in the File Path field.
- Final chainset will look like below
- Run the chainset and the xlsx output result should look like below.
sample.zip (6.7 KB)