Using Excel to open CSV files

The file extension .csv stands for "comma separated value." CSV files are long text lists of numbers where each data entry is separated by a comma (,). This is a popular format for transferring data from one application to another. Additionally, you may save Excel spreadsheets in this format and you may open CSV files in Excel.

For example, data pulled from a database and represented in comma-delimited format looks something like the following. Each column value is separated by a comma from the next column’s value and each row starts a new line:

LASTNAME, FIRSTNAME, AGE, SEX 
Adams, Jane, 46, female
Doe, John, 32, male
Jones, Sam, 13, male
Smith, Mary, 64, female

You will notice that the first row identifies the column headings for all subsequent rows.

There are two ways to open a CSV file in Excel. The first is just to click the “Open File” button (or select Open from the File menu). You will probably not see the file listed. Click on the "Files of type" bar and select Text Files. You should now see your .csv file listed, and you should be able to open it by double-clicking on the file.

However, opening a file in this manner may alter the data contained in the CSV file. MS Excel sometimes makes assumptions about the data and therefore converts it to a certain format. Here are a few examples:

• Dates – If your CSV file has a column that contains a number that appears to be a date (e.g., 02/04/2009 – or mm/dd/yyyy) Excel will actually change the value to its preferred date format (e.g., 2/4/09 – or m/d/yy)
• Zip Codes – If you have zip codes with leading zeros (e.g., 06611) Excel will assume that this column contains a regular number and strip out the leading zero (e.g., 6611)
• Credit Card Numbers - Excel will also assume that this is a number field. MS Excel will only allow up to 15 digits in a number cell. Microsoft Office Excel changes any digits past the fifteenth place to zeros. In addition, Excel displays the number in exponential notation, replacing part of the number with E+n, where E (which signifies exponent) multiplies the preceding number by 10 to the nth power.

1. Open Excel, on the Data menu, choose Import External Data, then Import Data. Browse to the CSV file, and click Open. This brings up the Text Import Wizard.
2. In step 1, select Delimited. 
    
3. In step 2, check Comma; this previews how the data will be separated.
    
4. In step 3, you can select each column in turn and choose a format. We recommend selecting “text” as the format for every column.
    
5. Save the new file an excel spreadsheet (*.xls).