How to Use IMPORTDATA in Google Sheets

Last Modified: March 3, 2023 - 8 min read

Hannah Recker

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.

https://www.stats.govt.nz/assets/Uploads/Annual-balance-sheets/Annual-balance-sheets-2021-provisional/Download-data/annual-balance-sheets-2007-2021-provisional.csv

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.

spreadsheet ai
Free AI-Powered Tools Right Within Your Spreadsheet

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.

Set Spreadsheet Data on Refresh

Try the Spreadsheet Automation Tool Over 300,000 Professionals are Raving About

Tired of spending endless hours manually pushing and pulling data into Google Sheets? Say goodbye to repetitive tasks and hello to efficiency with Coefficient, the leading spreadsheet automation tool trusted by over 300,000 professionals worldwide.

Sync data from your CRM, database, ads platforms, and more into Google Sheets in just a few clicks. Set it on a refresh schedule. And, use AI to write formulas and SQL, or build charts and pivots.

Hannah Recker Growth Marketer
Hannah Recker was a data-driven growth marketer before partying in the data became a thing. In her 12 years experience, she's become fascinated with the way data enablement amongst teams can truly make or break a business. This fascination drove her to taking a deep dive into the data industry over the past 4 years in her work at StreamSets and Coefficient.
Google icon
300,000+ users on Google Marketplace
Wait, there's more!
Connect any system to Google Sheets in just seconds.
Get Started Free

Trusted By Over 20,000 Companies