Pattern: Reorder, Rename, and Fill Columns in a Dataset
Sometimes one may have a need to take reportable data from one system and reorder columns, and rename the column headers. The reformatted data could, for example, be turned from .xlsx format into a .csv, and be uploaded in another system. Take the below spreadsheet as an example:
Our source system provides the data in the following order:
Name, Email Address, Location, Phone Number, Username
But we would instead like these columns in the below order, with slightly different labels (add a Record Type column added, and the Location column removed):
Record Type,Username,Email,First Name,Last Name,Phone
We can add placeholder columns to create the order we'd like (CSV: Add or remove columns) and then fill the columns with the values we'd like (CSV: Apply formulas to columns). In the latter step we can additionally insert a static Record Type value (Add), and split the full name into separate first and last name values.
Record Type,Username,Email,First Name,Last Name,Phone Add,wnelson,test@catalytic.com,Wanda,Nelson,+1 111 111 1111 Add,sjohnson,test@catalytic.com,Shirley,Johnson,+1 222 222 2222
The attached workflow has two workflow-level fields: Upload File and Template. When the workflow runs, the completed .csv file can be downloaded from the Insert .csv file into template step.