I suggest you use a text editor or word processor first, then import the
results (see below for details).
I have several rather extensive text files (extracted from an antiquated
local database) that I wish to import into either Access or Excel as
functional databases.
So far I've had the best results importing into Excel, as this at least
manages to divide the info into several fields somewhat, although still not
nearly as well as I'd like.
Several elements get crammed together in one cell when I choose to import
seperately.
In Access, the results are even worse and I can't even manage to make decent
cells out of the info. Most of the time the info gets imported into one
column, or into a zillion ones if I choose space as a seperator symbol, which
I truly don't want of course.
I've tried tinkering about with both applications a bit, but can't seem to
generate moderately desireable results.
I'm gonna try now to improve the textual layout of the source text in Word,
see if that can improve the import performance slightly, but apart from that
I wonder what I can do to import the text into either Excel or Access into
proper, functional cells.
Please give me some feedback. Any help or ideas would be appreciated.
This is exactly what I would suggest. When I have to play with files
like yours, I often convert the file to text (*.TXT type) and use a
favorite text editor to line them up in appropriate columns. You can do
something similar in MS Word, lining them up using tab characters in
ordinary paragraphs (instead of using a Word table).
(The examples in the following come from a message I sent someone else
recently, but I can't give you examples with your data without knowing
what they are.)
I would proceed this way: First of all, paste all of your data into a
new Word document. You can use Word to delete lines that contain only
white space, and you can delete repeated paragraph marks. Also, make
paragraph marks and tabs visible. I would define a special (for this
purpose) paragraph style that would include the tab settings for this
operation, but would not save it in the global Word template, as you'll
likely never need this style again, so leave "Add to Template"
unchecked, but check "Automatically Update" (so that changes you make to
tab settings will apply to all the paragraphs).
You can type field headings separated by tabs, such as
Name<tab>Next of Kin: Notify:<tab>Relation
<tab>Notes<tab>Address<tab>City
<tab>Telephone
(I had to fold this long line to fit this message, but I'm suggesting
that you put it all into one long line in Word.)
Then, in each line, you can insert the tab characters in the appropriate
spots, such as
Mary Smith<tab>Mr. & Mrs. John M. Smith<tab> (Parents)
<tab>(live with Son,Gerard)
Mary Smith<tab>Mr.Gerard H. Smith<tab>(brother)
<tab><tab>1234 Brookside BLVD.<tab>Kansas City, MO 12345
<tab>Tel: 1-816-123-4567
Check to be sure that all the addresses are in the Address column, etc.
If you expect that you might later want to separate first names from
last names, do it now, setting up separate columns for both fields. It's
probably easier to take care of it in Word than to have to split the
field later in Access.
This will work best if you can squeeze everything in one record onto one
line. (If not, you could use 2 lines, but you'd need to add some kind
of key value to each line that you could later use to link the records.)
You can use teeny tiny 8-point Arial Narrow type and a wide (22 inches,
or whatever the maximum is for Word) page to let you cram lots of stuff
into a line. It won't matter that you can't read it when you fit the
page to your screen; you can set the View --> Zoom value to 150% or
whatever makes sense, to make it easier to read.
If some value is too long to fit in the space you've allotted, you can
change the tab settings in all the paragraphs so you can line them up.
Since, in your example, several records apply to "Mary Smith", I suggest
that you copy that name into the first field of each line, so that you
can later keep the records together. (That's the easiest way I can
think of, but you might prefer some other technique, such as assigning a
key value to each record.) Later, in Access, you'd probably use an
Autonumber field to do this, but I think that that's not practical here.
And if you have two Mary Smiths, I suggest you call one of them "Mary
Smith_2", until after you've finished copying everything. You can
straighten that out later, in Access.
Having reformatted all the lines so that the names (for example) are all
in the same tab-delimited column, save the file first as a Word document
(which you'll need if something goes wrong), then rename it and save it
as Text Only With Line Breaks (for Access to read).
In Access, use File --> Get External Data --> Import... to read your
text file. Tell Access that it's tab delimited, and that the first
record contains the field names.
If the lines are too long for Access to import (I'm not sure what the
limit is; it might be 255 characters), you'll need to do this in stages.
Since you saved the Word document file, load and rename it and delete
some of the fields (but keeping the primary name and the next-of-kin
name), to shorten the lines, then save it as text and import it into
Access. Then do the same thing again but delete other fields, and
import those into another Access Table. (But I hope that won't be
necessary.)
None of this should involve any retyping. Even copying the primary Name
you can do via copy-and-paste operations in Word.
As you do all this, please be sure to make frequent backups, especially
just before you do any global search-and-destroy operation. (Those are
sometimes difficult to undo, and you can always erase your temporary
backup files later.)
-- Vincent Johns <
[email protected]>
Please feel free to quote anything I say here.
