Watch Video – IMPORTDATA Function in Google Sheets
IMPORTDATA function is Google sheets allows you to quickly import the data from the URL of a .csv (comma separated value) or .tsv (tab separated value) file.
This can be useful if you're working with data that is available online in a .csv/.tsv file. Using IMPORTDATA function, you can instantly get the data in Googe Sheets (instead of going the longer route of downloading the .csv/.tsv file and then copy-paste and format the data).
In this tutorial, I will show you how to import the entire data set or part of a dataset using the IMPORTDATA function in Google Sheets.
Before we get into the example, let's first look at the syntax of the function.
IMPORTDATA Function – Syntax
- URL – this function only takes one argument. You need to specify the complete URL of the file location and make sure it is with double quotes. If you have the URL in a cell in Google Sheets, you can also specify the cell reference instead of the URL. For example, if the URL is in cell A1, the formula would be =IMPORTDATA(A1)
Now let's take an example and see how to fetch the data using IMPORTDATA function in Google Sheets.
Example 1 – Fetch All the Data from a URL using IMPORTDATA Function
Below is the URL of the 2010 census data of population change in the US (state-wise).
If you go to this URL, it will open the .csv file that looks as shown below:
It looks like a lot of data with no discernable structure.
If you have to work with this data, it is better to first import it in Google Sheets.
Below is the formula that will get all this data into a worksheet in Google Sheets:
Note that you have to enter the entire URL in double quotes. Also, you need to specify the entire URL, including the ‘http' or ‘https' part.
As soon as you enter the formula in Google Sheets, you may see the text ‘Loading' in the cell. It might take a few seconds to fetch the data and populate the worksheet.
There are a couple of important things you need to keep in mind when using the IMPORTDATA function in Google Sheets:
- The function automatically fills the cells in the worksheet based on the data in the csv/tsv file. In case, some of the cells already have some data, the IMPORTDATA function would return an error. So make sure you have enough empty cells available to accommodate the data from the csv/tsv files.
- Ensure you're using the correct version of the URL. If you use ‘http' instead of ‘https', it will give you an error.
Example 2 – Fetch Specific Column from a Data using IMPORTDATA Function
If you use the IMPORTDATA function, it will fetch the entire data from the CSV/TSV file into Google Sheets.
But if you're not interested in the entire data set and only a few columns, you can do that by using a combination of VLOOKUP function with IMPORTDATA.
For example, if you only want the 2010 population data for five specified states, you can use the below formula:
Note that in the above formula, we have used IMPORTDATA function as the table array for the VLOOKUP function. Since we only wanted the data for 2010 population, we have used 12 as the column number (the third argument of the VLOOKUP function).
You May Also Like the Following Google Sheets Tutorials:
- Using Query Function in Google Sheets.
- Using IMPORTFEED in Google Sheets to Fetch Feed from URL.
- Using IFERROR Function in Google Sheets.
- How to Use COUNTIF Function in Google Sheets.
- Using IF Function in Google Sheets.
- Using IFS Function in Google Sheets to Test Multiple Conditions.