With the IMPORTDATA function in Google Sheets, you can easily import a Comma-Separated Values (CSV) file into Google Sheets in a tabular format. The IMPORTDATA function also supports importing Tab-Separated Values (TSV) files.

CSV and TSV files are simple text formats that store and exchange tabular data between applications. What are the differences between these formats, and how are they used?

• CSV: The values are separated by commas.
• TSV: The values are separated by tab characters.

These formats allow data to be exchanged between systems that support such file formats. Google Sheets supports both formats, and the IMPORTDATA function is one method for bringing this data into Sheets while maintaining the tabular structure.

In some cases, the imported data may not retain its tabular form. If this happens, you can use functions like QUERY or SPLIT to properly adjust and format the imported data.

IMPORTDATA Function in Google Sheets – Formula Examples

Syntax of the IMPORTDATA Function:

IMPORTDATA(url)

Example Formula:

Google provides thorough documentation on the IMPORTDATA function, and I’ll demonstrate a similar example here. Additionally, I’ll show how to limit the imported data to specific rows, columns, or even a single cell.

In this example, we’ll import a CSV file containing population estimates from the U.S. Census Bureau’s website. This data comes from their population estimates program for U.S. cities and towns between 2020 and 2023.

To use the IMPORTDATA function:

• Open a new Google Sheets file.
• In cell A1, enter the following formula:

=IMPORTDATA("https://www2.census.gov/programs-surveys/popest/datasets/2020-2023/cities/totals/sub-est2023_1.csv")

This formula will import the population estimate data in a tabular format.

Apply Data Manipulation Techniques to Imported Data

You can manipulate the imported data using the QUERY function to filter or limit the rows and columns returned.

Limit Rows Using QUERY with IMPORTDATA

You can limit the number of rows imported with a QUERY formula like this:

=QUERY(IMPORTDATA("https://www2.census.gov/programs-surveys/popest/datasets/2020-2023/cities/totals/sub-est2023_1.csv"), "SELECT * LIMIT 10")

This formula limits the output to the first ten rows of the imported data. You can adjust the number to return more or fewer rows as needed.

Limit Columns Using QUERY with IMPORTDATA

The default dataset contains 15 columns, but many of them might be irrelevant. You can limit the columns using the following formula:

=QUERY(IMPORTDATA("https://www2.census.gov/programs-surveys/popest/datasets/2020-2023/cities/totals/sub-est2023_1.csv"), "SELECT Col9, Col11, Col12, Col13, Col14")

This formula returns only columns 9 and 11 to 14 from the dataset.

You can replace these column references with others based on your requirements.

Use Conditions with IMPORTDATA

While the IMPORTDATA function itself doesn’t support conditions, you can combine it with the QUERY function to filter data based on specific conditions.

For example, to import only the data for Allgood town:

=QUERY(IMPORTDATA("https://www2.census.gov/programs-surveys/popest/datasets/2020-2023/cities/totals/sub-est2023_1.csv"), "SELECT Col9, Col11, Col12, Col13, Col14 WHERE Col9='Allgood town' ")

This formula imports only the rows where column 9 equals “Allgood town” and selects the specified columns.

Importing TXT Files Using the IMPORTDATA Function

In addition to CSV and TSV, you can also import text (TXT) files with the IMPORTDATA function in Google Sheets. However, the output may not always be in a tabular form.

To format the imported text into a table, you can use the SPLIT function. Here’s a generic formula:

=ARRAYFORMULA(SPLIT(QUERY(IMPORTDATA("your_text_file_URL"), "SELECT * OFFSET 5", 0), " "))

In this formula:

• Replace “your_text_file_URL” with the actual URL of the TXT file.
• Adjust the OFFSET value (currently 5) to skip the appropriate number of rows if needed.

Alternatively, if the text file uses a consistent delimiter (like a comma), a simpler version can work:

=ARRAYFORMULA(IFERROR(SPLIT(IMPORTDATA("text_file_URL"), ",")))

In this version, the system splits the data by commas. If your file uses a different delimiter, like a semicolon, replace the comma in the formula with the correct delimiter.

Conclusion

The IMPORTDATA function in Google Sheets is a powerful tool for importing external data in CSV or TSV formats while maintaining a tabular structure. If your imported data isn’t formatted correctly, you can use additional functions like QUERY and SPLIT to manipulate and adjust the data.

If you have any questions about using the IMPORTDATA function in Google Sheets, feel free to ask!

Resources

• HYPERLINK
• IMPORTFEED
• IMPORTHTML
• IMPORTRANGE
• ISURL
• ENCODEURL