CSV stands for comma-separated values. It refers to a plain ASCII file that contains data. CSV files are often used for exporting data from one application into another. For example, most online e-mail services allow you to export your address book as a CSV file. That way, if you ever want to switch from using online web mail to using an off-line e-mail client (such as Microsoft Outlook or Mozilla Thunderbird), you can. Or, perhaps, you simply want to load data into a spreadsheet, or a document, or some other local file on your computer to work with it. You can do that, too.

CSV files are usually named with a *.CSV extension. If you have Microsoft Excel installed, then the *.CSV file name extension is probably associated with Excel. So, double-clicking on a *.CSV file to open it would launch Excel and load the file into a new spreadsheet.

Riddle me this: Half the time, double-clicking on a *.CSV file to open it in Excel works perfectly fine — the data appears in neat columns and rows. Why is it, then, that half the time the data is scrunched up all in the A column?

The problem is that CSV isn’t a standard. Programmers have lots of different interpretations for how a CSV file should be formed. So, the “CSV” file that’s is exported by one program could be wildly different from the CSV file exported by another, even if the actual data was the same. For example, one big difference is in the field separator.

Comma-Separated Values: The CSV name is a holdout from the days when comma was a popular field separator. Notice here how “Public, John Q.” has to be in quotes so that the comma that is part of the value is not taken for a field separator.

Company Name,Contact Name,Phone Number
Acme Inc.,"Pubic, John Q.",(123) 456-7890

Tab-Separated Values: These days, files with tab-separated values are much more popular, because it means that the data itself can contain commas (without having to put them in quotes).

Company Name»Contact Name  »Phone Number
Acme Inc.   »Pubic, John Q.»(123) 456-7890

Pipe-Separated Values: One drawback to tab-separated values is that the tabs are invisible (unless you set your editor to show whitespace). So, some programmers opt to use yet another character that does not otherwise appear in the data. Symbols commonly used include the vertical-bar/”pipe” (|), the tilde (~), and the semicolon (;).

Company Name|Contact Name|Phone Number
Acme Inc.|Pubic, John Q.|(123) 456-7890

Other Variations: Just FYI, other ways in which the non-standard nature of CSV files manifest include:

• Trailing separators might be suppressed if there are no corresponding values
• One,Two,Three,,,,,
• One,Two,Three
• As with any ASCII file, the end-of-line separator can differ (PC style, UNIX style, Mac style, or rarely, even something else entirely)
• Which fields get quoted can differ:
• only fields that contain commas
• all text fields, but not numeric and date fields
• all text and date fields, but not numeric
• all fields
• The first line of the file is often the names of the fields, but not always

What’s in a Name?: As I said, CSV files are usually named with a *.CSV extension, although sometimes they will have a *.TXT extension (which stands for “text”, since CSV files are plain ASCII text files), or even some other completely arbitrary extension. It has become tradition, though, to continue to use the *.CSV filename extension to denote tab-separated these files, even though, technically, “TSV” would be more appropriate.

CSV Oddities: So, the problem is, a lot of software these days, when presented with a “*.CSV” file, expect the data to be tab-separated. If the data is actually, truly comma-separated, the software doesn’t know what to do with it. In the case of Microsoft Excel, it can handle any and all of these variations. By default, however, it only looks for tabs in a CSV file. The tabs tell Excel to put each value in a separate cell.

When presented with a CSV file that is genuinely comma-separated, however, Excel doesn’t initially pay attention. Each line of the CSV file ends up in a single cell (i.e. just one, big A column).

Case in Point: Adelphia cable was recently bought out by Time Warner, and everyone who was using Adelphia’s online e-mail client is being converted to RoadRunner. Both the old and new system allow you to export your address book to a CSV file. On Adelphia, the file produced was tab-separated, but the “new” RoadRunner system still produces files that are actually comma-separated. So, anyone who is used to double-clicking on a CSV file produced by Adelphia and seeing the addresses come up in Excel all nice and neat is in for a surprise. Double-click on a CSV file produced by RoadRunner, and all is not well. — Same filename extension, different results.

Manually Parsing Comma-Separated Values in Excel: For those of you running Excel, here’s how to fix it when a single column is loaded with comma-separated values, and you want to spread them out:

1. Click on the letter A at the top of the column. This will highlight all of the cells in the column.
2. Pull down the data menu and select “Text to Columns…”
3. In the “Step 1″ dialog, you’ll be given a choice of “Delimited” or “Fixed With”. Choose “Delimited” and then click on the “Next>” button.
4. In the “Step 2″ dialog, there are choices for delimiter types. You will probably see that “tab” is checked, but the others are not. Place a checkmark next to “Comma”. (It’s okay to leave “Tab” checked as well.) As soon as you place the checkmark next to “Comma” the data preview should suddenly look much better.
5. At this point, you can skip step 3. Just click directly on the “Finish” button.

Read more: Software, Windows