Standardize Column Names in a CSV file to Match an Upload Template Format
In an ideal world, every app would seamlessly share data with eath other in the correct format, every time. However, in the real world, manually editing and integrating data is very common.
For example, let’s say we’re a scrappy startup. Every day we get a file from our ecommerce system that gives us all of our order information for shipping. To ship, we use a second app for generating our postage. We simply need to get our ecommerce data into our shipping system.
This is a fairly straightforward task. For a one-time project, it would be pretty normal to use Excel and just manually concatenate and rename fields and then delete everything else. However, when you need to do it every day, that half-hour of daily busywork adds up.
For this example, we’ll set up an automated process to convert and format a CSV file using Flex.io. Specifically, we’ll do the following:
- Combine the name columns and the address columns into a single line
- Delete all columns except for the three we need
- Rename columns to match the template
Let’s start building!
For this example, we’ll use a sample contact list in CSV format that has 14 columns, which we’ll ultimately boil down to
File Input and Conversion
Now that we have our file, we’ll import it from the web:
input file: https://raw.githubusercontent.com/flexiodata/data/master/contact-samples/contacts-ltd1.csv => contacts.txt
As part of the input, we’ll also rename the file to
contacts.txtin case we want the JSON as text file on it’s own.
Convert to table to prep for cleanup
Next, to prepare the file to do our reformatting operations, we’ll convert it into a table:
convert from: delimited to: table delimiter: comma qualifier: none header: true
Concatenating columns means combining information from two or more columns into a single column. So if
givennamehas “Bill” and
surnamehas “Jones”, we’ll want to create a new combined column that shows: “Bill Jones”
calc formula: concat(givenname, ' ', surname) name: name
calc formula: concat(streetaddress, ', ', city, ', ', state, ' ', zipcode) name: address
Note that in order to add spacing we just add a space as part of the concatenation. By doing this you get a result of “Bill Jones” instead of “BillJones”.
Finally, as part of the
calccommand, we get to name the column; so we’ll name it as we’re required to per the template (
Delete unrequired columns
To remove unnnecessary columns, we’ll simply select the ones we want to output. To do this, we use the select command:
select col: name, address, emailaddress
Our email address column needs to change from
rename col: emailaddress=>email
That’s it. By using these commands in succession, we get a simple file to upload into our postage app.
You can run the process manually at any time. But, the next step is automating the process. To deploy you can do one of the following:
- Use the JSON with an AJAX call via the API
- Call the pipe manually via the Command Line Interface
- Schedule the pipe to run automatically
To start with the commands above ready to roll, here’s a CSV to JSON pipe template. Click on the button below to copy the pipe and modify it with your own data and commands.