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:
- Open any existing Google Sheets file or create a new spreadsheet.
- Select “File” from the header menu and choose the “Open” option.
- 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.
- 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.
- Google Sheets will create and launch a new workbook named after 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.
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.
- Launch your Google Drive.
- Click the “New” button located near the top left corner of the Google Drive window.
- Choose “File upload” from the “New” menu.
- 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.
- 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.
- Click “Open with Google Sheets” to open the file in Google Sheets.
- Google Sheets will show the imported CSV file as a workbook.
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:
- 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.
- 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.
- Highlight the imported CSV data in the spreadsheet.
- Open the “Data” drop-menu from the header and select “Split text to columns.”
- Click on the “Separator:” pop-up.
- 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).
- 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.
- The spreadsheet will now display the CSV data with properly split cells.
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: