The IMPORTDATA formula in Google Sheets is quite a handy tool. It helps us fetch data, that is stored in .csv (comma separated values) or .tsv (tab separated values) files, from a location on the web.
Syntax
IMPORTDATA(url)
- url – this is the address (uniform resource locator) on the web, where the .csv file or .tsv file is located. This can take two forms.
- A valid and fully qualified location in the form of text (enclosed in double quotes). For example: “http://www.census.gov/2010census/csv/pop_change.csv”
- Or it can be a reference to a cell (like B1) within Google Sheets, where the url is stored.
It might be useful to note that Google Sheets accommodates up to 50 IMPORTDATA formulas on a single spreadsheet.
Usage: IMPORTDATA Formula
Case # 1: IMPORTDATA(“location of the csv or tsv file located on the web”)
In the snapshot below, we are trying to import the data into the cell B3, which therefore becomes the destination to key in the IMPORTDATA formula.
We ensure the url is valid, and then enter the formula properly (i.e. enclose urlwithin double quotes) and hit the Enter key. Below is what we see as Google Sheets tries to fetch the data. Bigger the file, the longer it takes to finish the data import.
While the data import is in progress, the cell in which we keyed in the formula, looks like it returned as an error (as indicated by a little red speck on the top right corner of the cell). But, it isn’t an error, just a transient state. Hover the mouse on the cell, to see the following description.
As soon as Google Sheets loads the data, the red speck disappears. You’ll notice the data extends from the formula cell (B3) to right and also further down.
So, it is of utmost importance that we keep the expected real estate of the result clear of any preoccupied values. Otherwise, IMPORTDATA formula might cough up a #REF! error. For example, following image explains what could happen, if we had any values, for instance, in cell C9.
Case # 2: IMPORTDATA(reference to a cell where url is stored)
Here, we have already stored the url to the .csv in the cell B1. So, we’ll use that cell to refer in the IMPORTDATA formula, and the result is same as that of the first case.
Learn in this blog post how to easily import RSS feed to Google Sheets.
