Removing carriage returns from a CSV file.

[Graham]

Hi,
I'm afraid I am a complete VBA novice, so apologies if this
is a dead simple question to you guys out there!
The problem I have is that I need to migrate a flat file
containing data that is variable length, comma delimited
with each field quotated, into an Excel spreadsheet.
However many of the fields contain "carriage returns" (CR)
within the data.
e.g.:
"ID","FIELDA","FIELDB","COMMENT","LASTFIELD"
"1234","ABC","DEF","No carriage return comment","XYZ"
"1235","SDF","HJK","A comment with a carriage return.
Next line of comment.","MNB"
"1236","DFG","ERT","Another normal comment","JKL"

I would therefore like to write a macro that does the
following:
Start Loop
Find the next line that does not start with '"'
End of File? Yes, Stop, No, Carry on.
Backspace to delete the CR,
insert space
Go to Start Loop.

If someone could provide me with Word/Excel VB code to do
this, I would be very grateful.
Thanks,
Graham

 

[Jonathan West]

Hi Graham,

You can probably manage this without a macro, just with a few find & replace
operations. The following article should point you in the right direction.
You will probably have to adapt the exact find and replace strings suggested
there, but it should give you the right idea.

Eliminate carriage returns (paragraph marks) at the end of each line but not
at the end of each paragraph
http://www.mvps.org/word/FAQs/General/DeleteParaMarksAtEndOfLines.htm

If you want to make a macro of this, simply record a macro while you do the
actions by hand, once you are satisfied you have them right.

 

[JGM]

Hi Graham,

If you are talking about regular Word paragraph marks (carriage return, or ¶
with "Sow all" turned on), then, have you tried a simple Find/Replace?
Find paragraph marks (^p) and replace by (blank)...

HTH
Cheers!

 

[Graham]

Thanks for this response. However, I don't think it quite
does what I want it to do, as every line in my file ends
with a single carriage return, including the lines within
the comments. There is thus no way for me to tell whether a
particular carriage return needs to be replaced without
going to the next line to see whether it starts with a '"'
or not.
What I need to do is to find the beginning of each sentence
that does NOT start with a '"', and then to delete the
carriage return at the end of the previous line, replacing
it with a space. This will hopefully leave my proper
paragraph endings, but remove those in the middle of the
comments.
Thanks in advance for any further suggestions.
Graham

 

[Jonathan West]

Try this.

- Replace every paragraph mark with something else, maybe the string xyzxyz

- Replace every string xyzxyz" with a paragraph mark and "

- Replace every remaining string xyzxyz with a space

 

[Graham]

Now that's what I call clever! Perfect.
Thanks a lot.
Yours humbly,
Graham

 

[Guest]

You might want to try something like this

Dim IDField as String, FieldA as String, FieldB as String
Dim CommentText as String, NewComment as String, LastField as String
Dim I as Long
Open "FileName.CSV" for Input as #1
Open "NewFileName.CSV" for Output as #2
Do Until EOF(1)
Input #1,IDField, FieldA, FieldB, CommentText, LastField
NewComment = ""
For I = 1 to Len(CommentText)
If InStr(CommentText,Chr(13)) = 0 Then NewComment = _
NewComment & Mid(CommentText,I,1)
Next I
Write #2, IDField, FieldA, FieldB, NewComment, LastField
Loop
Close #2
Close #1

This will read each entry in the .CSV and write a new .CSV file with
carriage returns removed from the Comment field. I have not tested it, but
I think it will run.

-Brian