CRLFs from a csv file

jeffg22

New Member
Joined
Sep 25, 2002
Messages
3
I'm using Visual Basic (actually embedded Visual Basic) to write a program that will create a csv file to be opened in Excel. My problem is with CRLF (Carriage Return - Line Feed) characters.

I know how to insert CRLFs (Alt-Enter) into a workbook cell for multi-line cell entries. But what character or character combination do I have my program insert into my file to achieve the same multi-line result?? I've tried vbCrLf, Chr(13) & Chr(10), and ChrW(13) & ChrW(10), with no success.

My file is a name and address file. If I have a record that looks like:

"John","Jones","100 Main Street[CRLF here]Suite 700","New York","NY"

Excel sees the embedded CRLF as a record delimiter, even though it's within the quotation mark field delimiters. So I end up with 2 records instead of one.

I'm using Excel 2000, but I have no control over what my eventual users will be using.

Again, my question is what character or character combination do I have my program insert into my file to achieve a multi-line cell entry in Excel??




_________________
- Jeff
This message was edited by jeffg22 on 2002-09-26 16:53
This message was edited by jeffg22 on 2002-09-26 16:57
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Damon Ostrander

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
Hi Jeff,

I don't believe there is a way to do this directly because Excel wants to interpret everything in a csv file as text characters. As you probably know, the character you need to add to your text string to get it to wrap in a cell is a line feed (chr(10) or vbLf). One fairly easy way to accomplish what you want is to embed a string such as <LF> in your csv file at the point you want the line feed inserted. Of course, when you import or open the file this will just be read as a string. But now you can use a simple macro to convert all the <LF> strings to line feed characters. Here is a simple macro you can use to convert all "<LF>" strings to line feed characters on the active worksheet:

Sub LFreplace()
'Replace <LF> strings with linefeed characters in the entire active worksheet
Dim Cel As Range
Dim nCh As Integer
For Each Cel In ActiveSheet.UsedRange
nCh = InStr(1, Cel, "<LF>")
If nCh <> 0 Then
Cel = Left(Cel, nCh - 1) & vbLf & Mid(Cel, nCh + 4)
End If
Next Cel
End Sub
 

jeffg22

New Member
Joined
Sep 25, 2002
Messages
3
Thanks, Damon, but that won't work for me since I have no access to my users' activities, other than via my application.

I have control over creation of the csv file and can include any combination of characters in it. The file is being created as a result of a user selecting an export operation within my application.

But I have no control over my users or the applications they use, much less macros within those applications.

Some users will be importing/opening my csv file in Excel; some may be opening it with other applications that can import csv files. Most will not be familiar or comfortable with the concept of implementing Excel macros.

[I always thought that the idea of delimiter characters, like quotes, was that any other delimiter or separator between them would/should be ignored.]

Any other ideas or suggestions?? Anyone??
 

Damon Ostrander

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
Hi again Jeff,

I just checked and found out that you CAN indeed insert linefeeds into a csv file and Excel will use them to make a cell's contents multiple lines. I did this by writing a simple csv file using VBA I/O with a Write # statement.

So, just concatenate a vbLf (or Chr(10)) directly into the text string. I believe the reason that none of the methods you used worked is that the carriage return character must NOT be included, because this signals the end of the record in a csv file.

Also, some text editors will also allow you to insert a linefeed character directly into your text using a Ctrl-J (the control sequence corresponding to a linefeed).

Damon
 

jeffg22

New Member
Joined
Sep 25, 2002
Messages
3
OK. I think I've got this figured out. It's one of those things that makes you think about choosing another line of work.

It turns out that several of the things I tried successfully removed the Chr(13)s from the exported file, but Excel still would break to a new line when it encountered a Chr(10). I even broke out an old hex viewer to make sure the contents of my file were identical to the contents of a file saved as a csv file FROM Excel (they were).

The problem was that I was doing what many of my users are probably going to do: I was naming the export file with a .txt extension. Rename that file to have a .csv extension, making no other changes, and it works just fine!

Apparently the .txt extension triggers Excel's import 'wizard,' which apparently doesn't handle Chr(10)s very well.

I've also tested my exported file by trying to import it into Goldmine (a desktop contact management program). Goldmine doesn't care about the file extension; use whatever you like. But Goldmine turns ugly with ANY multi-line field (i.e., any Chr(10)s and/or Chr(13)s). Haven't figured that one out yet.

Thanks to all of you who spent time thinking about this question and trying to help. Hopefully, this thread will help some future developer from pulling out too much of his/her hair.

- Jeff
 

Forum statistics

Threads
1,144,293
Messages
5,723,554
Members
422,503
Latest member
aarifmahmood

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top