How to Import and Open CSV File in Google Sheets?

Comma-separated value, or CSV, is a non-standardized plain text data file format used to store and exchange tabular or spreadsheet information.

While CSV files omit any sort of formatting information, their plain text nature makes them very easy for any program to interpret.

What makes CSVs so useful is they provide a practical way to exchange information between databases, spreadsheet applications, and other programs.

You can open a CSV file in Google Sheets regardless of what program created the file.

This tutorial covers the two ways to import and open a CSV file in Google Sheets.

Importing and Opening CSV file in Google Sheets

At any time, you can upload and open a CSV file stored on your device in the Google Sheets application.

You can’t directly import a CSV file from a URL, you’ll need to download it to your device and upload it for the process to work.

The following steps show how to import a CSV file in Google Sheets:

  1. Open any existing Google Sheets file or create a new spreadsheet.
  2. Select “File” from the header menu and choose the “Open” option.Click on File and Open
  3. From the newly launched “Open a file” menu, choose the “Upload” tab and click “Select a file from your device.” Note that you can also drag the CSV file into the window area and skip the next step on Windows/Mac devices.Select a CSV file to upload
  4. Locate and select the CSV file using your device’s file browser interface. The example shows opening the “myCSV.csv” file from the “Downloads folder” on a Windows 10 device.Locate the file on your system
  5. Google Sheets will create and launch a new workbook named after the CSV file.Google Sheets opens the CSV file

Now you can work with the CSV file as if it were a native Google Sheets workbook. Since CSV doesn’t include any formatting information, you may need to spend a little time cleaning up the spreadsheet.

The following example shows the same data with resized columns, unneeded information removed, and a formatted header row.

CSV Data formatted in Google Sheets

Adding a CSV to Google Drive and Opening it in Google Sheets

You can also upload CSV files to your Google Drive and open them in Google Sheets. The following steps show how to upload a CSV file to your Google Drive and open it in Sheets.

  1. Launch your Google Drive.
  2. Click the “New” button located near the top left corner of the Google Drive window.Click on New option in the drive
  3. Choose “File upload” from the “New” menu.Click on File Upload
  4. Locate the CSV file on your device, using its file browser interface. The example shows opening the “myCSV.csv” file from the “Downloads folder” on a Windows 10 device.Locate the file on your system
  5. Google Drive will create a new Google Sheets workbook named after the CSV file. Click on the file in the upload footer pop-up to launch it in preview mode.CSV file Upload complete
  6. Click “Open with Google Sheets” to open the file in Google Sheets.Open with Google Sheets
  7. Google Sheets will show the imported CSV file as a workbook.CSV Data formatted in Google Sheets

Tips for Importing an Incorrectly Formatted or Imported CSV

Since CSV is a non-standardized format, you may run into issues with incorrectly imported data.

The term “character-separated values” is often used with CSV as well because some CSVs use something other than a comma to designated cell division.

Other times you may not be able to import a CSV file and your only option is to copy/paste the information from another source.

Google Sheets has a built-in data separation tool you can use to fix issues with CSVs.

Note that opening the CSV file in a text editor program and trying to copy/paste it into Google Sheets will format all in a single column.

This tool works the same way with any separator character including a comma.

This example uses the same CSV information as the previous examples, but the commas are replaced with semicolons.

The following steps demonstrate how to split an incorrectly formatted or imported CSV:

  1. Highlight and copy (Ctrl+C for PC, CMD+C for Mac) the CSV information from a text-editor, web page, or another data source. The example shows a CSV file opened in Google Chrome.CSV file in Text Editor
  2. Open your Google Sheets file and paste (Ctrl+V for PC, CMD+V for Mac) the data in the first open cell. The Data will come through respecting line breaks for rows, but all the information will be in a single column.Copy paste data from text to google sheets
  3. Highlight the imported CSV data in the spreadsheet.Highlight the data
  4. Open the “Data” drop-menu from the header and select “Split text to columns.”Click on Split Text to Columns
  5. Click on the “Separator:” pop-up.Detect automatically
  6. For the sake of this tutorial, choose the “Custom” option from the data (you could select “Semicolon” or “Detect Automatically” for this specific use-case and it would work).Select custom as the delimiter
  7. Enter the custom separator value in the “Custom” field to split the cells. In the example, we’re using a semicolon, but you could enter a comma or any other value to adapt to the CSV data formatting.Enter semicolon as the delimiter
  8. The spreadsheet will now display the CSV data with properly split cells.Data from CSV in Google Sheets

This tutorial covered the two main ways to import a CSV file into Google Sheets and how to work around importing errors and limitations.

I hope you found this tutorial useful!

Other Google Sheets tutorials you may like:

Leave a Comment