Fix a txt data file

[PA]

My company has decided to download selected customer information, monthly,
from one of our AS400 mainframes into a text file and then do a mail merge
with Word. There will be approximately 500 letters to be generated, each
month.
The fields to be used are:
"Fname","Lname","Address1","Address2","City","State","Zip"
The problem is, there is a line break between the address2 field and the
city field in the data, so that there is an extra line in the address. I
opened the text file in Word and see a paragraph mark, forcing city field to
the next line in the word file. The inside address will therefore not fit
properly in the window envelope. The field labels in the first row are on
one line, the problem begins in the data itself.
I hope this explanation is understandable, the cause is certainly not.
How can I correct this problem?

 

[Graham Mayor]

If you are trying to use the addressblock field, don't! Just place the
address fields as you require them.
You can conditionally insert Address2 if necessary to avoid empty lines when
the field has no data eg
{Mergefield Address1}{If {Mergefield Address2} <> "" "
{Mergefield Address2}
(Mergefield City} etc


--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[PA]

Thank you Graham, but I must not have explained completely.

I am not using the address block. I have the fields set up as you suggest,
but if I open the data file (txt file) in Word and turn on the non-printing
characters, there is a pilcrow after Address2, and the City, State and Zip
field are unnecessarily forced to the next row. This is occuring in each
record. I am thus getting an extra row whether Address2 is populated or is
null.
If I import the data into Excel, field headers are on row1, the two name
fields and address fields are on row 2 and city, state and zip are on row 3.
Subsequent records likewise take two rows.

 

[Peter Jamieson]

You could consider using WOrd find and replace to remove the additional
return, e.g. if you have

field1,field2,address,
city,state,zip
field1,field2,address,
city,state,zip
field1,field2,address,
city,state,zip

then you could find ",^p" and replace with "," (both expressions without
quotes)

Obviously if you are trying to automate then that would require an extrea
set of steps and there would probably be a better way to do it. But if you
are actually getting

field1,field2,address
city,state,zip

then things are a bit different. What is in the field that the AS400 people
are exporting from?

 

[Doug Robbins - Word MVP]

As an alternative to Peter's method

Dim i As Long
With ActiveDocument
For i = .Paragraphs.Count - 1 To 1 Step -2
.Paragraphs(i).Range.Characters(.Paragraphs(i).Range.Characters.Count).Delete
Next i
End With

or, if there is no comma at the end of the first row of data for each
record, use

Dim i As Long
With ActiveDocument
For i = .Paragraphs.Count - 1 To 1 Step -2
.Paragraphs(i + 1).Range.InsertBefore ","
.Paragraphs(i).Range.Characters(.Paragraphs(i).Range.Characters.Count).Delete
Next i
End With


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[PA]

Thank you all for your suggestions.
I will give it another attempt tomorrow when returning to the office.