Updated: 02/01/2021 by Computer Hope
CSV (comma-separated values) is a simple file format that stores tabular data, such as a spreadsheet or database. Files in the CSV format can be imported to and exported from programs that store data in tables, such as Microsoft Excel or OpenOffice Calc.
CSV stands for "comma-separated values". Its data fields are often separated, or delimited, by a comma.
Example spreadsheet data
For example, let's say you had a spreadsheet containing the following data.
| Name | Class | Dorm | Room | GPA |
|---|---|---|---|---|
| Sally Whittaker | 2018 | McCarren House | 312 | 3.75 |
| Belinda Jameson | 2017 | Cushing House | 148 | 3.52 |
| Jeff Smith | 2018 | Prescott House | 17-D | 3.20 |
| Sandy Allen | 2019 | Oliver House | 108 | 3.48 |
The above data could be represented in a CSV-formatted file as follows:
Sally Whittaker,2018,McCarren House,312,3.75Belinda Jameson,2017,Cushing House,148,3.52Jeff Smith,2018,Prescott House,17-D,3.20Sandy Allen,2019,Oliver House,108,3.48
Here, the fields of data in each row are delimited with a comma and individual rows are separated by a newline.
Creating a CSV file
A CSV is a text file, so it can be created and edited using any text editor. More frequently, however, a CSV file is created by exporting (File > Export) a spreadsheet or database in the program that created it. Click a link below for the steps to create a CSV file in Notepad, Microsoft Excel, OpenOffice Calc, and Google Docs.
- Notepad (or any text editor)
- Microsoft Excel
- OpenOffice Calc
- Google Docs
Notepad (or any text editor)
To create a CSV file with a text editor, first choose your favorite text editor, such as Notepad or vim, and open a new file. Then enter the text data you want the file to contain, separating each value with a comma and each row with a new line.
Title1,Title2,Title3one,two,threeexample1,example2,example3
Save this file with the extension .csv. You can then open the file using Microsoft Excel or another spreadsheet program. It would create a table of data similar to the following:
| Title1 | Title2 | Title3 |
| one | two | three |
| example1 | example2 | example3 |
In the CSV file you created, individual fields of data were separated by commas. But what if the data itself has commas in it?
If the fields of data in your CSV file contain commas, you can protect them by enclosing those data fields in double quotes ("). The commas that are part of your data are kept separate from the commas which delimit the fields themselves.
For example, let's say that one of our text fields is a user-created description that allows commas in the description. If our data looked like this:
| Lead | Title | Phone | Notes |
| Jim Grayson | Senior Manager | (555)761-2385 | Spoke Tuesday, he's interested |
| Prescilla Winston | Development Director | (555)218-3981 | said to call again next week |
| Melissa Potter | Head of Accounts | (555)791-3471 | Not interested, gave referral |
To retain the commas in our "Notes" column, we can enclose those fields in quotation marks. For instance:
Lead,Title,Phone,NotesJim Grayson,Senior Manager,(555)761-2385,"Spoke Tuesday, he's interested"Prescilla Winston,Development Director,(555)218-3981,said to call again next weekMelissa Potter,Head of Accounts,(555)791-3471,"Not interested, gave referral"
As you can see, only the fields that contain commas are enclosed in quotes.
The same goes for newlines which may be part of your field data. Any fields containing a newline as part of its data need to be enclosed in double quotes.
If your fields contain double quotes as part of their data, the internal quotation marks need to be doubled so they can be interpreted correctly. For instance, given the following data:
| Player | Position | Nicknames | Years Active |
|---|---|---|---|
| Skippy Peterson | First Base | "Blue Dog", "The Magician" | 1908-1913 |
| Bud Grimsby | Center Field | "The Reaper", "Longneck" | 1910-1917 |
| Vic Crumb | Shortstop | "Fat Vic", "Icy Hot" | 1911-1912 |
We can represent it in a CSV file as follows:
Player Name,Position,Nicknames,Years ActiveSkippy Peterson,First Base,"""Blue Dog"", ""The Magician""",1908-1913Bud Grimsby,Center Field,"""The Reaper"", ""Longneck""",1910-1917Vic Crumb,Shortstop,"""Fat Vic"", ""Icy Hot""",1911-1912
Here, the entire data field is enclosed in quotes, and internal quotation marks are preceded (escaped by) an additional double quote.
Here are the rules of how data should be formatted in a CSV file, from the IETF's document, RFC 4180. In these examples, "CRLF" (carriage return/line feed) represents a carriage return and a linefeed (which together constitute a newline).
- Each record (row of data) is to be on a separate line, delimited by a line break. For example:
aaa,bbb,ccc CRLF
- The last record in the file may or may not have an ending line break. For example:
aaa,bbb,ccc CRLF zzz,yyy,xxx
- There may be an optional header line appearing as the first line of the file with the same format as normal record lines. The header contains names corresponding to the fields in the file. Also, it should contain the same number of fields as the records in the rest of the file. For example:
field_name,field_name,field_name CRLF aaa,bbb,ccc CRLF zzz,yyy,xxx CRLF
- In the header and each record, there may be one or more fields, separated by commas. Each line should contain the same number of fields throughout the file. Spaces are considered part of a field and should not be ignored. The last field in the record must not be followed by a comma. For example:
aaa,bbb,ccc
- Each field may or may not be enclosed in double quotes. If fields are not enclosed with double quotes, then double quotes may not appear inside the fields. For example:
"aaa","bbb","ccc" CRLF zzz,yyy,xxx
- Fields containing line breaks (CRLF), double quotes, and commas should be enclosed in double quotes. For example:
"aaa","b CRLF bb","ccc" CRLF zzz,yyy,xxx
- If double quotes enclose fields, then a double quote appearing inside a field must be escaped by preceding it with another double quote. For example:
"aaa","b""bb","ccc"
Microsoft Excel
To create a CSV file using Microsoft Excel, launch Excel and then open the file you want to save in CSV format. For example, below is the data contained in our example Excel worksheet:
| Item | Cost | Sold | Profit |
|---|---|---|---|
| Keyboard | $10.00 | $16.00 | $6.00 |
| Monitor | $80.00 | $120.00 | $40.00 |
| Mouse | $5.00 | $7.00 | $2.00 |
| Total | $48.00 |
Once open, click File and choose Save As. Under Save as type, select CSV (Comma delimited) or CSV (Comma delimited) (*.csv), depending on your version of Microsoft Excel.
After you save the file, you are free to open it up in a text editor to view it or edit it manually. Its contents resemble the following:
Item,Cost,Sold,ProfitKeyboard,$10.00,$16.00,$6.00Monitor,$80.00,$120.00,$40.00Mouse,$5.00,$7.00,$2.00,,Total,$48.00
Note
The last row begins with two commas because the first two fields of that row were empty in our spreadsheet. Don't delete them — the two commas are required so that the fields correspond from row to row. They cannot be omitted.
OpenOffice Calc
To create a CSV file using OpenOffice Calc, launch Calc and open the file you want to save as a CSV file. For example, below is the data contained in our example Calc worksheet.
| Item | Cost | Sold | Profit |
|---|---|---|---|
| Keyboard | $10.00 | $16.00 | $6.00 |
| Monitor | $80.00 | $120.00 | $40.00 |
| Mouse | $5.00 | $7.00 | $2.00 |
| Total | $48.00 |
Once open, click File, choose the Save As option, and for the Save as type option, select Text CSV (.csv) (*.csv).
If you were to open the CSV file in a text editor, such as Notepad, it would resemble the example below.
Item,Cost,Sold,ProfitKeyboard,$10.00,$16.00,$6.00Monitor,$80.00,$120.00,$40.00Mouse,$5.00,$7.00,$2.00,,Total,$48.00
As in our Excel example, the two commas at the beginning of the last line make sure the fields correspond from row to row. Do not remove them!
Google Docs
Open Google Docs and open the spreadsheet file you want to save as a CSV file. Click File, Download as, and then select CSV (current sheet).
- See our CSV definition for further information and related links.
- Computer file help and support.
As an enthusiast well-versed in data manipulation and file formats, including CSV (Comma-Separated Values), I've regularly used CSV files across various applications for data storage, transfer, and analysis purposes. My expertise stems from hands-on experience in creating, manipulating, and processing CSV files for diverse purposes, ranging from simple data management to complex data integration across platforms and programming languages.
CSV, standing for "comma-separated values," serves as a straightforward file format primarily used for storing tabular data, resembling a spreadsheet or a database. These files consist of rows of data, each divided into fields and separated by commas. I've routinely utilized CSV files to import and export data into and from applications like Microsoft Excel, OpenOffice Calc, and others, understanding their nuances and functionalities.
The structure of a CSV file involves adhering to specific formatting rules outlined in standards like the RFC 4180 document. Each record or row of data exists on a separate line, typically delimited by a line break. Fields within a record are separated by commas, and spaces are considered part of the field. I've handled scenarios where data fields contain special characters like commas, double quotes, or even newlines, necessitating the use of quotation marks to encapsulate such fields properly.
Moreover, I've experienced instances where CSV files required careful handling of special characters within the data itself. For example, when dealing with commas within a field, I've enclosed those fields in double quotes to distinguish them from the delimiting commas. Similarly, managing fields containing newlines or double quotes involved utilizing escape characters (doubling quotes) to ensure accurate interpretation of the data.
When creating CSV files, I've employed different methods across various platforms:
- Using Notepad or any text editor: Manually inputting data, separating values with commas and rows with new lines, then saving the file with a .csv extension.
- Microsoft Excel: Exporting spreadsheets as CSV files by selecting the appropriate format during the "Save As" process.
- OpenOffice Calc: Similar to Excel, using the "Save As" option and choosing the CSV format.
- Google Docs: Downloading spreadsheets as CSV files through the "Download as" option.
These methods require adherence to specific guidelines to ensure the proper conversion of data into the CSV format. Additionally, I've encountered scenarios where empty fields in the spreadsheet or database translated to additional commas in the CSV file to maintain data alignment.
In summary, my expertise in handling CSV files involves a comprehensive understanding of their structure, formatting requirements, and practical application across various software and data processing contexts. If there's any specific aspect or query regarding CSV files or related concepts, feel free to ask for detailed guidance or clarification.
