The IMPORTDATA function in Google Sheets is used to retrieve CSV and TSV files from a website.
Syntax=IMPORTDATA(url)
url – The URL where the .csv or .tsv-formatted data is located.
Note: The value for url has to be enclosed in quotation marks or be a reference to a cell containing the URL text (without quotation marks).
Examples=IMPORTDATA("http://www.census.gov/2010census/ csv/pop_change.csv")
//The function loads the CSV file located at the URL in Google Sheets.=IMPORTDATA(D5)
//The function loads the .CSV file located at the URL in cell D5.
Copy spreadsheet with formulas and data
Contents
- The IMPORTDATA function
- Example 1: Using the IMPORTDATA function to retrieve a CSV file
- Example 2: Using IMPORTDATA with the QUERY function
- Example 3: Using IMPORTDATA with QUERY to retrieve certain rows
- Example 4: IMPORTDATA with QUERY to retrieve specific columns
- Example 5: Retrieve data based on conditions
What is the IMPORTDATA function?
Google Sheets has several utility functions that enable us to import all sorts of data from external sources, be it another spreadsheet, a website, or external files. We have discussed some of these functions in our articles IMPORTRANGE function in Google Sheets and how to import data from RSS and Atom feeds.
However, data can be stored in special formats known as CSV/ TSV. CSV stands for comma-separated values and TSV stands for tab-separated values; both are delimiter-separated formats. Essentially text files use commas and tabs to separate the different entities in a table.
In this article, we will learn how to use the IMPORTDATA function in Google to import CSV/TSV files from a website to our worksheets which present the data in a tabular form.
How to use the IMPORTDATA function in Google Sheets
We will not just import a CSV file. Google Sheets, as always, affords us room to combine the IMPORTDATA function with multiple functions to achieve more functionality and we’re going to use some of these. First, we begin with the basic usage case.
Example 1: Using the IMPORTDATA function in Google Sheets to Import a CSV file from a website
Importing CSV files is as simple as providing the needed parameters to the IMPORTDATA function. To do this, simply follow the steps below:
Step 1: Get the URL of the CSV file
Simply copy the link to the CSV file you intend to use from the website which hosts it.
For illustration purposes, we will use a CSV file located in a website at the URL below.
https://www.stats.govt.nz/assets/Uploads/Consumers-price-index/Consumers-price-index-September-2022-quarter/Download-data/consumers-price-index-september-2022-quarter-infoshare-data-updated.csv.This is spreadsheet data of the consumer price index for September. Using the IMPORTDATA function, we will download this file directly to our Sheets in a tabular format.
Step 2: Enter the IMPORTDATA function in a cell in Google Sheets
- Enter the IMPORTDATA function in an empty cell by typing =IMPORTD..
- Google Sheets autosuggest pops up. Select the function from the auto-suggest menu.
Step 3: Setup the IMPORTDATA function in Google Sheets
The only parameter we will be providing is the URL of the CSV file.
- Paste the URL you copied from step 1 into the IMPORTDATA function as shown below:
=IMPORTDATA("https://www.stats.govt.nz/assets/Uploads/Consumers-price-index/Consumers-price-index-September-2022-quarter/Download-data/consumers-price-index-september-2022-quarter-infoshare-data-updated.csv")Remember to enclose the URL in quotation marks.
- Press Enter. Since this is an operation over the internet, make sure you have a stable connection.
- The retrieval will take some time depending on the size of the file.
The formula we’ve used will fetch all the available data from the source. In this example, a record of data with over 17,000 entries.
We can already begin to see a problem. In a lot of cases, there might not be a need to work with that much data. What if we just wanted ten of the most recent entries or data based on some other criteria? This is where the Google Sheets QUERY function can come in handy.
In the remaining examples, we will learn how to use the QUERY function together with the IMPORTDATA formula in Google Sheets to return specific data from the CSV file.
Example 2: Using the IMPORTDATA function in Google Sheets and the QUERY function to import CSV files from a website
Follow the steps below to combine QUERY and IMPORTDATA:
- Enter the QUERY function into a cell.
The function needs three parameters – data, query, and an optional header. For a more detailed explanation check out our article on how to use a Query function in Google Sheets.
- Now, we will provide the IMPORTDATA function as the first parameter. By doing this we can use the Query function’s remaining parameters to modify the data we will pull to Google Sheets.
=QUERY(IMPORTDATA("https://www.stats.govt.nz/assets/Uploads/Consumers-price-index/Consumers-price-index-September-2022-quarter/Download-data/consumers-price-index-september-2022-quarter-infoshare-data-updated.csv"), "Select * ")
We have used Select * to indicate that we want to return all the data in the CSV file. But this isn’t that useful. Let us instead return a specific amount of rows from the CSV file.
Example 3: Using the IMPORTDATA function in Google sheets and QUERY to LIMIT the number of rows
Let us return only 10 rows of data from the CSV file instead. To do this, we will use the QUERY function’s LIMIT command
- Enter the formula below into a cell
=QUERY(IMPORTDATA("https://www.stats.govt.nz/assets/Uploads/Consumers-price-index/Consumers-price-index-September-2022-quarter/Download-data/consumers-price-index-september-2022-quarter-infoshare-data-updated.csv"), "Select * limit 10")- We specify that the rows should be limited to the first 10 using the Limit 10 part of the QUERY function.
- Press Enter.
We successfully imported only 10 rows from the CSV file.
Example 4: Using the IMPORTDATA function in Google sheets and QUERY to import a specific number of columns
Similarly, we could be in a situation where the data has so many columns, some of which might be irrelevant to our needs. We can use the SELECT command to import only specific rows.
Let’s see how.
- Enter the formula below into a cell
=QUERY(IMPORTDATA("https://www.stats.govt.nz/assets/Uploads/Consumers-price-index/Consumers-price-index-September-2022-quarter/Download-data/consumers-price-index-september-2022-quarter-infoshare-data-updated.csv"), "Select Col3, Col7 limit 10")- We have modified the formula to include “Select Col3, Col7 limit 10”
This will fetch only the data in the third and seventh column limited to 10 rows.
Example 5: Using the IMPORTDATA function in Google Sheets and QUERY to import data based on a condition
So far, we have dealt with explicitly specifying the data we want to import e.g a specific number of columns or rows. However, we can also import data based on conditions.
For instance, we might want to only see data that meets the criteria – data must be from rows that contain values greater than 100.
To do this, we simply use conditional operators such as <, >, =, in our statements
- Enter the formula below into a cell
=QUERY(IMPORTDATA("https://www.stats.govt.nz/assets/Uploads/Consumers-price-index/Consumers-price-index-September-2022-quarter/Download-data/consumers-price-index-september-2022-quarter-infoshare-data-updated.csv"), "Select * where Col3 > 50 limit 10") This will return only those entries in column 3 whose values are greater than 50.
Conclusion
The IMPORTDATA function in Google Sheets is very handy for importing CSV/TSV files directly to our sheets. Combine it with the Query function to extend its capabilities.
Some related questions
What is the difference between INPORTRANGE and IMPORTDATA?
The IMPORTRANGE function retrieves data from an external spreadsheet; we can access tables in different worksheets by supplying the URL and table name as the function’s parameters.
Syntax:
= IMPORTRANGE(Spreadsheet_url, tablename)On the other hand, the IMPORTDATA function in Google Sheets retrieves data from the web in CSV/TSV format. Google Sheets automatically displays the retrieved data as a table.
A CSV/ TSV format is a popular format used to store tabular data e.g database tables and spreadsheets data. To use them, however, one has to download them first from the online source, and then open them in spreadsheet software. The IMPORTDATA function makes this a simple process.
= IMPORTDATA(URL)How do I pull data from an HTML table to Google Sheets?
While the IMPORTDATA formula is ideal for importing tables in CSV/TSV format, it cannot import HTML tables. To do this, we can use a similar IMPORT function — the IMPORTHTML function.
See also
Check out other related articles on our blog
Import Data From Binance To Google Sheets