How to Easily Generate a JSON REST API from a CSV File

Hi, my name is Louis, and I’ve created QuickAPI, the fastest way to get an API up and running for your users.

Today, we’ll learn how to generate a JSON REST API from a CSV file. Including full querying abilities.

But first, a quick reminder on the topic of data formats and APIs:

CSV: the most common database

As its name — Comma Separated Values, implies, this old format is a series of values separated by commas. Simple as that. Usually, the first row is a header row that contains the label of the values. Note the separator is not always a comma. Other common separators are semi-columns or tabs

Name,Age,City
John,25,New York
Emily,32,San Francisco
Michael,28,Los Angeles
Name,Age,City
John,25,New York
Emily,32,San Francisco
Michael,28,Los Angeles

In this example, we have a CSV file with three columns: Name, Age, and City. Each row represents a different person, with their corresponding information.

CSV's simplicity and practicality make it one of the most widely used data formats. Almost everyone who has used Microsoft Excel or Google Sheets knows CSV.

JSON: simple for computers, simple for humans

JSON is short for JavaScript Object Notation. As you may have guessed, it stems from the Javascript Programming language.

Here’s what the above example would look like in JSON:

[
  {
    "Name": "John",
    "Age": 25,
    "City": "New York"
  },
  {
    "Name": "Emily",
    "Age": 32,
    "City": "San Francisco"
  },
  {
    "Name": "Michael",
    "Age": 28,
    "City": "Los Angeles"
  }
]
[
  {
    "Name": "John",
    "Age": 25,
    "City": "New York"
  },
  {
    "Name": "Emily",
    "Age": 32,
    "City": "San Francisco"
  },
  {
    "Name": "Michael",
    "Age": 28,
    "City": "Los Angeles"
  }
]

As you can see, it’s a less dense format; each row in our CSV file now takes up more space as the keys are repeated each time. But I’ll argue it is easier to read.

The main advantage of JSON over CSV is its ability to nest objects, allowing for more complex structure and data:

{
  "Name": "John",
  "Age": 25,
  "City": "New York",
  "Address": {
    "Street": "123 Main St",
    "City": "New York",
    "State": "NY"
  }
}
{
  "Name": "John",
  "Age": 25,
  "City": "New York",
  "Address": {
    "Street": "123 Main St",
    "City": "New York",
    "State": "NY"
  }
}

This format gained popularity with the rise of Javascript in the late 2000s, replacing XML in API communications.

Speaking of API…

API: how programs talk to each other

API stands for Application Programming Interface, but that’s not very important. What’s important is that it’s how software applications communicate and interact together. It’s a protocol, a contract shared between two or more computers to exchange information and perform actions.

APIs define the structure of requests and responses and specify the format in which data should be sent and received.

Let’s say you need to display the list of your followers; the app will request the data from the server so the app can display it to the user.

Untitled

Think of the API as a waiter in a restaurant. He gives you the menu so you know what to ask for, and when you have made your choice, he will go to the kitchen, order what you asked, and come back to serve you. All without you having to understand the intricacies of what’s happening in the kitchen.

One of the most widely known API forms is called REST.

A REST API allows different systems to communicate using standard methods such as GET, POST, PUT, and DELETE through URLs that we call endpoints.

Here’s what a simple REST API call looks like

GET https://quickapi.io/noop.json
GET https://quickapi.io/noop.json

And its response in JSON:

{
  "status": 200,
  "message": "You've correctly configured your QuickAPI endpoint"
}
{
  "status": 200,
  "message": "You've correctly configured your QuickAPI endpoint"
}

Fun fact: the address (URL) you see in your browser navigation bar is a GET request. Click the link to see it in action: https://quickapi.io/noop.json.

Okay, but why an API instead of a CSV file?

If you clicked on this article, there is a good chance you already know some of this, and you need to convert CSV to JSON and serve this data as an API.

But one last question remains before diving into the HOW. It’s WHY?

Why would anyone want to transform its CSV file into an API? Isn’t a CSV file already practical, easy, and all?

Yes BUT,

  • What if the file is huge (think 1GB or more)?
  • What if I don’t want to share the whole file?
  • What if I want to let people pick what data they need?
  • What if I want to charge money for using the data?
  • What if I want to let programs use my data on demand?
  • What if I want to search and filter the data easily?

Then, an API is the best answer to all these questions.

Let’s get to the how part.

How to create a REST API from a CSV file?

There are hundreds of ways to do this. If you're a developer, you should know about the Papaparse library, which handles CSV file parsing and manipulation. But we'll do simpler and use QuickAPI to get to our result faster.

Notes to developers

