Newlines in csv files

[tshad]

I have a problem with a .csv file I am trying to read into an excel sheet.

I have all of my fields surrounded by quotes to prevent commas inside a text
field from being mistaken for a field separator. The problem is that some
of my fields have newlines in them as they are taken from a multiline
textbox on my web page and put into my sql table. I then read the sql table
and write out the table in .csv format. But I think the newlines are being
mistaken for line separators - even if within quotes.

If I have a text with 3 new lines, they will end up on 4 lines of my Excel
sheet.

Why is that? Is there a way around this problem?

Thanks,

Tom

 

[Guest]

You have to somehow get rid of the carriage returns in the textbox, excel is
seeing them as end-of-record even if they are in the quotes.
In that text field, can you replace chr(13) with a slash "/" or something?

 

[tshad]

You have to somehow get rid of the carriage returns in the textbox, excel
is
seeing them as end-of-record even if they are in the quotes.
In that text field, can you replace chr(13) with a slash "/" or something?

I realized that. I just don't know what to change it to. I don't want to
use something like a slash as that may show up in the text as well.

I was hoping there was a way to tell excel to ignore newlines when inside
the Quotes. If it doesn't what good are the Quotes?

Thanks,

Tom

 

[Dave Peterson]

Open the file in your favorite text editor and start looking for weird
characters. If you find a slash, don't use it. If you find a backslash, don't
use it.

I used the vertical bar in this code:

Option Explicit
Sub testme01()

Dim myFileName As Variant
Dim myContents As String
Dim FileNum As Long
Dim InQuotes As Boolean
Dim iCtr As Long

Dim FSO As Object
Dim myFile As Object
Set FSO = CreateObject("Scripting.FileSystemObject")

myFileName = Application.GetOpenFilename("Text Files, *.txt")
If myFileName = False Then
Exit Sub
End If

Set myFile = FSO.OpenTextFile(myFileName, 1, False)
myContents = myFile.ReadAll
myFile.Close

InQuotes = False
For iCtr = 1 To Len(myContents)
If Mid(myContents, iCtr, 1) = Chr(34) Then
InQuotes = Not InQuotes
ElseIf InQuotes Then
If Mid(myContents, iCtr, 1) = vbLf Then
Mid(myContents, iCtr, 1) = "|" '<-------
End If
End If
Next iCtr

myFileName = Left(myFileName, Len(myFileName) - 4) & ".out"

Set myFile = FSO.CreateTextFile(myFileName, True)
myFile.Write myContents
myFile.Close

End Sub

You'll have to find a good character to use and then modify the code to use
that.

Then after you import the file into excel, you can select the cells and do:

Edit|replace
what: | (or whatever character you used)
with: ctrl-j (same as alt-enter to force a new line in the cell)
replace all

==========
Heck, you could always use a space character and forget about the alt-enter in
the cell?????