Importing Help: Importing from CSV, need to populate tables

[Eric Tubbs]

Greetings everyone,

I am a newbie to databases and Access 2002 and would like to ask for your
help in figuring out how to get imported data into the tables that have
already been created by me. Here are the followings items that have been
done so far:

1) tables have already been designed to meet the proposed standards for the
database

2) relationships and referential integrity has been established between the
tables

3) the import file is a CSV that currently contains 70 columns of data for
each record

4) using the text import wizard and created a import specification that will
take care of the import process.
a) field names have been renamed to meet the standards of the proposed
naming convention
b) data types have been correctly selected
c) import specification has been saved for future use

5) imported data get imported into a table called tblRosterImport

This is what I would like to find out:

1) how can I get the data from the table tblRosterImport into the other 15
tables that have been currently designed:
a) while preserving/maintaining primary keys and foreign keys
b) the 70 columns of data directly corresponds for each person; there can be
one record to an unlimited number of records in the import file

2) once the initial data has been properly imported into the database and
placed into the correct tables
a) how would you perform an update to the information
b) lets say we have another import file that contains updated information
for a previous person, how would you effecitively handle this information to
make sure the update handled it correctly

3) once the initial data has been imported and updates are handled correctly
a) what would be some ways to easily archive imported information
b) while keeping track of all of the updates for a particular person

If I need to provide you with more information, please don't hestitate to
ask me for it. Or, if I didnt not clearly define what needs to done, I will
gladly furnish you the requested information as best as I can.

Are there certain guidelines one should follow with thie particular (or not
so particular) situation? I have looked high and low for more information on
importing issues but have come up with a blank.

I do not mind getting my hands dirty and doing the work. Learning how to
design databases is becoming more fun each day as I have been able to find
solutions to my problems by reading books, experimentation and online
resources.

The decision to learn VBA has also sparked my interest because examing the
numerous database examples is quite amaxing.

Any help you can provide me in finding a workable solution(s) would be
greatly appreciated. I look forward in hearing from you soon.

Thanks,

Eric

P.S. Would it be ok to post my current database design, which is outlined in
an Excel file, to the list for you to possibly review? The database design
should be almost complete by the end of the week.

 

[Ken Snell]

First, please don't post any attachments to messages in this newsgroup. Many
people who read these posts must pay by the minute for connect and download
time.

Second, your questions are very general and comprehensive. The first step is
to get the data into a table so that you can work with it; you've noted how
you plan to do this.

The rest of what you seek to do is dictated by the structure of the tables
and the data themselves. We can't provide specific info for that because we
don't know the structure nor the type of data.

However, in general, the process that you would use to "load" up a database
with data is this:
(1) Get the raw data into tables so that you can easily access them.
(2) Use append queries to copy the "lookup" and "basic" data into the
lookup tables. If the data that you imported has primary key or other
identifiers in the records, then be sure to put those values into the
primary key fields that you have established in the permanent tables.
(3) Use append queries to copy the data into the parent table(s). You
need parent records in order to put records into child tables (tables where
the records link back to a parent record). If the data that you imported has
primary key or other identifiers in the records, then be sure to put those
values into the primary key fields that you have established in the
permanent tables.
(4) Use append queries to copy the data into the child tables. If the
data that you imported has primary key or other identifiers in the records,
then be sure to put those values into the primary key fields that you have
established in the permanent tables.

With respect to how you would update with new data, again that is completely
dependent upon how you've structured your tables and data. But you would
follow a process similar to that outlined above, except that you might use
update queries in place of append queries if you're wanting to change
existing data.

One great word of advice/caution: make a backup of the database after every
data manipulation that you do. That way, if something messes up during an
operation, you can go back to where you were just before that manipulation
and try something else. Please note that moving data into tables is one that
is fraught with possible errors and unexpected results.