Fix a txt data file

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

PA - 23 Feb 2008 14:21 GMT

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?

Reply to this Message

Graham Mayor - 23 Feb 2008 15:11 GMT

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

Signature

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

My web site www.gmayor.com
Word MVP web site http://word.mvps.org
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

> My company has decided to download selected customer information,
> monthly, from one of our AS400 mainframes into a text file and then
[quoted text clipped - 11 lines]
> I hope this explanation is understandable, the cause is certainly not.
> How can I correct this problem?

Reply to this Message

PA - 23 Feb 2008 17:11 GMT

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.

> If you are trying to use the addressblock field, don't! Just place the
> address fields as you require them.
[quoted text clipped - 19 lines]
> > I hope this explanation is understandable, the cause is certainly not.
> > How can I correct this problem?

Reply to this Message

Peter Jamieson - 23 Feb 2008 18:47 GMT

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?

Signature

Peter Jamieson
http://tips.pjmsn.me.uk

> Thank you Graham, but I must not have explained completely.
>
[quoted text clipped - 37 lines]
>> > I hope this explanation is understandable, the cause is certainly not.
>> > How can I correct this problem?

Reply to this Message

Doug Robbins - Word MVP - 23 Feb 2008 22:52 GMT

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

Signature

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

Reply to this Message

PA - 24 Feb 2008 13:24 GMT

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

> 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
[quoted text clipped - 10 lines]
> I hope this explanation is understandable, the cause is certainly not.
> How can I correct this problem?

Reply to this Message