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.
Each record of data in a CSV is represented on a separate line. Most times, the first line in a CSV is the headings for each of the columns. Because of 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 very common misconception is that CSV files are Microsoft Excel files. This is inherently wrong. It's quite likely that on your computer the ".csv" file extension is automatically associated with Excel, hence why the files open with it.
However, many times the default guesswork that Excel does in order to parse the data and display it in the spreadsheet ends up being incorrectly formatted.
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.
Don't worry, because you're in the right place!
This support document will help you understand CSV files better and show you the best way to work with CSV files so that you never have to deal with malformed data or unexpected behaviour when using the files in another system.
Importing CSVs using Google Sheets
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 we have the spreadsheet created, we're ready to import the CSV data.
Click on File > Import
Navigate to the "Upload" tab
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 on this "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 option. If you're importing the CSV into an already existing spreadsheet, then the "Insert new sheet(s)" may be a better option.
- Separator Type - It is 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 select it yourself.
When dealing with Tab-separated value (TSV) files the "Tab" option should be used.
- 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 automatically adjust your data, 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 proceed to filter, format, and edit the CSV data as you wish, without worrying about it being formatted or mangled due to improper handling.
Importing CSVs using Microsoft Excel
Open Excel and create a new workbook - don't choose the "open with Excel" pop-up that you might automatically get.
Navigate to the "Data" tab on the top of the Excel sheet
In the "Get external Data" dropdown, select the "From Text" option. (Since CSVs are simple text files this is the best way to get them into Excel).
This will give you a prompt to select the file you wish 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 click "Comma" and unselect the default "Tab" delimiter. Once you do this, you will see in the preview box that the data is being parsed.
Click "Next >" to proceed.
On this page, we'll tell excel how to process the values in each column.
You're able to select the boxes in the preview at the bottom of the dialogue. We need to select all columns in the preview box. To do this, click the first column, then hold shift and select the last column.
In "Column. data format" choose "Text." This will apply to all selected columns.
7. Click "Finish."
You'll then be prompted about where to put the data. Since we've already created a brand new sheet we can put the data in "Sheet1" starting at cell "A1."
If you wanted to import the data into an existing spreadsheet, you can specify the exact cell or new sheet into which to put it.
Once you are happy with your selection click "Ok."
You're done! The data will import and you'll see your CSV data now showing on the spreadsheet.
In this section, you we'll walk you through how to export CSV files from Google Sheets and Microsoft Excel.
This process is much easier than importing, 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.
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.
In order to export your Excel data all you have to do is go to File > Save As... and from the dialogue select the appropriate File Format.
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 with French or other non-English languages) to a UTF-8 encoded value. Depending on the system that 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 being put out in a CSV structure. Therefore this is the recommended option to use.