How To Create A CSV File

CSV is a simple file format used to store tabular data, such as a spreadsheet ordatabase. 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 most often separated, ordelimited, by a comma. 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

This data could be represented in a CSV-formatted file as follows:

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

Here, the fields of data in each row are delimited with a comma and individual rows are separated by a newline.

A CSV is a text file, so it can be created and edited using any text editor. More frequently, however, is created by exporting (File Menu -> Export) a spreadsheet or database in the program that created it. Click on 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 asNotepad or vim, and open a new file. Then enter the text data you want the file to contain, separating each field with a comma and each row with a new line.

Title1,Title2,Title3
one,two,three
example1,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 just 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 will then be 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 of some kind and users are allowed to put 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,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"

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", "Very, Very Fat Vic"	1911-1912

We can represent it in a CSV file as follows:

Player Name,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"", ""Very, Very Fat Vic""",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" is used to represent a carriage returnand a linefeed (which together constitute a newline).

1. Each record (row of data) is to be located on a separate line, delimited by a line break. For example:
   
   

   aaa,bbb,ccc CRLF

2. The last record in the file may or may not have an ending line break. For example:
   
   

   aaa,bbb,ccc CRLF
      zzz,yyy,xxx

3. There may be an optional header line appearing as the first line of the file with the same format as normal record lines; this header will contain names corresponding to the fields in the file and 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

4. Within 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

5. 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

6. 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

7. If double-quotes are used to 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, choose the Save As option, and for the Save as type option, 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 to edit it manually. Its contents will resemble the following:

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

Note: You'll notice that the last row begins with two commas. This is 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).

After you save the file, if you were to open the CSV file in a text editor, such as Notepad, the CSV file should resemble the example below.

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

Just as in our Excel example, the two commas at the beginning of the last line are necessary to 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. ClickFile, Download as, and then select CSV (current sheet).