How to Open CSV File in Google Sheets

It happens a lot of times when you download data from some application or from a web portal and that data is in the CVS file format. If you are a Google Sheets user, the good news is Google Sheets supports CSV files (Just Like Excel), meaning you can open CSV files in it.

This is highly important for the people who get data from various source and all those data file are CSV file formatted. Now in this tutorial, we will explore a list of methods in detail on how you can import data from a CVS file into a Google Sheets workbook.

All these methods I have test and used myself and let me tell you all these methods are perfect for a specific kind of situation. Now you need to check which one works perfect for you.

And make sure share with your comments in the comment section. So without any further delay let’s get started…

What is a CSV File Format?

CSV, a Comma-Separated Values file, is a plain text file that arranges tabular data using specific structuring. Because it’s a text-only format, CSV files are compatible with numerous editing and scripting tools. CSV files are a popular choice for data transfer and storage in contexts where a simple, lightweight, and easy-to-edit format is required.

Steps to Open or Import a CSV File in Google Sheets

Importing a CSV file means getting the data from the CSV file into an already existing Google Sheets workbook or create a new workbook when you import the data. Here are the steps to do this:

• Click on File in the top menu bar of the Google Sheets, and then click on Import to open the import menu.

• Now, you can select a CSV file from your device to upload. It can be any CSV file stored on your device. After you’ve found the File, double-click it to upload it, click once to select it, and then click ‘Open.’

• Once your File is uploaded, you’ll need to decide how to insert your data:
  • Create new spreadsheet: Imports the CSV data into a new sheet (creates a new workbook).
  • Insert new sheets: Add a new tab to your current spreadsheet and place the data there.
  • Replace Spreadsheet: Replace the data of the current sheet with the data from the CSV file.
  • Replace current sheet: Overwrites the data in your active sheet with the data from the CSV.
  • Append to current sheet: Adds the CSV data to the bottom of the active sheet.
  • Replace Data at selected cell: Adds data to the current sheet stating from the selected cell.

• Next, you can also choose the type of the separator (Auto Detected, Comma (,), Semicolon (;), Tab, or Space) you want to separate the data from the CSV format in the normal Google Sheets workbook. This feature is handy if your CSV file uses a delimiter other than a comma, such as semicolons, commonly used in Europe.

• In the end, click on the “Import Data” button to import the file in the Google Sheets. The data from the CSV file will appear according to the option you selected.

When you choose to create a new workbook to import the data it won’t open it right away but give you a link to open it after importing the data.

You just need to click on it to open it.

Use Open Option Instead of the Importing CSV File

Opening a CSV file in Google Sheets launches a new Google Sheets workbook. This method creates a new spreadsheet in your Google Drive where the CSV file data is displayed in the workbook.

• First you need to go to the File tab and then click on the Open options to open the dialog box which allows you to

• Then click on the “Upload” and then click on the “Browse” to upload the CSV file.

• Now, you can select a CSV file from your device to upload. It can be any CSV file stored on your device. After you’ve found the file, double-click it to upload it, click once to select it, and then click “Open”.

Importing a CSV Vs. Opening a CSV

Whether you open or import a CSV file into Google Sheets depends on how you intend to use the data: either as a standalone data or integrated with other data.

Importing Multiple CSV Files into the Google Sheets

Importing multiple CSV files into Google Sheets can be handled efficiently using Google Apps Script, which allows you to automate the process, which is especially useful when dealing with multiple files.

BUT FOR THIS, YOU NEED TO HAVE YOUR FILES INTO GOOGLE DRIVE. You need to create folder in the google Drive and then add the folder ID to the Google App Script shared below:

You can find this folder ID in the URL of the folder in the Google Sheets.

function importMultipleCSVs() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var folderId = "CHANGE-WITH-YOUR-FOLDER-ID"; // Replace with your folder ID where CSVs are stored
  var folder = DriveApp.getFolderById(folderId);
  var files = folder.getFilesByType(MimeType.CSV);

  while (files.hasNext()) {
    var file = files.next();
    var fileName = file.getName();
    var csvData = Utilities.parseCsv(file.getBlob().getDataAsString());
    var sheet = ss.getSheetByName(fileName) || ss.insertSheet(fileName); // Creates a new sheet with the name of the file or uses the existing one
    sheet.clear(); // Clears the existing data
    sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);
  }
}

After that, click the floppy disk icon to save the script. And then, click the play icon (►) to run the script. You might need to authorize the script by following the on-screen prompts and giving permissions.

Once you run the script, each CSV file will be imported into its own sheet within the Google Sheets document. The name of each sheet will correspond to the name of the CSV file.

Use IMPORTDATA function to Import a CSV File in Google Sheets

This method is useful for automatically updating data in your sheet without needing to manually download and upload CSV files each time the source data changes.

Select the cell where you want to import the data (Cell A1). Type =IMPORTDATA(“URL”) into the cell, replacing “URL” with the actual URL of your CSV file. For example, if your CSV file is at https://excelchamps.com/wp-content/uploads/myfile.csv, you would enter:

=IMPORTDATA("https://excelchamps.com/wp-content/uploads/myfile.csv")

After typing the function with your URL, hit Enter. Google Sheets will fetch the data from the CSV file from the specified URL and display its contents starting from cell A1.

Notes on Using IMPORTDATA

Make sure to note down this point when you are using the IMPORTDATA function to get data from the CSV file;

• Update Frequency: Google Sheets updates the imported data every hour if the URL points to data that changes over time. IMPORTDATA is an excellent tool for working with live or frequently updated data.
• #REF!: Occurs if the function tries to import more data than is available in the sheet’s remaining cells.
• Could not fetch URL: This indicates that the URL is incorrect, the server was unreachable, or the server denied access due to the CORS policy.
• Could not parse the data: Means that the file at the URL was not in a proper CSV or TSV (tab-separated values) format.
• Limits: You can use up to 50 IMPORTDATA calls in a single spreadsheet.

Important Points to Take Care

When importing a CSV file into Google Sheets, it’s important to take care some of the points:

• If your CSV file includes dates or numbers, be aware that Google Sheets may automatically format these fields. You may need to adjust this formatting to match your needs manually.
• Large CSV files may take longer to import and time out. If you have a huge CSV file, consider breaking it up into smaller files before importing.
• Ensure that the CSV file is formatted correctly. Each record should be on a new line, and a comma should separate each field within the record.
• Make sure there are no special characters within the file that could break the import process.

Wrap Up

If you ask me which method I liked the most, well, I’d say the IMPORTDATA is my favourite one which allows you to get a data from CSV file without opening it or downloading it. Do let me know in the comment section that which one is your favourite and works great for you.