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.
Table of Contents
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:
Let’s break the syntax down to better understand it:
=the equal sign is just how we start any function in Google Sheets.
IMPORTDATAis our function in this ultimate guide. We need to add another attribute to make it work flawlessly.
urlor 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:
- Always make sure to enclose the
urlin a quote-unquote symbol (“”).
- As aforementioned, Google only accepts up to 50
IMPORTDATAformulas 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.
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:
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
IMPORTDATAfunction 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.
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
- 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
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.
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:
- 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.
- Next, start our formula with an equal sign, followed by our function which is
- Add an open parenthesis “(“. You should see an auto pop-up message that will serve as your guide in writing your
- The fourth step is to paste the
urlof 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 (“”).
- End your formula with a close parenthesis “)“. Hit the Enter key to get your result.
- At this point, you should see the data that you want to import.
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. 🙂