CODEWITHSUNDEEP
vb.netexcelcsv
lsteinme
lsteinme

Reputation: 750

line break in CSV

What character or combination of characters do I have to insert into my csv file so that if I enable line breaks in Excel the text is correctly breaked at this point?

Private Sub removeHTMLTags(ByRef text As String)
    Dim htmlTagRegex As New Regex("<[^>]*>")
    Dim lineBreakHTMLTagRegex As New Regex("(<p>)|(<br>)|(</br>)|(</p>)")
    text = lineBreakHTMLTagRegex.Replace(text, Chr(10))
    text = htmlTagRegex.Replace(text, " ")
    text = text.Trim
End Sub

Instead of Chr(10) I also tried vbNewLine, vbCrLf, vbCr but every time with the same result: when the file is opened by Excel those signs are interpreted as normal line breaks, which leads to a split of rows in excel
That's what it should look like:

Soll

This what it does look like:

Ist

How do I need to do to achieve the style in the second screenshot if I want to simply load the csv file by double-clickling it, not over the import dialogue

remark: this is not VBA, I need to do this over an external vb.net console application that opens Excel after the csv file is created.

Upvotes: 2

Views: 1999

Answers (1)

Matt Wilko
Matt Wilko

Reputation: 27322

If you want to include CR and LF control characters then you need to wrap the field in double quotes.

If you do this I would suggest you should put double quotes round every field for consistency.

So your method would just look like this:

Private Sub removeHTMLTags(ByRef text As String)
    Dim htmlTagRegex As New Regex("<[^>]*>")
    Dim lineBreakHTMLTagRegex As New Regex("(<p>)|(<br>)|(</br>)|(</p>)")
    text = lineBreakHTMLTagRegex.Replace(text, Chr(10))
    text = htmlTagRegex.Replace(text, " ")
    text = "" & text.Trim & ""
End Sub

more details can be found here: http://www.creativyst.com/Doc/Articles/CSV/CSV01.htm

Upvotes: 1

Related Questions