Import CSV and Excel Data Into Google Sheets

In the previous tutorials, we’ve been talking about how you can import data from another sheet in the same spreadsheet and from other spreadsheets to Google Sheets. In this tutorial, let’s focus on importing data from CSV, TSV, and XLSX files to Google Sheets.

  • CSV – files that contain records of data with comma-separated values
  • TSV – files that contain data separated with tabs in plain text format
  • XLSX – the format of Microsoft Excel Workbooks

How to import CSV/TSV to Google Sheets

In Google Sheets, there is a dedicated function called IMPORTDATA to import CSV and TSV files from a given URL. Here is the IMPORTDATA syntax:

=IMPORTDATA("URL")

The first and only parameter of the function, URL, is the URL of a CSV or TSV file.

How to use the IMPORTDATA function

Let’s import a data set from a CSV file stored on openweathermap.org. Its URL is:

http://samples.openweathermap.org/storage/history_bulk.csv?appid=b1b15e88fa797225412429c1c50c122a1

Here is how the IMPORTDATA formula will look and work:

=IMPORTDATA("http://samples.openweathermap.org/storage/history_bulk.csv?appid=b1b15e88fa797225412429c1c50c122a1")
Import CSV to Google Sheets

Note: Beware of importing zipped CSV files using IMPORTDATA. For example, here is an example of a zipped CSV stored on quandl.com:

https://www.quandl.com/api/v3/databases/MX/metadata?api_key=eVEFx_xdJBXEHGnmu3sc

If you use this URL in the IMPORTDATA formula, here is what you’ll get:

=IMPORTDATA("https://www.quandl.com/api/v3/databases/MX/metadata?api_key=eVEFx_xdJBXEHGnmu3sc")

How to import CSV files stored on Google Drive, Dropbox or OneDrive

Unfortunately, a regular IMPORTDATA formula doesn’t work smoothly with CSV files stored on GDrive, Dropbox, or OneDrive. You’ll have to modify the formula and perform some manipulations for each case.

Import CSV files from Google Drive

Here are the steps you should take:

  • Share the Google Drive folder containing the file. Choose “Anyone with the link” and set the user role to “Viewer“. 
  • Right-click on your CSV file and select “Get link“. Copy the link, which may look like this:

https://drive.google.com/file/d/1QNFRHpe0Y7eh5dgTkPzNT-MilxPfhgU4/view?usp=sharing

  • Copy the ID part of the link (1QNFRHpe0Y7eh5dgTkPzNT-MilxPfhgU4) and append it into the following URL sample:

https://drive.google.com/uc?export=download&id=

You should get the following: 

https://drive.google.com/uc?export=download&id=1QNFRHpe0Y7eh5dgTkPzNT-MilxPfhgU4

Now you can use this URL for your IMPORTDATA formula:

=IMPORTDATA("https://drive.google.com/uc?export=download&id=1QNFRHpe0Y7eh5dgTkPzNT-MilxPfhgU4")
Import CSV to Google Sheets

Import CSV files from OneDrive

Use the following IMPORTDATA formula syntax to import CSV files from OneDrive:

=ARRAYFORMULA(IFERROR(SPLIT(IMPORTDATA("onedrive-csv-url"),";")))

The trickiest part here is how to get onedrive-csv-url: 

  1. Right-click on your CSV file and select “Embed“. Click “Generate” to get the embedding code of your CSV file.

Example:

<iframe src=”https://onedrive.live.com/embed?cid=EC25D9990D879C55&resid=EC25D9990D879C55%211498&authkey=AE20BwwO1PPaKdg” width=”98″ height=”120″ frameborder=”0″ scrolling=”no”></iframe>

  1. Get the URL from the embedding code. 

Example:

https://onedrive.live.com/embed?cid=EC25D9990D879C55&resid=EC25D9990D879C55%211498&authkey=AE20BwwO1PPaKdg

  1. In the URL, replaceembed” with “download“.

Example:  

https://onedrive.live.com/download?cid=EC25D9990D879C55&resid=EC25D9990D879C55%211498&authkey=AE20BwwO1PPaKdg

Use this CSV URL in the IMPORTDATA modified for OneDrive:

=ARRAYFORMULA(IFERROR(SPLIT(IMPORTDATA("https://onedrive.live.com/download?cid=EC25D9990D879C55&resid=EC25D9990D879C55%211498&authkey=AE20BwwO1PPaKdg"),";")))
Import CSV into Google Sheets

Import CSV files from Dropbox

To import CSV files stored on Dropbox, you’ll need the same IMPORTDATA formula syntax as for OneDrive:

=ARRAYFORMULA(IFERROR(SPLIT(IMPORTDATA("dropbox-csv-url"),";")))

And, again, the trickiest part is to get dropbox-csv-url:

  • Get a shared link of your Dropbox CSV file. For this, point out your CSV file on Dropbox => click “Share” => click “Create link” => click “Copy link”.

Example of a Dropbox shared link:

https://www.dropbox.com/s/r73ppy9wdkoi4qd/Driver_Activity_Hour.csv?dl=0

Replace “dl=0” with “raw=1” in the shared link of your Dropbox CSV file. You should get the following URL:

https://www.dropbox.com/s/r73ppy9wdkoi4qd/Driver_Activity_Hour.csv?raw=1

Use this modified URL in the IMPORTDATA formula:

=ARRAYFORMULA(IFERROR(SPLIT(IMPORTDATA("https://www.dropbox.com/s/r73ppy9wdkoi4qd/Driver_Activity_Hour.csv?raw=1"),";")))

How to import CSV data into Google Sheets without formulas

IMPORTDATA works pretty well unless you need to import CSV data from GDrive, OneDrive or Dropbox. If you want to avoid the manipulations described above and complex formulas, you can use the CSV importer by Coupler.io. It is a Google Sheets add-on for data import. Here is how it looks in a spreadsheet.

To import data using the CSV importer, you need to insert the URL of your CSV file in the respective field – that’s it. For more on how it works with the previously mentioned file storage sites, read How to Sync Your CSV File on OneDrive or Dropbox with Google Sheets.

How to import XLSX files to Google Sheets

Google Sheets provide functions to import data of different formats including HTML, XML, etc.; but there is no dedicated function for Excel files (XLS or XLSX format). So, if you have an Excel file and want to import data from it, there are two ways to do this:

Manual import 

Go to the File menu of your spreadsheet and select Import. If your Excel file is stored on your Google Drive, use the My Drive tab. 

Otherwise, you’ll need to upload the file from your device. In the case of storing your file on OneDrive or Dropbox, you’ll have first to download it to your device and then upload to Google Sheets. 

Automatic import

The already mentioned CSV importer supports Excel files as well. Just copy the URL of your XLSX file and paste it in the respective field of the tool. For more on this, refer to the blog post Import Excel to Google Sheets

Note: Do not confuse importing and converting Excel files to Google Sheets. When you import, the Google Sheets retrieves the values, not the formulas. So, if you want to keep your Excel formulas, you’ll need to do the conversion. 

Wrapping Up

One of the best things about Google Sheets is that the app provides multiple solutions to automate your workflow. For example, with IMPORTRANGE, you don’t need to manually copy and paste data from other spreadsheets. The IMPORTDATA function, which we reviewed in this blog post, allows you to automate your workflow with CSV data. And this is only the tip of the iceberg.

You can nest IMPORTDATA with QUERY (just like we did with IMPORTRANGE in Import Data in Google Sheets #2 Import Data from other Spreadsheets) and benefit from custom queries for the imported dataset. If you want to learn more about this, leave your reply and we’ll get on it.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.