0

I want to export SQL tables from my database into CSVs, but I got some fields in my tables that contain line breaks and carriage returns.

When I exported the tables, the CSV file displayed every line in the field as a new row,so I tried the solution in this question to replace the line feeds and carriage returns with empty space.

However, this will alter the data since I need to keep the line feeds and carriage returns so that when I import the CSV files back into my database I will get the field structure in the same way.

Is there any way I can export my multiline data to CSV that maintains the field text structure?

Community
  • 1
  • 1
user3340627
  • 3,023
  • 6
  • 35
  • 80

1 Answers1

-1

Please see the standard rfc4180.

As it states, there is no formal specification for CSV. Rather, there are many different file formats called by the common name CSV. What format is meant by the name "CSV" depends on the program you use.

While there are various specifications and implementations for the CSV format (for ex. [4], [5], [6] and [7]), there is no formal
specification in existence, which allows for a wide variety of
interpretations of CSV files.

[4] Repici, J., "HOW-TO: The Comma Separated Value (CSV) File Format", 2004, http://www.creativyst.com/Doc/Articles/CSV/CSV01.htm.

[5] Edoceo, Inc., "CSV Standard File Format", 2004, http://www.edoceo.com/utilis/csv-file-format.php.

[6] Rodger, R. and O. Shanaghy, "Documentation for Ricebridge CSV Manager", February 2005, http://www.ricebridge.com/products/csvman/reference.htm.

[7] Raymond, E., "The Art of Unix Programming, Chapter 5", September 2003, http://www.catb.org/~esr/writings/taoup/html/ch05s02.html.

But it does give a common solution

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

  2. 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"

Community
  • 1
  • 1
Marko Kohtala
  • 744
  • 6
  • 16
  • Hi. I see downvotes on my answer. I want to provide a useful answer, but I do not know what is wrong with this one. Please comment why you downvote so I can improve the answer or remove it if I am reading the question wrong. – Marko Kohtala Jan 09 '20 at 06:40