It's tempting to spin up a JS function and start parsing the CSV ourselves. However, there are lots of issues you shouldn't gloss over, such as rate-limiting, and streaming for large files. Once you're there, you're only half-way, as you need to setup a good query mechanism to allow users to filter and search your data. Anyway, let's get to our method.

The simplest way: by using QuickAPI

There are tools online that convert your CSV into JSON. But what should you do with that file?

If you want to give people restricted access and allow them to query that file and filter results, the easiest way is to use something like QuickAPI

QuickAPI will let you upload a CSV file and then transform it into a ready-to-use REST API in JSON format in no time. You can then let people query the file and share access to its content.

First, create a free account at https://quickapi.io/signup

  1. Choose a name for your project Creating a new project on QuickAPI

  2. Upload your CSV file and choose the type of each column

  • Integer for non-decimal numbers
  • Float for decimal numbers
  • Datetime for Date and Time
  • String for the rest Setting up the CSV for upload

  1. Create the related endpoint that you will be able to call to query your data Linking the file to the API endpoint

  2. You can now query your CSV file like any other API. You're ready to query

QuickAPI’s CSV endpoints are GET only, which means you cannot create, update or delete data, you can only query your data.

Speaking of which:

Search and filter

You may want to query specific parts of your CSV file, for example, to filter users by email or by a specific birthday. So here’s how you would do it:

Searching is available for all columns by appending query parameters to the endpoint URL.

Basic equality

In the above example, the step column is a number (Integer), so you can search by equality. Try adding &step=4 at the end of the URL.

https://demo.quickapi.io/api/sample-csv?qapikey=YOUR_API_KEY&step=4
https://demo.quickapi.io/api/sample-csv?qapikey=YOUR_API_KEY&step=4

Comparison operators

For number columns (Integer and Float), you can search for lower than or greater than by appending column modifiers at the end of column names.

  • [column_name]_lt for lower than
  • [column_name]_lte for lower than or equal
  • [column_name]_gt for greater than
  • [column_name]_gte for greater than or equal
https://demo.quickapi.io/api/sample-csv?qapikey=YOUR_API_KEY&step_lte=3
https://demo.quickapi.io/api/sample-csv?qapikey=YOUR_API_KEY&step_lte=3

Text search

Similarly, for String columns, you can search for text like so:

https://demo.quickapi.io/api/sample-csv?qapikey=YOUR_API_KEY&instruction=SENSITIVE
https://demo.quickapi.io/api/sample-csv?qapikey=YOUR_API_KEY&instruction=SENSITIVE
  • By default, the search is CASE SENSITIVE if you want to search regardless of the case, you can use the [column_name]_i modifier
https://demo.quickapi.io/api/sample-csv?qapikey=YOUR_API_KEY&instruction_i=InSeNsItIvE
https://demo.quickapi.io/api/sample-csv?qapikey=YOUR_API_KEY&instruction_i=InSeNsItIvE
  • By default, the search is PARTIAL, meaning results will be returned even if it only matches a text substring. You can use the Regexp ^ and $ markers if you need an exact match.
https://demo.quickapi.io/api/sample-csv?qapikey=YOUR_API_KEY&instruction_i=^This is an exact match$
https://demo.quickapi.io/api/sample-csv?qapikey=YOUR_API_KEY&instruction_i=^This is an exact match$

How to give someone access to your API?

Now that you know how to create an API from a CSV file, you probably want someone else to use it. That’s the inherent advantage of APIs: you control who has access and you can set limits. As opposed to sharing a whole file.

Luckily, QuickAPI handles this for us. Head over to the “Users” tab in your dashboard and click on the “Invite” button to share your API with the world.

Active users will show up in your dashboard

Active users will show up in your dashboard

Users will then be able to sign up to your APIs and get access to their own dashboard.

By default, QuickAPI will create a free plan limited to 100 credits so that invited users can start using the API right away.

You can change this in the “Pricing” tab. You may even want to monetize access to your data, in which case you’ll need to create a Stripe account to get paid.

Note: Creating paid plans for your API is only available on a Paid QuickAPI subscription.

Wrapping up

In this article, we explored how to easily generate a JSON REST API from a CSV file. We discussed the differences between CSV and JSON formats, as well as the importance of APIs in data communication. We also highlighted the benefits of creating an API instead of sharing the entire CSV file.

Additionally, we covered how to query the API for specific data, including searching text, filtering by dates, and filtering by numbers. We also touched on giving someone access to the API through user invitations in QuickAPI.

By following the steps outlined in this article, you can easily generate a JSON REST API from a CSV file and leverage the power of APIs to share and query data efficiently.

Remember, whether you're a developer or not, understanding how to generate a JSON REST API from a CSV file can be valuable in various domains such as data science, marketing analysis, and research.

So go ahead and explore the possibilities of creating your API from a CSV file. Happy building !