Converting Word Address list to Access database

  • Thread starter Thread starter Don Schuman
  • Start date Start date
D

Don Schuman

I would like to convert a Word document in this format:
GAYLE xxMAN HELEN xxOX JACK xxDARD
106 N. HICKORY 307 N. LINCOLN AVE. C/O NEW SITE
MONETT, MO 65708 MONETT, MO 65708 MONETT, MO 65708 .

JULIE xxxNSON LEOLA xxT MARY xxAN
6081 6TH 420 SCHULZ 1100 E. CLEVELAND
MONETT, MO 65708 MONETT, MO 65708 MONETT, MO 65708

to an Access database like this:
Name Street Address City ST Zip
GAYLE xxMAN 106 N. HICKORY MONETT MO 65708
HELEN xx 307 N. LINCOLN AVE. MONETT MO 65708
JACK xxDARD C/O NEW SITE MONETT MO 65708
JULIE xxxNSON 6081 6TH MONETT MO 65708
LEOLA xxT 420 SCHULZ MONETT MO 65708
MARY xxAN 1100 E. CLEVELAND MONETT MO 65708

This is a list of several thousand names. Currently, I am copying them into Excel and 'Transpose Pasting' 3 at a time. And then copying them into Access. There has to be a better way.

Thank you for any and all suggestions.
 
Hi Don,

There is probably a more clever
solution (RAC?), but this is how
I would tackle this problem.

Hold down the <ALT> key
and select all of one column.
Copy to selection to clipboard
and paste into new doc.

Do the same for other 2 columns
trying to keep one blank line between
pastes.

In your new doc, select Edit\Replace.

Find What: ^p^p
Replace With: ^^

Then,

Find What: ^p
Replace With: ^t

Then,

Find What: ^^
Replace With: ^p

Double-check that now have
tab-delimited rows for each address.

Import into Access.

Please respond back if I have misunderstood.

Good luck,

Gary Walter
I would like to convert a Word document in this format:
GAYLE xxMAN HELEN xxOX JACK xxDARD
106 N. HICKORY 307 N. LINCOLN AVE. C/O NEW SITE
MONETT, MO 65708 MONETT, MO 65708 MONETT, MO 65708 .

JULIE xxxNSON LEOLA xxT MARY xxAN
6081 6TH 420 SCHULZ 1100 E. CLEVELAND
MONETT, MO 65708 MONETT, MO 65708 MONETT, MO 65708

to an Access database like this:
Name Street Address City ST Zip
GAYLE xxMAN 106 N. HICKORY MONETT MO 65708
HELEN xx 307 N. LINCOLN AVE. MONETT MO 65708
JACK xxDARD C/O NEW SITE MONETT MO 65708
JULIE xxxNSON 6081 6TH MONETT MO 65708
LEOLA xxT 420 SCHULZ MONETT MO 65708
MARY xxAN 1100 E. CLEVELAND MONETT MO 65708

This is a list of several thousand names. Currently, I am copying them into Excel and 'Transpose Pasting' 3 at a time. And then copying them into Access. There has to be a better way.

Thank you for any and all suggestions.
 
Some more thoughts....

It occurs to me that you might
end up with some TAB chars
after your paste process.

So first Find/Replace should be

Find What: ^t
Replace With: ""

And I forgot to mention that you
should save final doc as a TEXT
file so you can import into Access.
Hi Don,

There is probably a more clever
solution (RAC?), but this is how
I would tackle this problem.

Hold down the <ALT> key
and select all of one column.
Copy to selection to clipboard
and paste into new doc.

Do the same for other 2 columns
trying to keep one blank line between
pastes.

In your new doc, select Edit\Replace.

Find What: ^p^p
Replace With: ^^

Then,

Find What: ^p
Replace With: ^t

Then,

Find What: ^^
Replace With: ^p

Double-check that now have
tab-delimited rows for each address.

Import into Access.

Please respond back if I have misunderstood.

Good luck,

Gary Walter
I would like to convert a Word document in this format:
GAYLE xxMAN HELEN xxOX JACK xxDARD
106 N. HICKORY 307 N. LINCOLN AVE. C/O NEW SITE
MONETT, MO 65708 MONETT, MO 65708 MONETT, MO 65708 ..

JULIE xxxNSON LEOLA xxT MARY xxAN
6081 6TH 420 SCHULZ 1100 E. CLEVELAND
MONETT, MO 65708 MONETT, MO 65708 MONETT, MO 65708

