When working with data, it's common to come across requests for the .csv file type. For many, working with CSVs can feel a little daunting. This article will help you to understand CSV files and show you the best ways to work with them, so you can feel confident importing and exporting data like a pro.
What is a CSV file?
Comma-separated values, or CSV's as they're more commonly known, are simple text files that, as the name suggests, delimit data with commas. Thanks to their simplicity, CSV files are commonly used to import/export data from different systems.
Are CSV files the same as Excel files?
In short, no. It's a common misconception that CSV files are Microsoft Excel files as many computers automatically associate the ".csv" file extension with Excel, hence why the files open with it.
However, the default guesswork that Excel does to parse the data and display it in the spreadsheet often needs to be correctly formatted after the fact.
It's common to see dates being converted to Excel date objects or large integer numbers like credit card numbers, SINs, or phone numbers being converted to scientific notation. This usually causes a lot of confusion and makes people weary of CSV files.
If you're feeling unsure about CSVs, don't worry–you're in the right place!
Importing CSVs
In this section, we'll walk you through how to import CSV files to Google Sheets and Microsoft Excel. Use the drop-downs below to learn more:
-
Let's begin by creating a blank spreadsheet. Instead of using the "Open with Google Sheets" option that may pop up, it's better to start with a blank Google sheet.
Once the spreadsheet is created, we're ready to import the CSV data.
- Click on File and select Import
- In the pop-up, click on the Upload tab on the far right
- Select the CSV file you want to import
- After you've selected your CSV and uploaded it, you'll be presented with the import file dialogue
-
This is the most important step. The settings you select in the import file pop-up will determine how easy or hard it will be to work with the data
To help, this is what we suggest:
- Import Location
- Since we created a brand new spreadsheet, the Replace spreadsheet option would be the best. However, if you're importing the CSV into an existing spreadsheet, then the Insert new sheet(s) may be a better option.
- Separator Type
- It's important that you select Comma here. Google is pretty good at detecting the comma automatically; however, if you want to be sure, it's best to set it yourself.
-
Note
The Tab option should be used when dealing with Tab-separated value (TSV) files.
- Convert text to numbers, data, and formulas
- This is where you'll choose what to do with text, numbers, dates, and formulas. If you don't want Google to adjust your data automatically, it's best to select No on this option. This will allow the values to be imported as simple text and not be formatted in any way.
- Once you hit the Import data button, the page will refresh, and your data will show up in all its glory in the google spreadsheet
From this point on, you can filter, format, and edit the CSV data as you wish without worrying about it being formatted or mangled due to improper handling.
-
Open Excel and create a new workbook - we recommend ignoring the "open with Excel" pop-up you might get automatically, as it doesn't work as well as creating a new workbook from the start.
-
Navigate to the Data tab at the top of the Excel sheet
-
In the Get External Data drop-down, select the From Text option. (Since CSVs are simple text files, this is the best way to get them into Excel)
-
This will prompt you to select the file you'd like to import.
-
Select the file and click Get Data
-
The Text Import Wizard window will pop up. Choose Delimited and then click Next >
-
On the next page, under Delimiter, select Comma and unselect the default Tab option. Once you do this, you will see in the preview box that the data is being parsed
-
Click Next > to proceed
-
In this step, we'll tell excel how to process the values in each column by selecting the boxes in the preview at the bottom of the dialogue
-
First, we need to select all columns in the preview box. To do this, click the first column, hold shift, and select the last column
-
In Column data format, choose Text. This will apply to all selected columns
- Click Finish
- You'll then be prompted about where to put the data. Since we've already created a new sheet, we can put the data in Sheet1 starting at cell A1
Note
If you want to import the data into an existing spreadsheet, you can specify the exact cell or new sheet to put it into.
-
Once you are happy with your selection, click Ok
- You're done! The data will be imported, and your CSV data will show on the spreadsheet
Exporting CSVs
In this section, we'll walk you through how to export CSV files from Google Sheets and Microsoft Excel. Use the drop-downs below to learn more:
This process is much easier than importing, and it can be done in 3 clicks - literally.
Click File > Download as > Comma-separated values (.csv, current sheet)
The file will be generated and downloaded instantly.
Note
It's recommended to delete any unused columns or rows in the spreadsheet before exporting to reduce the size and complexity of the file. Less useless data equals fewer headaches.
To export your Excel data, all you have to do is go to File > Save As... and select the appropriate File Format from the dialogue box.
There are two viable options you can select:
CSV UTF-8 (Comma delimited) (.csv) - This file format will automatically convert any accented characters (commonly seen in French or other non-English languages) to a UTF-8 encoded value. Depending on the system you will be using this file into, this may not be the best option because it could result in incorrectly encoded data.
Comma Separated Values (.csv) - This file format is the pure unedited values denoted in a CSV structure. We recommend you choose this option.