Clean a CSV File and Convert to JSON

Clean CSV File, 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.

Build the Pipe: CSV to JSON Conversion Stream

For this example, we’ll take a CSV file, reformat and reduce columns and convert to JSON.

  1. Sample Data

    For this example, we’ll use a sample contact list in CSV format that has 14 columns, which we’ll ultimately boil down to name, address and email.

    sample data table

  2. 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.txt in case we want the JSON as text file on it’s own.

  3. 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
  4. Concatenate columns

    Concatenating columns means combining information from two or more columns into a single column. So if givenname has “Mike” and surname has “Jones”, we’ll want to create a new combined column that shows: “Mike Jones”

    To perform the concatenation, we’ll use the calc command to create a new column and then the concat function to concatenate the information. Here are the commands, for name and address, respectively:

    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 Jones instead of MikeJones.

    Finally, as part of the calc command, we get to name the column; so we’ll name them as required per the template (name and address, respectively).

  5. 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
  6. Rename columns

    Our email address column needs to change from emailaddress to email, so we’ll use the rename command:

    rename col: emailaddress=>email
  7. 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 command:

    convert to: json

And that’s it! The CSV to JSON conversion process is ready to roll.

Deploy the Pipe

Now that you have your pipe, you can deploy it as desired:

Get Started

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.

CSV to JSON Conversion Template