Google Sheets is a terrific spreadsheet application with a remarkable collection of tools and sharing features that make collaborative work a lot easier. One of these excellent features is the ability to import CSV into Google Sheets from other apps.
Importing data from other applications can be cumbersome for the most part. However, CSV files make it easier to pack the data into an accessible format that you can use over virtually any spreadsheet software.
This article will discuss what CSV files are, how to paste CSV into Google Sheets, and the way to correctly format one so it can work properly in Google Sheets. Read on to find out more.
Table of Contents
Quick Answer: Convert CSV to Google Sheets
- In Google Sheets, navigate to File > Import > Upload > Select a file from your computer
- Choose the CSV file and click Import data in the pop-up window
What Is a CSV File and Why Import CSV to Google Spreadsheet?
A Comma Separated Values file or a CSV file is a simple text file that generally contains a list of data. Users can utilize a CSV file is to exchange data between applications. CSV files work perfectly in contact managers and databases.
As the name suggests, a comma separates the values in CSV files. However, sometimes other characters are also used like semicolons. Due to this, you may often see variations in the file’s name as they are sometimes called Character Separated Values or Comma Delimited files.
For example, the structure of a contact list CSV file could look something like this:
Name, Gender, Address, Number, Email
As CSV files take up hardly any space, they are often used as downloads from websites. But, to work with the data easily, you’ll need it put into a spreadsheet program. That’s why you’ll need change the CSV file to be Google Sheets friendly.
How to Import CSV into Google Sheets
There are essentially two ways to import CSV to Google Sheets. The first way is to import a CSV file locally stored on your computer. The second involves importing a CSV file onto Google Sheets using Google Drive.
Locally Stored CSV Files
To import a locally stored CSV file onto Google Sheets, follow these steps:
Step 1: Go to Google Sheets and click on the file icon to open the file picker.
Step 2: A smaller window shows up in the middle of the screen. Click on Upload in that screen to choose the local CSV file.
Step 3: In the window, drag and drop the file or click on the Select a file from your device to choose your file from the file manager in your operating system.
Step 4: In your file manager, simply go to the CSV file you wish to import into Google Sheets and simply double-click on it. The file should load in after a few seconds depending on the size of the file.
You can follow a similar process to convert Excel to Sheets files too.
Import CSV to Google Sheets Using Google Drive
If you have a file stored in Google Drive, the steps to import it into Google Sheets are almost the same as importing a local CSV file. Note that before you can use a CSV file in Google Sheets, you first need to upload the file in Google Drive. Then you can upload the CSV to Google Sheets.
The only change is to look for the file in the My Drive section. Simply click on your CSV file and then click on Open.
Related Reading: Using the Google Sheets IMPORTXML Function
How to Convert CSV to Google Sheets
Often, the CSV files are not formatted correctly to work with Google Sheets. There are some things we can do to make sure they work perfectly. In our case, the file used Semicolons (;) instead of a Comma (,) which caused trouble in loading the files correctly. Fixing this is a simple process. To do this, follow these steps:
Step 1: Right-click on the file and click on Open with. There, select any text editor software. We use Notepad here as it comes built-in with Windows.
Step 2: Click on Edit and then Replace or use the Ctrl+ H shortcut. A small window should open to find and replace the text.
Step 3: Input the delimiter used in the file in the first textbox. In this case, it’s a semicolon, so we wrote (;) in the textbox. Input a comma (,)Â in the second box, which is the delimiter used in Google Sheets. Once you finish, click on Replace All, and your text should be formatted appropriately.
Step 4: To save your changes, click on File and save as, or use the Ctrl+ Shift+ S shortcut.
Step 5:Â To ensure that the file is saved in the proper format, ensure the File name contains a .csv at the end and the Save as type is chosen as All files.
Google Sheets Paste CSV Guide
Sometimes you’ll run into errors when importing CSV into Google Sheets and you will have to copy-paste the CSV values into Sheets from a word processor. Here’s how:
- Open the CSV file in a word processor
- Highlight all the text inside the file
- Press Ctrl + C (Cmd + C on macOS)
- In a blank spreadsheet, paste the values with Ctrl + V or Cmd + V
- As it will all be in one column, highlight that column and navigate to Data > Split text to columns
- Select Detect automatically in the Separator pop-up box
- Press Enter
Note: if the values don’t separate properly, you may need to specify the delimiter used in the CSV data instead of the Detect automatically option.
Related Reading: Import JSON files to Google Sheets
How to Clean up a Spreadsheet CSV File
After you export CSV to Google Sheets, the first thing you should do is delete unwanted columns of data. To do this, simply right-click the column header and select Delete column.
After that, you may want to concatenate (combine) data from two columns into one. To do this:
- In a blank column, enter =CONCATENATE(
- Click on the first cell reference you wish to use. In our example, it’s B1.
- Enter a comma, then a delimiter inside quotes followed by another comma. In the example above we want a space so we used ,” “,
- Click the other cell reference you wish to use, C1 in our example
- Press Enter to execute the function
- Click and drag the fill handle down the column to apply the formula to all the appropriate cells.
How to Create a CSV File
- Open the spreadsheet you wish to convert to CSV
- Navigate to File > Download > Comma Separated Values
You can follow a similar process to export Sheets files as Excel files too.
Frequently Asked Questions
Can I Edit CSV in Google Sheets?
Yes, but you’ll need to import it instead of clicking to open it. Google Sheets can open CSV files by importing them through File > Import > Upload > Select a file from your computer and select the CSV file. To download it as a CSV file again, head to File > Download > Comma Separated Values.
How Do I Automatically Import a CSV File Into Google Sheets?
There is no feature to import CSV to Google Sheets automatically. However, Google does have Apps Script that allows you to code in functionality to automatically import CSV files in Google Sheets.
Can You Open a CSV File in Google Sheets?
You can open Comma Separated Value files in Google Sheets. However, CSV files can not be imported into Google Sheets using a URL, which means you should use Google Drive to import and open CSV in Google Sheets or import a locally stored CSV to Google Sheets.
How Do I Import Multiple CSV Files Into Google Sheets?
There are essentially two ways To import multiple files into Google Sheets. The first one is easier but requires you to use paid add-ons. The second method is free but does require a bit of coding knowledge. You can do this by using the Apps Script feature in Google Sheets.
How Do I Format a CSV File in Google Sheets?
The best way to format after you upload CSV to Google Sheets is to write it in the following format:
Value1, Value2, Value3, Value 4
This means that your data will divide using commas, where commas separate columns while individual lines represent rows.
How Do I Import a CSV File Into Google Spreadsheets Using Google Apps?
Google Drive is the best way to seamlessly import a CSV file into Google Sheets as it saves your changes to the Drive with different versions. This allows you to roll back to a previous version easily if needed.
Wrapping Up the Google Sheet Import CSV Guide
Google Sheets CSV import allows you to use CSV files imported from other databases or software into Google Sheets. This can be crucial if you’re someone who works with several databases or other forms of software, as CSV files can eliminate a lot of the hassle usually associated with transferring data from one software to another.
Now that you know how to import CSV into Google Sheets, you may find the following related tutorials helpful:
