Deploy an API endpoint to convert a CSV file into JSON

Introduction

In this tutorial, you’ll learn how to create a Flex.io API endpoint that converts a CSV to JSON.

Specifically, you’ll learn how to:

  • Read a CSV file from a website
  • Convert the CSV file to JSON format
  • Create and call an API endpoint that returns the resulting JSON

When you’re done, you’ll be able to read a CSV on the web as JSON.

For example, suppose you have a CSV on the web:

OS,2009,2010,2011,2012,2013,2014,2015,2016,2017
Android,1.6,9.6,36.4,56.9,74.4,80.8,78.8,84.1,86.1
iOS,10.5,15.4,16.9,22.5,18.2,15.3,17.9,14.8,13.7
Microsoft,10.2,6.8,2.6,1.9,2.9,2.7,2.5,0.7,0.1
RIM,20.6,19.7,13,6.8,3,0.6,0.4,0.2,0
Symbian,48.8,44.2,27.7,8.5,0.6,0.2,0.1,0,0

You’ll be able to access it as JSON:

[
{"os":"Android","2009":"1.6","2010":"9.6","2011":"36.4","2012":"56.9","2013":"74.4","2014":"80.8","2015":"78.8","2016":"84.1","2017":"86.1"},
{"os":"iOS","2009":"10.5","2010":"15.4","2011":"16.9","2012":"22.5","2013":"18.2","2014":"15.3","2015":"17.9","2016":"14.8","2017":"13.7"},
{"os":"Microsoft","2009":"10.2","2010":"6.8","2011":"2.6","2012":"1.9","2013":"2.9","2014":"2.7","2015":"2.5","2016":"0.7","2017":"0.1"},
{"os":"RIM","2009":"20.6","2010":"19.7","2011":"13","2012":"6.8","2013":"3","2014":"0.6","2015":"0.4","2016":"0.2","2017":"0"},
{"os":"Symbian","2009":"48.8","2010":"44.2","2011":"27.7","2012":"8.5","2013":"0.6","2014":"0.2","2015":"0.1","2016":"0","2017":"0"}
]

Using an API call:

curl -X POST 'https://api.flex.io/v1/me/pipes/csv-to-json/run'
--header "Authorization: Bearer {token}"
'

Setup

To complete this tutorial, you’ll need the following:

  • A Flex.io API key
  • A simple way to make an HTTP request to test your API endpoint

To get a Flex.io API key, sign up for Flex.io, or if you already have an account, sign in. When you sign in, you’ll see your API listed in the “API Keys” section on the dashboard where you’re directed after you sign in.

In addition, you’ll need a tool that allows you to make HTTP requests. We’ll show you how using CURL.

Build it

Next, you’re ready to build the data feed to convert CSV to JSON.

When you’re done, you’ll have a pipe defined in the Flex.io webservice that looks like:

Create the pipe

  1. Open the Pipes list by selecting “Pipes” from the nav menu.
  2. In the Pipes list, create a new pipe by clicking the “New Pipe” button.
  3. In the pipe properties section, name your pipe “CSV to JSON” and give your pipe an alias “csv-to-json”.
  4. In the pipe configuration section, add the following code to access a sample CSV and convert it to JSON:

Run the pipe

  1. In the pipe configuration section, click the “Run” button.
  2. The pipe should take about a second or so to run, and then you should see JSON in the output area similar to the following:
[
{"os":"Android","2009":"1.6","2010":"9.6","2011":"36.4","2012":"56.9","2013":"74.4","2014":"80.8","2015":"78.8","2016":"84.1","2017":"86.1"},
{"os":"iOS","2009":"10.5","2010":"15.4","2011":"16.9","2012":"22.5","2013":"18.2","2014":"15.3","2015":"17.9","2016":"14.8","2017":"13.7"},
{"os":"Microsoft","2009":"10.2","2010":"6.8","2011":"2.6","2012":"1.9","2013":"2.9","2014":"2.7","2015":"2.5","2016":"0.7","2017":"0.1"},
{"os":"RIM","2009":"20.6","2010":"19.7","2011":"13","2012":"6.8","2013":"3","2014":"0.6","2015":"0.4","2016":"0.2","2017":"0"},
{"os":"Symbian","2009":"48.8","2010":"44.2","2011":"27.7","2012":"8.5","2013":"0.6","2014":"0.2","2015":"0.1","2016":"0","2017":"0"}
]

Update the pipe

In this example, we’ve converted a CSV file in our sample data GitHub repo. Next, let’s update the pipe to convert a URL of your choice.

  1. Edit the pipe configuration.
  2. In the request step, change the URL of the ‘phone-market-share.csv’ to the web URL you’d like to convert as JSON.
  3. Save the pipe.

Deployment

Once you’ve built the pipe, you’re ready to use it:

  1. Go to your command prompt and run the following CURL command with the ‘csv-to-json’ alias you gave to the pipe:
curl -X POST 'https://api.flex.io/v1/me/pipes/csv-to-json/run'
--header "Authorization: Bearer {token}"
'
  1. You should see JSON corresponding to your CSV.

In general, you can call pipes with an alias of your choice by filling in {pipe-alias} in the following:

curl -X POST 'https://api.flex.io/v1/me/pipes/{pipe-alias}/run'
--header "Authorization: Bearer {token}"
'

And of course, you can invoke the API endpoint in any server or client code that allows you to make an HTTP request.

Going further

This tutorial shows you how to create an API endpoint that returns the contents of a CSV file on the web in JSON. Following a similar pattern, you can also convert CSVs in cloud storage to JSON.

Here are some templates to get you started:

Create an API endpoint from a CSV on Amazon S3
Create an API endpoint from a CSV on Box
Create an API endpoint from a CSV on Dropbox
Create an API endpoint from a CSV on SFTP