to an Access database like this:
Name Street Address City ST Zip
GAYLE xxMAN 106 N. HICKORY MONETT MO 65708
HELEN xx 307 N. LINCOLN AVE. MONETT MO 65708
JACK xxDARD C/O NEW SITE MONETT MO 65708
JULIE xxxNSON 6081 6TH MONETT MO 65708
LEOLA xxT 420 SCHULZ MONETT MO 65708
MARY xxAN 1100 E. CLEVELAND MONETT MO 65708

This is a list of several thousand names. Currently, I am copying them into Excel and 'Transpose Pasting' 3 at a time. And then copying them into Access. There has to be a better way.

Thank you for any and all suggestions.
 
Gary, you are brilliant! Thank you so much for taking time to share that solution!
Some more thoughts....

It occurs to me that you might
end up with some TAB chars
after your paste process.

So first Find/Replace should be

Find What: ^t
Replace With: ""

And I forgot to mention that you
should save final doc as a TEXT
file so you can import into Access.
Hi Don,

There is probably a more clever
solution (RAC?), but this is how
I would tackle this problem.

Hold down the <ALT> key
and select all of one column.
Copy to selection to clipboard
and paste into new doc.

Do the same for other 2 columns
trying to keep one blank line between
pastes.

In your new doc, select Edit\Replace.

Find What: ^p^p
Replace With: ^^

Then,

Find What: ^p
Replace With: ^t

Then,

Find What: ^^
Replace With: ^p

Double-check that now have
tab-delimited rows for each address.

Import into Access.

Please respond back if I have misunderstood.

Good luck,

Gary Walter
I would like to convert a Word document in this format:
GAYLE xxMAN HELEN xxOX JACK xxDARD
106 N. HICKORY 307 N. LINCOLN AVE. C/O NEW SITE
MONETT, MO 65708 MONETT, MO 65708 MONETT, MO 65708 .

JULIE xxxNSON LEOLA xxT MARY xxAN
6081 6TH 420 SCHULZ 1100 E. CLEVELAND
MONETT, MO 65708 MONETT, MO 65708 MONETT, MO 65708

to an Access database like this:
Name Street Address City ST Zip
GAYLE xxMAN 106 N. HICKORY MONETT MO 65708
HELEN xx 307 N. LINCOLN AVE. MONETT MO 65708
JACK xxDARD C/O NEW SITE MONETT MO 65708
JULIE xxxNSON 6081 6TH MONETT MO 65708
LEOLA xxT 420 SCHULZ MONETT MO 65708
MARY xxAN 1100 E. CLEVELAND MONETT MO 65708

This is a list of several thousand names. Currently, I am copying them into Excel and 'Transpose Pasting' 3 at a time. And then copying them into Access. There has to be a better way.

Thank you for any and all suggestions.
 
You're welcome Don.

I worked for a university when
they standardized on MS Office.

One of my coworkers set up
a "tip-of-the-day" list server
where employees would send
in "sneaky tricks" they had learned
and he would post one of them daily.
I was blown away when someone sent in
a tip similar to what we did here.

So simple...so powerful!

From Word Help file:

Examples of special characters and document elements you can find and replace

You can search for and replace the following special characters and document elements by typing codes for the items in the Find what and Replace with boxes in the Find or Replace dialog box.

Tip Here's a quick way to enter many of these items in the Find what and Replace with boxes: If you don't see the Special button, click More; then click Special and click the item you want.

To specify Type In
Paragraph mark () ^p Find what or Replace with box
Tab character () ^t Find what or Replace with box
Comment mark ^a Find what box
ANSI or ASCII characters ^0nnn, where nnn is the character code Find what or Replace with box
Any character ^? Find what box
Any digit ^# Find what box
Any letter ^$ Find what box
Caret character ^^ Find what or Replace with box
Clipboard contents ^c Replace with box
Contents of the Find what box ^& Replace with box
Endnote mark ^e Find what box
Field ^d Find what box
Footnote mark ^f Find what box
Graphic ^g Find what box

Breaks

To specify Type In
Column break() ^n Find what or Replace with box
Manual line break () ^l Find what or Replace with box
Manual page break() ^m Find what or Replace with box
Section break() ^b Find what box

Hyphens and spaces

To specify Type In
Em dash ( ¾ ) ^+ Find what or Replace with box
En dash ( - ) ^= Find what or Replace with box
Nonbreaking space () ^s Find what or Replace with box
Nonbreaking hyphen () ^~ Find what or Replace with box
Optional hyphen () ^- Find what or Replace with box
White space ^w Find what box

Notes

·If you omit the optional hyphen code, Word finds all matching text, including text with optional hyphens. If you include the optional hyphen code, Word finds only text with optional hyphens in the position you specified.

·White space includes any number and combination of regular and nonbreaking spaces, tab characters, and paragraph marks.
 
Back
Top