Import Excel table to 2 Access tables

[Dana809904]

Hello,
I am importing many records off of Excel worksheets and need to break them
up in the resulting Access table. Right now I am taking an Individual's data
and importing it all and then manually copying and pasting a Organization
name located in another table through a comboBox which links it with a
foreign key.
I am wondering if it is possible to write a script or something that takes
the Organization column from excel and adds the new organization (if
neccessary) to the Organization table and links it to the appropriate
Individual record with the OrgID_FK automatically.
I am pretty sure this is easily possible but I do not have the skills to
exectute it (YET!) I really appreciate if anybody can guide me on this or at
least point me in the right direction.
Thanks!
Dana S.

 

[Nikos Yannacopoulos]

Dana,

There's certainly some way to automate this process, but in order to get
specific help, you should post some details on:

* Your target tables: names, fields in each, relationship between the two
(field they are joined on)
* The spreadsheet: columns, and which fiield in each table should each
column hit.

Nikos

 

[Dana809904]

Thanks for the reply Nikos,
Basically what I have is a contact management system with some extra fields
for special needs of my client. The two tables I am dealing with now are:
Individual:
IndID (autonumber primary key)
OrgID_FK (foreign key to Organization autonumber primary key)
IdentificationNum (would be PK but not filled in in many records)
LastName
FirstName
Title
Nickname
(Several fields for contact information which should also be broken
into separate tables)

Organization:
OrgID (autonumber primary key)
OrganizationName
DepartmentName
SICCode
(Same contact information fields which should also be moved to
separate tables.)

Spreadsheet info:
One hurdle I am dealing with is that I am getting all my data from many
different spreadsheets scattered with data from years of mis-management. The
overall format looks pretty much like this:

IdentificationNum (which is often blank!)
Organization
LastName
FirstName
Title
Address
City
ST
Zip
WorkPhone
HomePhone
FaxNumber
Email
Web

Now there is some variation in columns but this is the overall format.
So by now I hope you see my need to break Organization into a separate table
as in many cases more than one person works for a particular Organization
and/or Department. In similar fashion I would also benefit separating all
contact info from the main tables and relating them with keys as well,
however I was having trouble when I started but I should be able to handle
that by now.

I really appreciate anybody that would like to tackle this with some advice
as it will take me way longer than I want to sit there and link every
Individual with an Organization, the same would go for Address and Contact
info if I was to break that up as well.
Thanks in advance!
Dana Simmelink

 

[Nikos Yannacopoulos]

Hi Dana,

The way I see it, you need to resort to VB code to do this, through
recordset operations. Assuming the spreadsheet is a linkled table in Ascess
(or imported to an intermediate table), the code would:

* Open the linked table, the Organizations table and the Individuals table
as recordsets (say rst1, rst2 and rst3 respectively)
* Loop though the records of rst1, and:
1. Search rst2 for the Organization (based on name); if it exists, note
the OrgID; if it doesn't, create a new record for it and note the OrgID
2. Create a new record with the Individual details in rst3; the OrgID is
known from the previous step
* Once all the records in rst1 eare imported, close and reset all three
recordsets.

If you have some experience with VB and recordsets this should be pretty
straightforward, if not it probably seems scary (but it isn't).
One thing you need to be cautious of is the changes in the worksheet format;
if the column headers don't chnage though the culumn succession does, you
can use absolute field referencing (by field name) in the linkled table
recordset; if, on the contrary, the names change but the sequence doesn't,
you can use index referencing (1st, 2nd etc) and, again, do the job. If they
both change, though (and chances are this is the case!), you have a problem
there, which will require you to manually modify the spreadsheets so as to
maintain a fixed column sequence or header names.

Are you willing to give it a shot?

Nikos

 

[Dana809904]

Wow,
Thanks for doing the hard thinking for me. I totally see your
methodology to get this job done here, but it does look like a bit of work,
or in the least thinking! I do have some experience with VB, however I am a
pretty rusty programmer, inconsistant is probably the better word. It is not
until recently that I have dedicated myself much more to focusing my mind to
the task at hand. (Waiting till I graduate from college to do this is a heck
of a time! )
Anyways, I would like to take a crack at this as it would be great for my
general databasing skills as well. I'm going to try and get to it today and
we'll see how it goes. If you have any good reference materials available
online feel free to send it over.
Thanks for your assistance,
Dana

 

[Nikos Yannacopoulos]

Dana,

I'm afraid I have no material to recommend, I developed my DAO (whatever...)
skills starting with the examples in Access help, and moving on with trial
and error. I'm sure you'll be able to get valuable help in the modulesdaovba
group, though, once you get started - and I'll be happy to assist myself if
I can.

Good luck,
Nikos