What are CSVs?
Comma-separated values or CSV as they are more commonly known, are simple text files that as the name suggest delimit data with commas. Each record in a CSV is represented on a separate line. It is very common to see the first line in a CSV be the headings for each of the columns in the file. Because of their simplicity CSV files are commonly used to import/export data from different system.
A very common misconception is that CSV files are Microsoft Excel files. This is inherently wrong. It is quite likely that on your computer the .csv file extension is automatically associated with Excel, hence why the files open with it. However as convenient as that may be a lot of the times the default guess work that Excel does in order to parse the data and display it in the spreadsheet, ends up incorrectly formatting or mangling it. It is common to see dates being converted to Excel date objects, or large integer numbers like credit card numbers, SINs or phone numbers be converted to scientific notation.
This document will show you the proper 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.
In this section you will find guides to importing CSV files into commonly used software to work with delimited data in a spreadsheet format.
Let's begin by creating a blank spreadsheet. You want a fresh start instead of using the open with Google Sheets option that some browsers may give you.
Once We have the spreadsheet created we are ready to import the CSV data.
Click on File > Import
Go over to the Upload tab
Select the CSV file you wish to import
After you have selected your CSV and it uploaded you will be presented with the import file dialog. This is the most important step. What you select on that pop up will determine how much trouble you will have working with the data. Here is the appropriate selections:
- Choose the Import Location. Since we created a brand new spreadsheet the Replace spreadsheet option should be the best option. If you are importing the CSV into an already existing spreadsheet perhaps Insert new sheet(s) is a better option.
- Choose the Separator Type. It is important that you select Comma here. Google is generally speaking pretty good at detecting the comma automatically, however if you want to be bullet proof its best to select it yourself. When dealing with Tab-separated value (TSV) files the Tab option should be used.
- Choose what to do with text, numbers, dates, and formulas. If you don't wish google to automagically do things toyour data it is 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 Import data 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.
Let's begin by creating a blank spreadsheet. You want a fresh start instead simply double clicking the file to open with Microsoft Excel.
In order to import the CSV we will take the following steps:
First navigate to the Data tab in the Excel window
Then we will select the From Text from the leftmost panel. Since CSVs are simple text files this is the right 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 should have shown up. On the first step what we want to do is indicate that the file is Delimited. Then we click ***Next >***.
On Step 2 we will indicate that the Delimiter used is a Comma, and unselect the default Tab delimiter. Once that selection is made you can already see the data being parsed in the Preview on the bottom. Click Next > to proceed.
On Step 3 (the last step) we will tell excel how to process the values in each column. This step works slightly differently from the previous 2. The preview at the bottom of the dialog is selectable. What you want to do is convert the formatting on each column from the default General to the plain Text format. There are two ways to achieve this. The first is by clicking in each of the columns, one at a time and changing the Column data format at the top. You will see the change being reflected in the first row in the preview. See example below where I changed the Email column to be formatted as text:
The second way to do this is by selecting the first column then scrolling to the last column. Then while holding down SHIFT clicking the last column. If done correctly this will select all the columns at once. Then when you select the Text format it will apply to all of them.
Once you have set the formatter to Text on all columns you are ready to click Finish and import your data. This will give you a prompt asking you for where to put the data. Since we 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.
This will finish the import and you will see your CSV data now present in the spreadsheet.
In this section you will find guides to exporting CSV files from commonly used software in order to use them in other systems.
Fortunately enough, unlike importing, getting a CSV from an already created Google Sheet is single step process. Simply click:
File > Download as > Comma-separated values (.csv, current sheet)
The file will be generated and download instantly.
One thing to note is any unused columns or rows in the spreadsheet. It is always a good idea to delete those before exporting to reduce the size and complexity of the file. Less useless data equals less headaches.
In order to export your Excel data all you have to do is go to File > Save As... and from the dialog 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 thismay not be the best option. 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.