Creating a CSV file using Notepad (or any text editor)

Creating a CSV file using Notepad (or any text editor)

A CSV file is a text file, so it can be created and edited using any text editor, But more frequently a CSV file is created by exporting a spreadsheet or database in the program that created it.

Creating CSV Normal File

To create a CSV file in Notepad, First open a new file using

File →New or ctrl +N.

Then enter the data you want the file to contain, separating each value with a comma and each row with a new line.

For example consider the following details

Topic1,Topic2,Topic3

one,two,three

Example1,Example2,Example3

Save this content in a file with the extension .csv . You can then open the same using Microsoft Excel or any other spreadsheet program. Here we have opened using Microsoft Excel. It would create a table of data similar to the following:

In the above CSV file, you can observe the fields of data were separated by commas. But what happens if the data itself contains 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.

Creating CSV File That contains Comma With Data

For example, let’s say that one of our fields contain commas in the description. If our data looked like the below example:

To retain the commas in “Address” column, you can enclose the fields in quotation marks. For example:

RollNo, Name, Address

12101, Nivetha, “Mylapore, Chennai”

12102, Lavanya, “Adyar, Chennai”

12103, Ram, “Gopalapuram, Chennai”

As you can see, only the fields that contain commas are enclosed in quotes. If you open this in MS Excel, It looks like as follows

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.

For Example

It should be written in CSV file as

RollNo, Name, Address

12101, Nivetha, “Mylapore, Chennai”

12102, Lavanya, “Adyar, Chennai”

12103, Ram, “Gopalapuram, Chennai”

The Result will look like this

Creating CSV File That contains Double Quotes With Data

If your fields contain double-quotes as part of their data, the internal quotation marks need to be doubled so that they can be interpreted correctly. For Example, given the following data:

It should be written in csv file as

RollNo, Name, FavoriteSports, Address

12101, Nivetha,””” Cricket ””,”” Football ”””, Mylapore chennai 12102, Lavanya,””” Basketball ””,”” Cricket ”””, Adyar hennai 12103, Ram,””” Soccer””,”” Hockey”””, Gopalapuram Chennai

The output will be

Rules to be followed to format data in a CSV file

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

xxx,yyy 

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

3. 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 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_name1,field_name2,field_name3 

4. Within the header and each record, there may be one or more fields, separated by commas. 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: Red , Blue

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:

6. Fields containing line breaks (CRLF), double quotes, and commas should be enclosed in double-quotes. For example:

Red, “,”, Blue CRLF # comma itself is a field value.so it is enclosed with double quotes Red, Blue , Green

7. If double-quotes are used to enclose fields, then a double-quote appearing inside a field must be preceded with another double quote. For example:

“Red, ” “Blue”, “Green”,          # since double quotes is a field value it is enclosed with another double quotes

, , White