Understanding CSV (Comma Separated Value) Files
SCRIBE, among many other applications, uses Comma Separated Value (CSV) files for importing and exporting data. However, many users simply see a confusing text file or try to open up the file in Microsoft Excel just to see their data appearing strangely.
It is true that if you open a CSV file by double-clicking or by opening it through Excel’s menu, you will see some skewed values. For example, a school with the identification code of “001” will have its leading 0’s removed so that it is now simply ‘1’.
The worst part about opening a file in this way, is that some users will save the file when closing it. Doing so will cause those leading 0’s and any other changes made by Excel to be saved as well, possibly rendering the file useless for their needs.
In this week’s post, I’d like to take a few minutes to walk through the process of opening a CSV file so that it may be used and edited in Excel so that we can all make better use of the information contained in these files as well as having some better understanding of the formatting of the file.
We will also take a look at saving an Excel worksheet as a CSV file, opening a CSV file as a text document, and useful tips for formatting a CSV file.
Click an item to jump to that section
Importing a CSV file into Microsoft Excel
This guide will use Microsoft Excel 2007, but the steps are identical in Excel 2010.
CSV01 – Importing the CSV file into Microsoft Excel.
I will exclude the most obvious step of opening Excel and get right into the import process. If you are familiar with Microsoft Office 2007/2010, you will probably be familiar with the Ribbon which replaced the toolbars in previous versions. Clicking on any of the menu items at the top of the window will change the contents of the Ribbon below.
Clicking on Data will bring up the Ribbon displayed above. To import the CSV file into excel, click on the From Text button.
CSV02 – Select the CSV file to imported.
Through the dialog box that opens, navigate to the location of the CSV file that you wish to import. (Note that a CSV file will usually end with the “.csv” file extension; however, it can also end with “.txt” or another file extension depending on how it was saved.)
Select the file and click the Import button in the lower right hand corner of the dialog box.
CSV03 – First screen of the Import Wizard dialog, selecting the file format.
The first screen of the Import Wizard will show you a preview of the file and allow you to choose how your file’s values are separated. The two choices are “Delimited” and “Fixed Width”. Delimited refers to a character (a comma in our case, but some other examples are the tab character, semicolons, and spaces); and Fixed Width means that each value takes up a specified number of characters/spaces.
Our example uses a standard CSV file, so we will choose “Delimited” and click the Next button.
CSV04 – Second screen of the Import Wizard dialog, selecting the delimiter.
The next screen of the Import Wizard allows us to select the deliminator (or the character that separates the values). As mentioned above, there are several options, however, we will select “Comma”. When “Comma” is selected, the preview window will change to reflect the individual values that are separated by the commas. If the formatting appears correctly with the column headers at the top and the appropriate values in the columns below, click the Next button to continue.
CSV05 – Third screen of the Import Wizard dialog, defining the format of the values.
The final screen of the Import Wizard will allow us to define how the individual value columns are formatted. At this point, it is sometimes possible to simply skip this last screen by clicking the Finish button on page 2 of the Import Wizard which will leave all of the column formats as “General”. This will allow Excel to make the call of how to format the values. Where this method sometimes run into problems are when fields, such as “SchoolNumber” in the example above, have values with leading 0’s. Excel will then trim “001” down to ‘1’ as the program recognizes this as simply a number. Therefore, it is a good practice to select any fields where this would be considered a problem and select the radio button next to “Text”.
Unconventional date formatting can also be a problem, so assigning “Date” and selecting the format in the drop down box to the right of it is advisable as well.
After all of the columns have had their format assigned, click the Finish button.
CSV06 – Select where the imported file’s data will go.
Now that the format of the import has been defined, we now need to determine where the data will go. A small dialog box will replace the Import Wizard allowing us to select either the “Existing worksheet” with which we started the import process or “New worksheet” which will open an entirely new Excel worksheet in which to put the data.
Select the option that is most convenient for you and click the “OK” button.
CSV07 – Viewing the imported data.
After you have chosen in which worksheet the data will appear, you will be able to see the data separated into columns as in the above picture. Now that you have your data in Excel, it will be easy to sort and review your data.
Saving a CSV file in Microsoft Excel
You may create a CSV file in Excel by specifying header names in the first row of the worksheet and then the values corresponding to the headers in the subsequent rows.
CSV08 – Saving a CSV file.
To save an Excel file as a CSV file, click “Save” for a new file or “Save as” to save an existing file of another format (for instance. Excel format – .xls) as a CSV. In the Save dialog, specify the File name and then click the drop down box to the right and scroll down to “CSV (Comma delimited)”, and then click the Save button.
Opening a CSV file as a text file
There may be an instance where you would like to take a look at a CSV file as a text document.
CSV09 – Opening a CSV file with Notepad or a similar program.
In many cases, Microsoft Excel will be set as the default program to open up a CSV file even though it will alter data as described above if it is simply double-clicked. This is the case in the above image in that the icons for the file look like the Excel icon even though they are CSV files.
In the above case and in general, the file can be opened by right-clicking the file which will cause a menu to be displayed. In the new menu, hover over or click on “Open with”. Doing so will display a list of programs with which the file may be opened. Clicking “Notepad” will cause the file to appear as it does below.
CSV10 – A CSV file opened in Notepad.
For many CSV files containing a great deal of information, it is recommended that “Word Wrap” be turned off. This can be done by opening the “Format” menu and making sure that “Word Wrap” does not have a check next to it.
Notepad is not the only program that can be used to view the contents of a CSV file as text, but it is included on all Windows machines. Other useful programs to view a CSV file like this are TextPad and NotePad++ which display line numbers as well as provide users with other useful functions.
Tips for formatting a CSV file
In this section, we will review some helpful tips for formatting a CSV file.
- In a CSV file, each line is ONE record. In the examples used in this post, each line in the file used represented one student and the information relating to that individual student.
- Each delimiter used within the CSV file separates two values. “MALE,15” or “MALE, 15” both represent the values “MALE” and “15”.
- There must be as many values in a record (on a line) as there are column headers.
- Two commas next to each other represent a null/blank value corresponding to the column that would be in the next column header.
- Commas can be recorded in a CSV file ONLY if the value containing the comma is enclosed in parentheses. For example, in an address where there is a specific apartment, the value should be entered as “Street Address, Apt. 1” or “Brown, Jr.”.
- Leading or trailing spaces can be recorded in a CSV file ONLY if they are recorded in parentheses (ex: ” String “).
- Parentheses can be recorded in a CSV file ONLY if the parentheses are themselves enclosed in parentheses (this may sound confusing, but it is simply specified as: “”String””).
We hope that this look into how to better understand CSV files was helpful to you. As always if you have any questions about SCRIBE, including configuring your records into CSV files for our import process, please send us an email at SCRIBESupport@xcalibur.com.