The often misunderstood CSV file format.

Interfacing information between different systems is a critical task for any enterprise large or small. Tax reporting, federal reporting, state reporting, sharing data between systems, and even web service and ajax data access are just some of the interfacing concerns that are faced.

Sometimes you don't have a choice on the format the data must be in when sending data to other systems. But where you do have a choice, CSV format should be at the top of your list. It is THE smallest size of any format and, as a result, it has the fastest transfer speed. It also has very simple rules that guarantee flexibility. The following is the most common set of rules.

1. Separate data using a comma (standard) or another character like a tab or pipe
2. Enclose strings using double-quotes to allow commas in strings. (if needed)
3. Escape double-quotes inside strings using a designated escape character; usually backslash (\"). (if needed)
4. Escape the designated escape character if used in string data (\\). (if needed)

Example:

"001416","David \"The Tank\"","Davidson, II",33

Those coming from XML or JSON may say, "That data is not self describing." Easy... just put a single header row with the field titles in it. With a field title header, the data fields in the file could be in a different order but still be loaded properly by the receiver. (But most interfaces require consistent ordering... which isn't hard to do, and thus, they don't need a header.)

"employeeNumber","firstName","lastName","age"
"001416","David \"The Tank\"","Davidson, II",33

Still not good enough? Then put another row below the header to describe the data type or other information. (Though most interfaces already know what the data is without being told.)

What about object-relational data? Again, easy... just add fields that identify the data being stored and describe the relationship for related data (much like foreign keys in a database table). Each row can have the format needed for the data being stored.

"Employee","001416","David \"The Tank\"","Davidson, II",33
"Dependent","001416","Margot","Davidson","Wife"
"Dependent","001416","Julie","Davidson","Daughter"
"Benefit","001416","Health","Family Plan"
"Benefit","001416","Vision","Family Plan"
"Benefit","001416","Dental","Family Plan"

Another benefit of CSV is that it does not require complex data structures or libraries to use it (though I'm sure they exist). CSV is simple to build and simple to parse so you can write your own code without a lot of effort.

Following the simple rules of CSV means your data can contain ANYTHING and will be small, light, and speedy!