Create an API endpoint from a CSV file in a cloud storage service

Introduction

In this tutorial, you’ll learn how to create an API endpoint that returns the contents of a CSV file in a cloud storage service.

Specifically, you’ll learn how to:

  • Connect to a cloud storage service
  • Read a CSV file from a cloud storage service
  • Convert the CSV file to JSON format
  • Create the API endpoint that returns the resulting JSON

Setup

To complete this tutorial, you’ll need:

  • A Flex.io account and API key
  • A cloud storage service that has a CSV file you’d like to use as the source of your data feed. Here are some of the cloud storage services available in Flex.io: Dropbox, Box, Google Drive and Amazon S3.

Connect to a cloud storage service

  1. To connect to a cloud storage service, navigate to the Connection page and add your Connection.

  2. Give the connection the alias example-cloud-storage-api-source. This will be used as part of the path in the code that reads the CSV file, similar to a network drive mapping.

Read a CSV file from a cloud storage service

  1. Create a new pipe in Flex.io

  2. In the pipe, you’ll see an initial Execute Task; make sure the language option is set to Python, and then overwrite the example “Hello, World” code with the following:

def flex_handler(flex):

# open the file on the cloud storage service
input_path = 'example-cloud-storage-api-source:/feed-file.csv'
input_file = flex.fs.open(input_path)

# read the data
data = input_file.read()

# write out the data
flex.output.write(data)

Change the input_path value to the path of the file you’d like to load. For example, if you’d like to load a file in a particular folder /temp/my-feed-file.csv, you’d set the value of input_path to example-cloud-storage-api-source:/temp/my-feed-file.csv

  1. After saving your changes, press the “TEST” button in the upper right of the Pipe Builder. You should see the file in the pipe output.

  2. In some cases, the data you want to expose in the endpoint might be zipped. If you need to unzip a file before exposing it through the API, use the following:

Convert the CSV file to JSON format

Next, transform the data into the output format. For this tutorial, we’ll assume the following output from the previous step, but your actual data may differ:

"item", "category", "description"
"123", "diamond", "1 caret, E color, round shape"
"465", "diamond", "2 caret, A color, square shape"
  1. Add a new Execute Task after the task that reads the file from the cloud storage service. In this task, select ‘Python’ as the language, then paste the following code:

Running the pipe, you’ll see something like:

[
{
"item": 123,
"category": "diamond",
"description": "1 caret, E color, round shape"
},
{
"item": 465,
"category": "diamond",
"description": "2 caret, A color, square shape"
}
]
  1. At this point, you’ve got the basic flow up that reads the file from storage and converts it from CSV to JSON. In practice, you may need to perform additional cleaning or transformation. For example, with this data, you may want to extract out the description info into separate key value. In this case, you might modify the convert execute step with the following:

Running the pipe, you’d see something like:

[
{
"category": "diamond",
"weight": "1",
"shape": "round",
"color": "E",
"item": 123
},
{
"category": "diamond",
"weight": "2",
"shape": "square",
"color": "A",
"item": 465
}
]

Create the API endpoint that returns the resulting JSON

Finally, 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-from-a-csv-in-cloud-storage.

  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-from-a-csv-in-cloud-storage/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, where {token} is your API Key.

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-from-a-csv-in-cloud-storage/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-from-a-csv-in-cloud-storage/run'
--header "Authorization: Bearer {token}"
'