Read the following guide for a full overview of how to use IMPORTDATA in Google Sheets.
According to recent data, 90% of data entry and extraction are still done using manual methods.
However, manually extracting data often involves copy-paste, which is tedious, time-consuming, and prone to error.
But if you want to automatically extract data from CSV files published online, you can use the IMPORTDATA function in Google Sheets.
Here’s how to use IMPORTDATA in Google Sheets, based on mini-tutorials and real examples.
Video Walkthrough: How to Use IMPORTDATA in Google Sheets
What is IMPORTDATA in Google Sheets?
IMPORTDATA is a function in Google Sheets that pulls data from a CSV or TSV file, based on a given URL. You can extract data from files in TSV or CSV format published on the web, such as on a website.
IMPORTDATA Function Syntax
In Google Sheets, the syntax for IMPORTDATA is:
=IMPORTDATA(url)
The URL must link to a CSV or TSV file in order to work.
The URL must be enclosed in quotation marks. However, you don’t need to use quotation marks if you’re referencing a cell that contains the URL.
IMPORTDATA in Google Sheets: Real Examples
1. Use IMPORTDATA to Pull a CSV File from a Website
Pulling data from a CSV file is one of the most common use cases for IMPORTDATA. Here’s how you do it.
First, copy the URL you want to pull the CSV data from. For our example, we’ll use this URL, an annual balance sheet in CSV file format.
Next, open the Google spreadsheet you want to pull the data into. Enter the IMPORTDATA formula and paste the URL inside.
=IMPORTDATA(“https://www.stats.govt.nz/assets/Uploads/Annual-balance-sheets/Annual-balance-sheets-2021-provisional/Download-data/annual-balance-sheets-2007-2021-provisional.csv“)
You must place the URL parameter in quotation marks.
Press Enter and the data will populate your spreadsheet.
IMPORTDATA retrieves all the available data from your source, in this case, more than 45,000 entries in our sample CSV data.
2. Use the IMPORTDATA and QUERY Functions to Limit Rows
IMPORTDATA can also import specific entries of data, rather than an entire dataset.
To accomplish this, you’ll need to leverage the Google Sheets QUERY function with IMPORTDATA to return specific data from a CSV or TSV file published online.
Here’s how you do it.
Enter a QUERY function before IMPORTDATA in your Google Sheets formula. The QUERY function consists of the data, query, and optional header parameters.
IMPORTDATA is now the first parameter in your QUERY function. This allows you to use the remaining arguments to change the imported data.
Your complete QUERY and IMPORTDATA formula should look something like this:
=QUERY(IMPORTDATA(“https://www.stats.govt.nz/assets/Uploads/Annual-balance-sheets/Annual-balance-sheets-2021-provisional/Download-data/annual-balance-sheets-2007-2021-provisional.csv”), “Select * “)
The formula above includes Select*, which retrieves all the data within the CSV file. To return a specific number of rows from the CSV file, you must include the LIMIT command in the QUERY function.
Enter the formula below in a blank cell:
=QUERY(IMPORTDATA(“https://www.stats.govt.nz/assets/Uploads/Annual-balance-sheets/Annual-balance-sheets-2021-provisional/Download-data/annual-balance-sheets-2007-2021-provisional.csv”), “Select * limit 20”)
Adding limit 20 limits the data import to the first 20 rows.
3. Use the IMPORTDATA and QUERY Functions to Limit Columns
Besides limiting the number of rows, you can also specify the number of columns to pull. You can still use the combined IMPORTDATA and QUERY functions, but this time, add the SELECT command to limit the columns.
Your formula should look like this:
=QUERY(IMPORTDATA(“https://www.stats.govt.nz/assets/Uploads/Annual-balance-sheets/Annual-balance-sheets-2021-provisional/Download-data/annual-balance-sheets-2007-2021-provisional.csv”), “Select Col2, Col6 limit 20”)
Adding Select Col2, Col6 limit 20 tells the function to return only the data in columns two and six until the 20th row.
4. Use the IMPORTDATA and QUERY Functions to Fetch Data Based on Conditions
You can use IMPORTADATA and QUERY functions to extract data from an online CSV file.
For example, you can retrieve only the data from rows containing values greater than 15,000 using the two functions and conditional operators such as >, <, and = in your statement.
To make it happen, enter the formula below:
=QUERY(IMPORTDATA(“https://www.stats.govt.nz/assets/Uploads/Annual-balance-sheets/Annual-balance-sheets-2021-provisional/Download-data/annual-balance-sheets-2007-2021-provisional.csv”), “Select * where Col7>15000 limit 20”)
The formula returns only the rows with entries in the seventh column (Column G) whose values are greater than 15,000.
Supercharge your spreadsheets with GPT-powered AI tools for building formulas, charts, pivots, SQL and more. Simple prompts for automatic generation.
You can also combine the VLOOKUP and IMPORTDATA functions to import data from CSV and TSV files based on conditions.
The IMPORTDATA function serves as the table array, and the column number serves as the third argument in your VLOOKUP function.
Read this ultimate guide to VLOOKUP in Google Sheets to learn more about this function.
Alternative to Using IMPORTDATA in Google Sheets
While using IMPORTDATA is useful for CSVs, it cannot import data from company systems or other data sources.
A more robust option is to use data connectors from Coefficient. Coefficient is a code-free solution that connects your Google spreadsheets to your business systems and data sources quickly and easily.
The app runs as a Google Sheets add-on you can install from the Google Workspace Marketplace. It allows you to import live data into Google Sheets and update it instantly or automatically by setting an auto-refresh schedule.
Give it a try for free now!
IMPORTDATA in Google Sheets: Common Issues
Like most Google Sheets formulas, IMPORTDATA has limitations, and you’re likely to encounter potential errors when using it.
Learn several of the most common IMPORTDATA problems and limitations you can experience below, including how to resolve them.
Limitation on the Number of URLs to Extract Data From
Google Sheets does not allow you to import data from multiple URLs simultaneously. You’ll need to have a separate formula for each URL.
You can use the IMPORTDATA function 50 times for each Google spreadsheet, although this could impact performance greatly.
Updating Limitations on Your Imported Data
While you can delete and retype your IMPORTDATA formula to force a refresh of your imported data, Google Sheets doesn’t offer a native functionality to set up custom and automatic update frequencies.
Google Sheets functions that import external data such as IMPORTFEED, IMPORTXML, IMPORTDATA, and IMPORTHTML automatically update hourly. However, you’ll need to use Google Apps Script to customize this.
File Size Limits
Google Sheets limits the size of the CSV or TSV file that you can import. You’ll see an error message if the file exceeds the maximum allowed size.
#N/A Error When Using IMPORTDATA in Google Sheets
An #N/A error usually indicates that your formula can’t find the CSV or TSV file.
Check your formula for incorrect arguments, misspellings, and missing or extra symbols, such as parentheses and quotation marks.
You can also download the TSV or CSV file from the source URL and import it directly to Google Sheets.
Google Sheet Size Issues
IMPORTDATA will not increase the number of columns if the CSV file you import exceeds the available columns. If this happens, Google Sheets will show an error message.
Check the number of columns in your source file first to ensure you have enough in your sheet, and add more when necessary.
Other Important Considerations When using IMPORTDATA in Google Sheets
Here are some key factors and limitations you should know about IMPORTDATA in Google Sheets.
- Ensure your source data (URL) has public access. The IMPORTDATA function can only extract data from URLs with public access. Your source data should be hosted on websites that are accessible without logging in.
- The source data should be less than 50,000 cells. IMPORTDATA can only import a maximum of 50,000 data cells at a time.
- The source data must be in TSV or CSV format. The IMPORTDATA function can only pull data from CSV or TSV files. If the data you want to retrieve is in another format, such as JSON or Excel, you must convert it into CSV or TSV format before importing using IMPORTDATA.
- Clean the data if possible. The data you import from CSV or TSV files using IMPORTDATA is raw and unfiltered. If the data has errors or inconsistencies, you’ll have to clean it first to make it usable and accurate.
- Understand rate limits. If you make many requests to the same URL, you might be rate limited by the server that provides the data.
Extract Data Easily Using the IMPORTDATA in Google Sheets
The IMPORTDATA function in Google Sheets allows you to pull data from publicly accessible URLs into your spreadsheets easily.
Harness IMPORTDATA effectively by knowing the various ways to use it and the common issues and limitations, including troubleshooting tips to resolve and work around these problems.
Also, you can use Coefficient to automatically import CSV data and data from other company systems in a single click.
Get started with Coefficient for free now to import your business data into Google Sheets seamlessly.
