How to Use IMPORTDATA Function in Google Sheets

IMPORTDATA Function in Google Sheets
How to Use IMPORTDATA Function in Google Sheets – Sheetaki

The IMPORTDATA Function in Google Sheets is useful if you want to bring in a Comma-Separated Value (.CSV) file and/or a Tab Separated Value (.TSV) file to the spreadsheets.

The IMPORTDATA function comes handy if you want to move a .CSV or .TSV file data from the web to Google Sheets. It is commonly used when you are dealing with tabular information such as sales, population, and statistics. The IMPORTDATA function does this simply by adding the URL of a given website.

Let’s take an example.

Say I am tasked to work on a research paper about the population change in the U.S. Normally, data like this is shown as a clickable link. The only dilemma that I will have is how to take this data from the web to Google sheets without messing up its format, spacing, and things like that.

The answer is simple.

We will make use of the IMPORTDATA function to transport this file smoothly from the web to the Google Sheets. You only have to follow three easy steps:

  • First, copy the URL of the file. The one that starts with http://. If it isn’t available, you can hover your mouse to the said file or link, right-click, then click on Copy Link Address.
  • Then, paste it on the active cell. (Where you started your formula)
  • Lastly, hit the Enter key, then be mesmerized with how it works!✨

It’s beneficial to know that Google only allows up to 50 IMPORTDATA formulas on a single spreadsheet. Also, the larger the file that you want to import, the slower it loads.

 

 

The Anatomy of the IMPORTDATA Function

The syntax or the way we write the IMPORTDATA function is:

=IMPORTDATA(url)

Let’s break the syntax down to better understand it:

  • = the equal sign is just how we start any function in Google Sheets.
  • IMPORTDATA is our function in this ultimate guide. We need to add another attribute to make it work flawlessly.
  • url or Uniform Resource Locator (URL) known as in the internet world. This is where your .csv file or .tsv file is located. Usually, it starts with https://. (e.g.  http://www.census.gov/2010census/csv/pop_change.csv). Two ways to get the URL:
    • If it’s available on the website, you can easily hover your mouse to the file, right-click, then click Copy Link Address; or
    • In case the URL is already in the Google Sheet, you can easily have it as a reference. Just click the cell where the URL is located.

⚠️ Now a few notes before using the IMPORTDATA Function:

  1. Always make sure to enclose the url in a quote-unquote symbol (“”).
  2. As aforementioned, Google only accepts up to 50 IMPORTDATA formulas in one spreadsheet.

 

 

A Real Example of Using IMPORTDATA Function

Take a look at the example below to see how IMPORTDATA function is used in Google Sheets.

IMPORTDATA Function in Google Sheets

As you can see in the example above, we used the IMPORTDATA function to retrieve the Census’ population change data. The function is as follows:

=IMPORTDATA(“http://www.census.gov/2010census/csv/pop_change.csv”)

Here’s what the example above does:

  • We selected our data from the Census’ website, http://www.census.gov.
  • We checked if the file is in a .csv format. Note that, the IMPORTDATA function only works for .csv and .tsv files.
  • After we made sure that the file that we want is in .csv format, we then took its URL. We right-clicked on the said file, then we clicked on Copy Link Address.
  • On the Google Sheet, we selected A1 as our active cell. This is where we want to paste our URL.
  • You can now paste the URL on the active cell by using the shortcut key, CTRL+V.
  • We waited for our data to load.
  • Viola! The Census’ Population Change data is up!
Now, what if our URL is already in the Google Sheets. How can we pop it up? It’s easy. Take this example below.

IMPORTDATA Function in Google Sheets

Say that we are working on a spreadsheet with 10 or more URLs and each URL has its own corresponding tabular data. The file will slow down for sure if we put so many tables in it. Here’s where referencing comes into the picture.

In the example above, we showed how referencing in IMPORTDATA function works.

  • The URL was already given in cell B2.
  • Therefore, our first step will be, to click on an active cell, a cell where we want our data to populate. In my case, I selected A3.
  • We started off by writing the IMPORTDATA function.
  • Then, we selected the cell where the URL is located. In my case, I selected B2.
  • We hit the Enter key and wait for the data to load.
  • Yes! We’ve successfully used the IMPORTDATA !

Easy, right?

You may make a copy of the spreadsheet using the link I have attached below:

Have a feel on how to work with this formula. Try it out for yourself.

I made 2 sheets for you. One, where the URL is provided, and another, where the URL is not provided.

Let’s begin writing our own IMPORTDATA function in Google Sheets.

 

 

How to Use IMPORTDATA Function in Google Sheets

The IMPORTDATA Function in Google Sheets is probably one of the easiest functions. To use it, we:

  1. Go ahead and click on any cell to make it active. This is where you will put your formula. For this guide, I will be selecting A1.

IMPORTDATA Function in Google Sheets

 

  1. Next, start our formula with an equal sign, followed by our function which is IMPORTDATA.

IMPORTDATA Function in Google Sheets

 

  1. Add an open parenthesis “(“. You should see an auto pop-up message that will serve as your guide in writing your IMPORTDATA formula.

IMPORTDATA Function in Google Sheets

 

  1. The fourth step is to paste the url of the file that you want to import to the spreadsheet. For this guide, we will use the suggested sample, http://www.census.gov/2010census/csv/pop_change.csv. Do not forget to enclose it in a quote-unquote symbol (“”).

IMPORTDATA Function in Google Sheets

 

  1. End your formula with a close parenthesis “)“. Hit the Enter key to get your result.

IMPORTDATA Function in Google Sheets

 

  1. At this point, you should see the data that you want to import.

IMPORTDATA Function in Google Sheets

 

That’s it. Well done! 👏🏆

You can now use the IMPORTDATA function together with the other numerous Google Sheets formulas to create even more powerful formulas that can make your life much easier. 🙂

 

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content.

 

0 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like