Clean a CSV File and Convert to JSON
It seems that almost every website plugin uses JSON as a data input. This works great if you have a JSON stream handy (say, via an API), but will require a conversion step if you only have a CSV or Excel file to work with.
While there are a variety of online or desktop utilities that offer this functionality, to get a live, repeatable, refreshable stream, you’ll need to find a library to parse the CSV into JSON and then set up a server to run it as needed. Flex.io lets you quickly set up a pipe to perform this task so you can ingest the JSON via API.
For this example, we’ll take a CSV file, reformat and reduce columns and convert to JSON.
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 “Mike” and
surnamehas “Jones”, we’ll want to create a new combined column that shows: “Mike Jones”
calc formula: concat(givenname, ' ', surname) name: name
calc formula: concat(streetaddress, ', ', city, ', ', state, ' ', zipcode) name: address
Note the little trick we’re doing here: in order to add spacing we just add a space as part of the concatenation. By doing this you get a result of
Mike Jonesinstead of
Finally, as part of the
calccommand, we get to name the column; so we’ll name them as required per the template (
Remove unnecessary columns
To remove unnnecessary columns from our output, 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
Convert from CSV to JSON
Now that we have our file in good shape, we just have to convert the CSV to JSON. To do this, we simply use a
convert to: json
And that’s it! The CSV to JSON conversion process is ready to roll.
Now that you have your pipe, you can deploy it as desired:
- 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 get going instantly, 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.