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

source