Create 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 URL from a website
  • Convert the CSV file to JSON format
  • Create the API endpoint that returns the resulting JSON

When you’re done, you’ll be able call the API endpoint to read a CSV file on the web and return it as JSON.

Setup

To complete this tutorial, you’ll need:

Read a CSV file from a website

To begin, you’ll create a basic pipe that reads a CSV located at a URL.

  1. Create a new pipe in Flex.io

  2. By default, you’ll see a sample Execute Task, which will house our function. In the drop-down menu, change the language to “Node.js”. Then, paste the following code into the Execute Task and Save Changes:

  1. After saving your changes, press the “TEST” button in the upper right of the Pipe Builder. You should see the CSV data parsed into a JSON format, similar to the following:
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

Convert the CSV file to JSON format

Now that you have read your CSV data, you’ll need to get it into JSON format, as follows:

  1. You’ll now edit your current Execute Task with the conversion code.

  2. First, add const parse = require('csv-parse/lib/sync') under const axios = require('axios')

  3. Then, update the logic inside the .then(response => {}) to the following:

When you’re done, your full code block should look like this:

  1. After saving your changes, press the “TEST” button again. You should now see data transformed, 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"}
]

Set up an API endpoint to access the data

Next, you’ll simply need to get an API endpoint to access this data transformation feed you just created, as follows:

  1. First, turn on your pipe to activate it.

  2. Then, scroll down to the Deployment section and check the box next to Run using an API endpoint to see your API endpoint and API key.

  3. Next to the endpoint, which by default uses the unique pipe EID as a reference, click the Edit button to add an alias to your pipe as follows: example-create-an-api-endpoint-to-convert-a-csv-file-to-json.

  4. To quickly test the endpoint, copy the HTTPS endpoint and paste it into your browser to see your data; the HTTPS endpoint will look like this (where {token} is your unique API key):

https://api.flex.io/v1/me/pipes/example-create-an-api-endpoint-to-convert-a-csv-file-to-json/run?flexio_api_key={token}

Going further

Of course, now that you have your endpoint, you’re now ready to invoke it in any server or client code that allows you to make an HTTP request. Here are two examples.

HTML Script:

<script src="https://cdnjs.cloudflare.com/ajax/libs/axios/0.18.0/axios.min.js"></script>
<script>
axios({
method: 'post',
url: 'https://api.flex.io/v1/me/pipes/example-create-an-api-endpoint-to-convert-a-csv-file-to-json/run',
headers: {
Authorization: 'Bearer {token}'
}
})
.then(response => {
// var converted_data = response.data
})
.catch(response => {
})
</script>

CURL:

curl -X POST 'https://api.flex.io/v1/me/pipes/example-create-an-api-endpoint-to-convert-a-csv-file-to-json/run'
--header "Authorization: Bearer {token}"
'