How to avoid CSV formatting errors?

A Comma Separated Values (CSV) file is commonly used to store list of data. You can see many tools offer exported/imported data in a CSV file.

In this post, we will learn about some common issues we often encounter and how to avoid data breaking when export data to a CSV file.

Unwanted comma

CSV uses a comma to identify where to separate a column. What if your text contains a comma?

Phone, Battery
Samsung Galaxy S20+, 4,500
LG V60 ThinQ 5G, 5,000

If we view the data in Excel, we will see that columns are broken and are not shown as we want.

To fix this, wrap the unwanted comma with double quotes!

Phone, Battery
Samsung Galaxy S20+, "4,500"
LG V60 ThinQ 5G, "5,000"

New line between text

Be careful with new line which appears between text! Something like this “Long title of\n a product” will break a file’s rows. When a file reader encounters “\n” in your text, it pushes the sequel text to a new line.

To fix this, you should remember to escape or remove the line break when exporting your data.

Store a list in a cell

For example, we need to export a list of classes and their top three students to a file. We can’t use comma to separate sub-data because it will break the format. There are many solutions in this case. However, to view the data correctly you need to format it again later.

Any delimiters can be used instead of a comma.

Class A, Student 1|Student 2|Student 3

XML can be good when there are many attribute

<?xml version="1.0"?>
<students>
  <student>
    <name>Student 1</name>
    <rank>2</rank>
  </student>
  <student>
    <name>Student 2</name>
    <rank>1</rank>
  </student>
  <student>
    <name>Student 3</name>
    <rank>3</rank>
  </student>
</students>

Leave a Comment

Your email address will not be published. Required fields are marked *

We care about your privacy and data security. Google serves cookies to analyse traffic and show ads on this site. By using this site you agree to these cookies being set. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close