Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Newlines in csv files
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Newlines in csv files
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" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Newlines in csv files
"Marvin P. Winterbottom"
wrote in message ... 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 "tshad" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Newlines in csv files
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????? tshad wrote: "Marvin P. Winterbottom" wrote in message ... 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 "tshad" wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
converter tool to convert XL 2007 files to XL 2003 files | Excel Discussion (Misc queries) | |||
Can I save excel files as web connected files | Excel Discussion (Misc queries) | |||
Drive Erased, got Files back but only excel files scrambled, help. | Excel Discussion (Misc queries) | |||
I want to attach word files or PDF files to an excel database | Excel Discussion (Misc queries) | |||
How can I view files chronologically when opening multiple files | Excel Discussion (Misc queries) |