Importing CSV Into Google Sheets (import CSV, TSV, and XLSX)

In the previous tutorials, we talked about importing data from another sheet in the same spreadsheet and from other spreadsheets to Google Sheets. In this tutorial, let’s focus on importing CSV into Google Sheets. You can import TSV files also using the methods described below. In addition to that, I will show you how to import XLSX files also to Google Sheets. The CSV, TSV, and XLSX stands for;

  • 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

Different methods of Importing CSV into Google Sheets (applicable for both CSV and TSV)

There is a dedicated function called IMPORTDATA to import CSV and TSV files from a given URL in Google Sheets. 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. See the following example on importing CSV into Google Sheets using this function.

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")
Importing CSV into 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")
2 importdata zipped csv Google Sheets

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.

Importing CSV into Google Sheets 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.
4 onedrive embed code Google Sheets Import Data

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”.
6 shared link dropbox Import CSV to Google Sheets

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"),";")))
7 importdata dropbox to Google Sheets

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.

8 csv importer for Google Sheets

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. 

9 import excel file to Google Sheets

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 copy and paste data from other spreadsheets manually. 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 How to import data from one Google Sheet to another) 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.

3 thoughts on “Importing CSV Into Google Sheets (import CSV, TSV, and XLSX)”

  1. Hi, thanks for the details you have provided, one thing I have not been able to find anywhere is if I can use one of the IMPORT formula in google sheets to pull in SmartSheet data. Do you know if this is possible? thanks, KC

    Reply
  2. Thanks a lot for all this information, helped me a lot working with csv files, and saving time. I have an issue with one csv file wich has “;” delimiter and in the same time in some records “;” within a text string, so the split function didn´t work very well. Any suggestions?

    Reply
    • If you are familiar with writing functions/Coding better try out with Appscripts. Its easier to import csv file much faster.
      for delimiter use parseCSV functions
      function CSV_To_Array( strData, strDelimiter ) {
      strDelimiter = (strDelimiter || “,”);
      var objPattern = new RegExp(
      (
      “(\\” + strDelimiter + “|\\r?\\n|\\r|^)” +
      “(?:\”([^\”]*(?:\”\”[^\”]*)*)\”|” +
      “([^\”\\” + strDelimiter + “\\r\\n]*))”
      ),
      “gi”
      );
      var arrData = [[]];
      var arrMatches = null;
      while (arrMatches = objPattern.exec( strData )){
      var strMatchedDelimiter = arrMatches[ 1 ];
      if (
      strMatchedDelimiter.length &&
      (strMatchedDelimiter != strDelimiter)
      ){
      arrData.push( [] );
      }
      if (arrMatches[ 2 ]){
      var strMatchedValue = arrMatches[ 2 ].replace(
      new RegExp( “\”\””, “g” ),
      “\””
      );
      } else {
      var strMatchedValue = arrMatches[ 3 ];
      }
      arrData[ arrData.length – 1 ].push( strMatchedValue );
      }
      return( arrData );
      };

      Reply

Leave a Comment

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

Share via
Copy link