0

I have data in a database that needs to be exported to CSV. Some of the fields include addresses and such, which include newlines which MUST be preserved. The data is being exported like this (this is sample data of course):

"Bob Smith",38,"Fire Chief","1234 Obscure St.
Random Town, NA, 12345"

Which doesn't show up correctly in Excel, or import correctly with TextFieldParser class. Is it possible to export newlines in CSV files? How?

Entity
  • 7,972
  • 21
  • 79
  • 122

2 Answers2

1

Substitute the newline characters in the data with a unique combination of other characters (eg,"*#^$&@!"). Undo the substitution on the other end.

miyamotogL
  • 513
  • 3
  • 10
  • I would use vba to replace the unique combination with either char(10) or vbLf. (eg. Range("A1").value = "This is" & chr(10) & "a test"). The format of the cell must have word wrapping turned on. It might be possible to use the "Replace" formula instead, but I do not have Excel installed to check. – miyamotogL Apr 28 '11 at 02:25
  • It ultimately depends on the problem that is being solved; are we talking E.T.L., a report, or just a data dump (backup, export, etc.)? – miyamotogL Apr 28 '11 at 02:33
  • Well thats why my answer is better than reaplcing say @ for an enter. what happens in an email adress? ok so you use " , what happens if you get quotes? You can use ! but never shout or # but never use it to define a number. An escape character sequence of /n which is defined by a standard is the least common occurring sequence of characters in any speaking language and is used in Java, VB, and other languages as an accepted standard for new line character in string only data sets... I don not think this is the correct answer!@!# /nYou see – Piotr Kula Apr 28 '11 at 07:08
0

CSV does not support special escape character like new lines.

But

In most cases you would need to replace special characters like caridge return, whitespaces with special characters assigned to a format. 

\n

All in one Loooong line and when it gets parsed into another application these codes get replaced with the correct characters.

Try and export to XML. It will save you hours of frustrations!

Wont solve opening in Excel ...

Piotr Kula
  • 9,597
  • 8
  • 59
  • 85
  • 1
    http://stackoverflow.com/questions/1241220/generating-csv-file-for-excel-how-to-have-a-newline-inside-a-value – Piotr Kula Apr 27 '11 at 16:32