pull data from an API

What is an API?

API (Application Programming Interface), is a software communication protocol that allows two applications to talk to each other. By exchanging data, one application would be able to access certain information from the other so certain tasks can be performed. When we pull our phone for weather data, a lot of data objects would hit your weather app from a server that stores all the weather information.

Most modern applications use APIs to connect with each other and exchange data. What if you want to pull data from an API endpoint? That means that you’ll need an app that would understand the API and the data behind it.

API data formats

There are mainly 3 types of data that and API returns. They’re JSON objects, CSV, and XML. Each format has its own particular strengths and weaknesses.

JSON objects

JSON is the most popular format for API calls. It consists of key-value pairs and arrays that look like the example below.


"crust": "original",

"toppings": ["cheese","pepperoni", "garlic"],

"status": "cooking"


One core advantage of using JSON is speed. Since it is compact and more parsable, it runs faster than XML, another popular data interchange standard prior to JSON. It’s also very scalable and supports a larger number of data objects sent from the server. Though many would argue that it’s not as secure as XML or CSV format.


CSV is a data storage format that stores data values (plain text) in a list format separated by commas. It’s actually more compact and even faster than JSON, but it’s rarely used in a web development environment since it lacks a hierarchical structure. For what it lacks in scalability, it is more accessible due to its tabular format. A spreadsheet or relational database can easily access a CSV file while providing a variety of functional features in analytics and data manipulations.


XML, as previously mentioned, was the data exchange format for API prior to JSON. It’s a markup language that’s both human and machine readable. Though it’s not compact and optimized for read speed. It’s considered verbose and redundant when compared to JSON. Though XML is more suited for combining information sets from different systems such as metadata.

How to pull data from an API?

Solution 1: Pull API data manually on a browser

The most straightforward way of accessing data from an API endpoint is simply viewing it in the browser. As long as you’re connected to the internet, you should be able to preview most API’s data regardless of its format.

Updating the data however requires you manually refreshing the URL address and requesting new data points from the API endpoint. This may work well if you only want to download a few responses. When your API updates more frequently, viewing it on a web browser will not be effective.

access API on a browser

Solution 2: Pull API data via Python

Python can be a powerful way to collect data from an API. Though it’s a programming language, it comes with many useful packages that support not only data collection but also data manipulation and storage.

To get started, you’ll need to install Python. Then you can import the required libraries such as “requests” and “JSON”. In the example below, you can print out the status of the API before pulling the data.

After reading the JSON objects, you may now normalize them into a relational table for further analysis.

Solution 3: Save API data automatically into a database

Another way of pulling data from an API is through a cloud database management software. Here you can use a pre-build API connector to systematically request an API endpoint and store the data in a database for future applications.

The main advantage of using this method is so that you can pull and store the data automatically. The setup can be configured upfront, then there are minimum maintenance efforts needed afterward.

  1. Configure the API endpoint

An API endpoint can be complex. It requires a good understanding of its technical design to effectively parse it into a system. For example, your API looks like this


You can directly paste the URL endpoint into the URL input box. Alternatively, you can enter the parameters respectively.

configure data from an API

By inserting the values into the boxes, you can use dynamic date expressions to request data beyond a date specified such as from 2021-01-01 to {year}-{month}-{day}.

dynamic date expression from API

2. Preview the API data

After the API data is successfully understood and connected, you should see a preview popup that contains a snippet of the data feed.

After the connection has been established, you will see a Syncing icon next to your API resource. It may take a few minutes to several hours depending on the data size. Once your data is ready, you will receive a notification email.

dynamic date expression from API

3. Store data into a database

After data is pulled from the API and the initial sync is complete, it will be stored in a tabular database that will sync with the resource. When the API updates, the resource should update its content and your project table should reflect that update as well.

store API data in a database

4. Transform the API data

Within the database, you can freely transform it into your desirable table format. There’re a number of built-in SQL-based actions you can apply to this data pulled from the API.

acho pivot table

5. Export the data to an application

Once your table is built, you can use it to power an application such as a dashboard,  spreadsheet, or an embedded table on your website.

export data from Acho to an application

6. Check and maintain the pipeline

After the connection has been made between your Acho data project and your destination application, you should check and maintain the pipeline to ensure that data is transferred timely with no delays or breakages.

dynamic date expression from API

Now your API data should be parsed and stored in a database now, you can perform analytics or use it to support an application. If there's a certain application that you need help setting up with, feel free to contact us through the chatbox on the bottom right or email.

Follow us on Twitter @team_acho for the latest product news.

Email us contact@acho.io for any questions you may have.