Removing carriage returns from a CSV file.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Graham - 28 Jan 2004 10:36 GMT

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

Reply to this Message

Jonathan West - 28 Jan 2004 15:53 GMT

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.

Signature

Regards
Jonathan West - Word MVP
http://www.multilinker.com
Please reply to the newsgroup

> Hi,
> I'm afraid I am a complete VBA novice, so apologies if this
[quoted text clipped - 24 lines]
> Thanks,
> Graham

Reply to this Message

Graham - 28 Jan 2004 17:44 GMT

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

>-----Original Message-----
>Hi Graham,
[quoted text clipped - 41 lines]
>
>.

Reply to this Message

Jonathan West - 28 Jan 2004 17:56 GMT

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

Signature

Regards
Jonathan West - Word MVP
http://www.multilinker.com
Please reply to the newsgroup

> 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
[quoted text clipped - 67 lines]
> >
> >.

Reply to this Message

Graham - 29 Jan 2004 16:55 GMT

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

>-----Original Message-----
>Try this.
[quoted text clipped - 79 lines]
>
>.

Reply to this Message

JGM - 28 Jan 2004 15:57 GMT

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!

--
_______________________________________
Jean-Guy Marcil
jmarcil@sympatico.ca

> Hi,
> I'm afraid I am a complete VBA novice, so apologies if this
[quoted text clipped - 24 lines]
> Thanks,
> Graham

Reply to this Message

- 31 Jan 2004 14:48 GMT

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

> Hi,
> I'm afraid I am a complete VBA novice, so apologies if this
[quoted text clipped - 24 lines]
> Thanks,
> Graham

Reply to this Message