Standardize Column Names in a CSV file to Match an Upload Template Format

Clean CSV File, Convert to JSON

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.

Convert an ecommerce CSV file to match an upload template


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!

  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 “Bill” and surname has “Jones”, we’ll want to create a new combined column that shows: “Bill 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 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 calc command, we get to name the column; so we’ll name it as we’re required to per the template (name and address, respectively).

  5. 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
    
  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
    

    That’s it. By using these commands in succession, we get a simple file to upload into our postage app.

Automating the process


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:

Get Started


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.

Reformat CSV Template