Hugh, many times I have had to do a preliminary edit with Word to clean up the
data so it could be imported into either Excel or Access. Both of these have
similar wizards to assist in importing data, and, sometimes it is just easier
to straighten things up before trying to import them. Two cases, that
immediately come to mind occured at work within the last month. The first was
48 different Word Perfect 4.1 files of Names and addresses that were manually
formatted to to print lables. These files had as few as 3 lines and as many as
8 lines of addresses. Sometimes the Zip code was on the same line as the city
sometimes and often it was not. These all needed to be put into the same
format and ZIP code sorted for a large mailing by an outside vendor in either
an Access database, or preferably an Excel Spreadsheet.
They looked something like this
Mr. John Smith Esq.
%Smith Smith and Smith
123 N Capitol Dr, Suite 1245
Sacramento CA
98887-2334
Mr. Sam Smith Esq.
%Smith Smith and Smith
123 N Capitol Dr, Suite 1245
Sacramento CA
98887-2334
You get the idea.
* 1st, I concatenated them into 1 file. I read each of them into Word and
then cut and pasted each of the individual files into 1 large file. You would
not need to do this since you already have 1 file.
* 2nd, I used the find and replace all to find all pairs of paragraph marks
"^p^p" and replace them with a single tilde (that Squiggly thing over the tab
key "~", Next I replaced each pair of tildes "~~" with just 1 tilde. What
this does is to separate each complete address with ~~
* 3rd, I used the find and replace all to find all single paragraph marks
"^p" and replaced them with a tab "^t". This will make it easy to separate
the data into columns when I import it with a wizard.
* 4th, I used the find and replace all to find all the tildes "~" and replace
each with a paragraph mark "^p".
* 5th (and the last Word step), I did a file save as a Templist.txt file for
use in Access.
* I then opened a "new" Access database, and used the file, get external data,
import menus to cause the Access Wizard to look for the new file
"Templist.txt". I'm not going run thru all the steps I did to rearrange the
data here,
but I did use a query to find all the Zips and Sort them into one column
(without writing ANY code). If you want, I will E-mail the code in the access
file I made if you Email me a direct request at [log in to unmask]
* Finally I used Access's Tools, Office Links, Analyse it with Excel to
output the Mailing list in the Zip code order
format the printer wanted. The whole thing took about 3 hrs on a Pentium
166mhz machine for a total of about 15000 addresses. I even used Access's
wizard to eliminate about 2800 duplicates and at a mailing cost of about 57
cents for each brochure and a printing cost of 1.15 that saved us about
$4800. Quite a savings for just 3 hours work.
Jim Meagher wrote:
> Hugh,
>
> Open Excel and then click FILE OPEN and simply open the text file.
> Depending on how the text is formatted (commas, fixed length, etc)
> Excel will either import it correctly or will open a wizard to help you
> identify which data belongs in what column.
>
> Jim Meagher
> =====
> Micro Solutions Consulting Member of The HTML Writers Guild
> http://www.ezy.net/~microsol International Webmasters Association
> 410-543-8996 MS Site Builder Network - Level 2 member
> =====
> ----- Original Message -----
> From: <[log in to unmask]>
>
> > I received a text file that is 1600 names and addresses. I am using
> > netscape 4. I want to send these files to either excel 97 or access 97.
> > I did a copy and paste in excel but I cannot get them separated out by
> > member, address, city, state, zip, phone, and fax. How do I send this
> > file to either excel or access then set them up so I can use them to
> > either send fax or make mailing labels?
>
> The PCSOFT web site always needs good submissions. If
> you would like to contribute to the website, send any
> software tech tips or software reviews to:
> [log in to unmask]
The PCSOFT web site always needs good submissions. If
you would like to contribute to the website, send any
software tech tips or software reviews to:
[log in to unmask]
|
