Mail Merge Text file help

[Dig]

I was given a text file to use for labels. Using Word XP I started the mail
wizard and went step by step, when I get to the point of chosing the file
with the addresses in it Word locks up. To test the file I tried to open the
same file in Excel and Access and they both locked up.

Looking inthe file I noticed that the fields seem to be correctly formatted
with open and close quotes and then commas between them. The one thing in
this file is that there are no line breaks, so the first row of fields are
field names and when you get to the close quote of the last field name there
is a comma and the data fields start. This continues all the way thru the
file.

So I am guessing that it appears to the program as one long contiuous stream
of data instead of line after line of data.

1.) Does that sound like the problem?

2.) Does anyone know a program that exports csv files in this format? If
this is a normal way to export for a particular program maybe I can tell the
individual to re-export it in a different format.

3.) Does anyone know of a way that this file can be easily corrected without
me having to go line by line trying to find the end of each line and
deleting the comma and inserting a line break?

Thanks

 

[Marek Williams]

3.) Does anyone know of a way that this file can be easily corrected without
me having to go line by line trying to find the end of each line and
deleting the comma and inserting a line break?

From what you said my understanding is that there is a comma
separating each field. But instead of a line break at the end of each
record there is just another comma. As a result all your applications
are crashing because they think there are hundreds of fields, and they
can't handle that many fields.

To put a line break at the end of each record, record a macro that
searches for a comma for a number of times equal to the number of
fields, and when it gets to the final comma, replaces it with a
paragraph mark (line break). If you want to get fancy you could open
the macro code and make it into a for-next loop that would go through
the whole document until it gets to the end. If there are thousands of
records it may be worth it. If there are a shorter number, you can
assign a keyboard shortcut to the macro. Just hit the keyboard
shortcut over and over until you get to the end of the records.

To import the text into Access it has to be plain text. Save from Word
as ASCII text and Access will pick right up on the number of fields,
etc. If you put the names you want for the fields into the first
record, Access will even use them to name the fields. I think it works
the same with Excel, but I never use Excel.

In the future, I recommend using something other than a comma for the
record delimiter. Some fields contain commas in the data (e.g., street
addresses), so you have to enclose the data in the field into quotes
(quotes as a field delimiter). If you use a different character for
the field delimiter (I use an asterisk), you can probably dispense
with the commas. In all cases, the record delimiter should be the line
break.

I hope that helps.

 

[Doug Robbins - Word MVP]

Hi Dig,

Open the data file in Word, use Edit>Replace to replace all of the " with
nothing so that the field names and the data a merely separated by a comma,
then select all of the text and from the Tables menu, select Convert Text to
Table. Specify the separator as a comma, then change the number of columns
that appears in the dialog so that it equals the number of fields. This
should convert the text into a Table with the field names in the first row.
Save that document and use it as the datasource.

Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.

Hope this helps
Doug Robbins - Word MVP

 

[Suzanne S. Barnhill]

Dig posted a duplicate message in another NG where this problem was solved
several days ago. Word is quite capable of converting the comma-separated
text to a table if you just tell it how many columns to use.

--
Suzanne S. Barnhill
Microsoft MVP (Word)
Words into Type
Fairhope, Alabama USA
Word MVP FAQ site: http://www.mvps.org/word
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.