With the help of the Importdata function in Google Sheets, you can import a Comma-Separated Value file (CSV) to Google Sheets in tabular form. The Importdata function also supports Tab Separated Value file (TSV) import.
Both the CSV and TSV are simple text file formats. What are the peculiarities of these file formats and what are their purposes?
The CSV and TSV (file extensions) file formats are used to store database tables or spreadsheet tables in text format.
In CSV, the field values are separated by a comma but in TSV it’s by a tab character. Both file formats are using to exchange data between supporting applications. See the relevant Wiki page here – CSV | TSV.
Google Sheets supports such file formats and the Importdata is an example of this. You can import such text formats to Google Sheets keeping the tabular form.
In certain cases, the imported data may not keep the tabular form. In such situations, you can use the Query or Split function to manipulate the imported data.
Importdata Function in Google Sheets – Formula Examples
The Syntax of Importdata Function:
IMPORTDATA(url)
Importdata Example Formulas:
This Google Doc help article includes ample information about the use of the Importdata function in Google Sheets.
I am following the same example here. Additionally, you can learn how to limit the imported data to certain rows, columns or even to a single cell.
Here is one working CSV file link:
Link: https://www.census.gov/2010census/csv/pop_change.csv
If you open this link you can see the output as below.
Note: The link is working at the time of writing this post.
This is the population change data published by the U.S. Census (2010). Let me show you how to import this data in Google Sheets in tabular form.
Open a new Google Sheet and enter this formula in cell A1.
=IMPORTDATA("https://www.census.gov/2010census/csv/pop_change.csv")
This Importdata formula would import the census data in comma delimited format as below in the tabular form.
Apply Data Manipulation Techniques in Imported Data (Query with Importdata Function)
You can use Query with Google Sheets Importdata as below to limit the rows in output.
Limit the Rows Using Query in Importdata Function in Google Sheets
Formula:
=query(IMPORTDATA("https://www.census.gov/2010census/csv/pop_change.csv"),"Select * limit 4")
Change the number four to the total number of rows that you want in the formula output. The above Query and Importdata combination return 4 rows excluding the column label.
Limit the Columns Using Query in Google Sheets Importdata Function
The above formula imports a total of 23 columns. Most of the columns may irrelevant to you.
Here I am limiting the number of columns returned using Query with the Importdata formula.
=query(IMPORTDATA("https://www.census.gov/2010census/csv/pop_change.csv"),"Select Col22,Col23")
Result:
Change the columns in the last part of this Query to return different columns.
Importdata Function with Conditions
The function, by default, doesn’t support conditions in it. Here again, the Query can come in handy.
=query(IMPORTDATA("https://www.census.gov/2010census/csv/pop_change.csv"),"Select * where Col1 = 'California'",3)
This formula only imports the census change data for the region California.
My imported data is not in tabular form. How to make it in tabular form? Read on to find the answer to this question.
Import Txt File Using Importdata Function in Google Sheets
Other than CSV and TSV you can import TXT file using the function Importdata in Google Spreadsheets.
If you use Google Sheets Importdata function to import a text file, the output won’t be in tabular form.
You can, depending on the file, correct that using the Split function.
I don’t have a file to show you an example. So I am providing you a generic formula.
=ArrayFormula(split(QUERY(importdata("your txt file URL.txt"),"Select * offset 5",0)," "))
In this replace the URL with your text file URL that ends with the TXT file extension. Also, change the Offset 5 to the number of rows to offset on the top.
The following simple version will also work well.
=ArrayFormula(IFERROR(split(IMPORTDATA("text file URL.txt"),",")))
In this formula, I have omitted the Query as it’s not a must. We just want to import the data and split the content into columns and rows (tabular form). So this formula will work well in that case.
But please do note that I have put the comma as the delimiter in the just above formula. If your text file is separated by semicolon, then change that accordingly in the formula (see the last part of the formula, after the URL)
That’s all. Any doubt about using the Importdata function in Google Sheets, please let me know.
Related Reading:
I tried your last example, where my “csv” file is delimited with a pipe.
There’s a problem with rows of data that don’t have any data between the pipes, it eliminates them from the results for some reason, putting data in the wrong columns.
What’s causing this?
Query:
=ArrayFormula(IFERROR(split(IMPORTDATA("url_to_csv_data"),"|")))
Hi, Paul,
If possible, please share a sample of your data. You can share the URL below which won’t be published.
Can you do a query to ignore certain columns? Like
"Select * except(col2,col7)"
Hi, Ivan,
Nope! Instead, you can include the required columns like
"Select Col1, Col2, Col3, Col6"
. I know it’s a difficult task to include so many columns likewise.The good thing is that you can do it dynamically.
How to Get Dynamic Column Reference in Google Sheets Query.
Hi Prashanth
Thanks for your answer.
Here is the url with the Sheet.
– link (Sheets) removed by the admin –
Hi, PETIT,
Wrap the cell reference with the T function.
=importdata(t(D8))
Best,
Hello! I need some help with importdata function. Can you please help me?
Here is my formula (not finished)
={IMPORTDATA(D4); IMPORTDATA(D5); IMPORTDATA(D6); IMPORTDATA(D7); IMPORTDATA(D8); IMPORTDATA(D9); IMPORTDATA(D10); IMPORTDATA(D11); IMPORTDATA(D12); IMPORTDATA(D13)}
(i need it to go to D978)
First question: do you know a way not to make it manually? (maybe the easiest question)
Second question: cells from D4 to D978 are, in fact, dates as 2019-08-27
I need the dates written in the first (for example) column for every CSV I import.
Is this possible with a simple formula? Thanks for your help and your daily blog.
Hi, Petit,
Can you share a demo sheet or screenshot link?
To capture your Google Sheets screenshot and get the link you can depend on online screenshot tool like “Lightshot”. Here is the link to that site: https://prnt.sc/
Best,