Why quotation marks are used in CSV
Now that you may fancy yourself a CSV aficionado, you may be coming up with more in-depth questions about the data in your CSV. One of those questions may be:
What do I do if I have a comma within the text of one of my fields, that should not be treated as a separator?
Great question! And that is what brings us to our topic about quotation marks.
What do I do if I have a comma within the text of one of my fields, that should not be treated as a separator?
Great question! And that is what brings us to our topic about quotation marks.
Quotation marks are used as text qualifiers
Quotation marks appear in CSV files as text qualifiers. This means, they function to wrap together text that should be kept as one value, versus what are distinct values that should be separated out.
This is particularly relevant when you take data from a CSV - which maintains its simple structure by commas - and open that data in a spreadsheet program or import it into a database, and other such scenarios. The program or database needs to know which commas are true delimiters and which commas are just part of the text within your file.
Remember our CSV to Excel conversion article, in step 2, Excel requires that you specify what you are using as your text qualifier. It needs this information so that when you open your file, your data is accurately parsed out into the columns and rows as you intended.
Quotation marks appear in CSV files as text qualifiers. This means, they function to wrap together text that should be kept as one value, versus what are distinct values that should be separated out.
This is particularly relevant when you take data from a CSV - which maintains its simple structure by commas - and open that data in a spreadsheet program or import it into a database, and other such scenarios. The program or database needs to know which commas are true delimiters and which commas are just part of the text within your file.
Remember our CSV to Excel conversion article, in step 2, Excel requires that you specify what you are using as your text qualifier. It needs this information so that when you open your file, your data is accurately parsed out into the columns and rows as you intended.
Let's look at a real example:
Your CSV file contains currency values. So, you may have a row that looks like this.
As you may notice, one of your values (16,000) contains a comma, which is also your file delimiter. The only way for the computer to understand this is one value is to wrap it in quotes!
Your row should then look like this.
Your row should then look like this.
Again, the quotes prevent the comma inside of the value from being interpreted as a separator. It keeps your value (16,000) in one field, instead of two.
If you opened your CSV in a spreadsheet program, you can see the difference in how these two rows appear.
If you opened your CSV in a spreadsheet program, you can see the difference in how these two rows appear.
vs.
The same principle would apply to different delimiters as well. For example, if you are using semi-colon as your separator, you would apply quotation marks around any text containing a semi-colon that you wish to keep together. This is why some advocate for using a delimiter that won't pop up in your text values. This way you don't have to worry about double quoting data within your file.
Other scenarios you will find quotation marks used are the following:
Other scenarios you will find quotation marks used are the following:
- When your field value has line breaks you wish to retain. Your row should then look like this.
Your value will then appear like this when opened in a spreadsheet.
- When you have quotes in a field value that you wish to retain. Your row should then look like this.
Your value will then appear like this when opened in a spreadsheet.
Using quotation marks ensures that the values in your file are kept intact, rather than translated incorrectly into separate fields when you go to view that data in Excel or any other database or app. Whether your data contains currencies, email messages with line breaks, quoting feedback from customers, and more - using a text qualifier will ensure your data integrity is kept intact.