Upload Zip Linked Csv File to Google Sheets

In the previous tutorials, we talked virtually importing information from another sail in the same spreadsheet and from other spreadsheets to Google Sheets. In this tutorial, allow'due south focus on importing CSV into Google Sheets. Y'all can import TSV files too using the methods described below. In improver to that, I will prove 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 comprise data separated with tabs in plain text format
• XLSX – the format of Microsoft Excel Workbooks

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

There is a dedicated function chosen IMPORTDATA to import CSV and TSV files from a given URL in Google Sheets. Here is the IMPORTDATA syntax:

          =IMPORTDATA("URL")        

The first and but parameter of the office, URL, is the URL of a CSV or TSV file. Run across the post-obit case on importing CSV into Google Sheets using this office.

How to use the IMPORTDATA function

Permit'southward 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 piece of work:

          =IMPORTDATA("http://samples.openweathermap.org/storage/history_bulk.csv?appid=b1b15e88fa797225412429c1c50c122a1")        

Annotation: Beware of importing zipped CSV files using IMPORTDATA. For instance, 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 become:

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

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

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

Importing CSV into Google Sheets from Google Drive

Here are the steps you should have:

• Share the Google Bulldoze binder containing the file. Choose "Anyone with the link" and set the user office 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 suspend information technology into the following URL sample:

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

You should get the following:

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

Now you can employ this URL for your IMPORTDATA formula:

          =IMPORTDATA("https://bulldoze.google.com/uc?consign=download&id=1QNFRHpe0Y7eh5dgTkPzNT-MilxPfhgU4")        

Import CSV files from OneDrive

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

          =ARRAYFORMULA(IFERROR(Dissever(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.alive.com/embed?cid=EC25D9990D879C55&resid=EC25D9990D879C55%211498&authkey=AE20BwwO1PPaKdg" width="98″ top="120″ frameborder="0″ scrolling="no"></iframe>

2. Get the URL from the embedding code.

Instance:

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

3. In the URL, replace "embed" with "download".

Instance:

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

Utilise 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 files from Dropbox

To import CSV files stored on Dropbox, you'll need the aforementioned IMPORTDATA formula syntax equally for OneDrive:

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

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

• Become 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".

Instance 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

Apply this modified URL in the IMPORTDATA formula:

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

How to import CSV information into Google Sheets without formulas

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

To import data using the CSV importer, yous 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 unlike formats including HTML, XML, etc.; but in that location is no defended function for Excel files (XLS or XLSX format). And so, if you have an Excel file and want to import information from it, there are two means to do this:

Manual import

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

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

Automatic import

The already mentioned CSV importer supports Excel files equally 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 misfile importing and converting Excel files to Google Sheets. When you import, the Google Sheets retrieves the values, not the formulas. Then, if you lot desire to keep your Excel formulas, y'all'll demand 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, yous don't demand to re-create and paste data from other spreadsheets manually. The IMPORTDATA part, which nosotros 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 Canvass to some other) and benefit from custom queries for the imported dataset. If you lot desire to learn more about this, go out your respond, and we'll go on it.

millerwilapplen.blogspot.com

Source: https://www.bpwebs.com/importing-csv-into-google-sheets/

Share this post