Importing data across many-to-many related tables (1 Viewer)

T

TMBIT

New member
Local time
Yesterday, 23:23
Joined
May 20, 2019
Messages
5
Lost this post first time around, don't have it in me to fully recreate the context. Basically, I'm building an Access solution for a medium sized org, have to clean and import 15 years of dirty data from many, many, discrete Excel sheets.

In the current Excel "solution" each row has contact information for Contacts, with columns for email(s), phone(s), address(es), etc. Sometimes the user entered multiple emails/phones/etc into the same relevant column for a Contact, sometimes they added another row for the same Contact.

I've structured the Access database so that the contacts table is related many-to-many with emails, phones, addresses, etc. tables.
My question: is it at all possible to import data from Excel into Access and specify which column from Excel goes to which attribute in which table in Access, all while maintaining referential integrity?
If not, what, if any, are the best practices for importing/cleaning data into many-to-many related tables?
 
C

CJ_London

Super Moderator
Staff member
Local time
Today, 07:23
Joined
Feb 19, 2013
Messages
16,610
is it at all possible to import data from Excel into Access and specify which column from Excel goes to which attribute in which table in Access, all while maintaining referential integrity?
Click to expand...
You do so with a number of queries. You can either link to the spreadsheet or import to a temporary table, then use sql to populate your tables in order of seniority - grandparent>parent>child.

rather than importing or linking you can use sql directly.

If you have many spreadsheets, it is better to get them all imported to a single temporary table first and use queries to remove duplicates, identify inconsistent records etc before populating your tables.
 
The_Doc_Man

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 01:23
Joined
Feb 28, 2001
Messages
27,148
FYI, TMBIT - you appear to have double-posted, and it happens when there is some network issue. I've done it a couple of times myself. You can delete the second post that has no responses.
 
T

TMBIT

New member
Local time
Yesterday, 23:23
Joined
May 20, 2019
Messages
5
How embarrassing. I'll go sit in the shame corner now. :banghead:
 
T

TMBIT

New member
Local time
Yesterday, 23:23
Joined
May 20, 2019
Messages
5
Re-posting the full version here for posterity.

TMBIT said:
Hey all. I'm developing an Access solution for my organization's database needs. Long story short, I need to clean up a large digital mess (many, many discrete excel sheets) left by the organization's staff over the past 15 years.

My current roadblock is getting our legacy data into Access. For years, contact information has been stored in excel sheets and each row contains names, address(es), email(s), phone number(s), etc. I have structured my Access database well (hopefully) and have separate tables for addresses, emails, phones, etc. (rather than attributes within the contacts table). However, I can't link the contacts table to these various tables in one-to-many relationships directly because each contact can have betwee zero-to-many phone numbers, email addresses, etc. (non-negotiable rule from the powers that be). So I have them in many-many relationships via one-many's and associate tables.

My question, and I'm afraid I already know the answer: is it possible to import data from excel into access AND, automatically within Access import wizard, import parse the excel rows based on column into specified fields across multiple tables in Access, while maintaining referential integrity?
If not, what are the best-practices for importing data across many-many relationships?
Click to expand...
 
C

CJ_London

Super Moderator
Staff member
Local time
Today, 07:23
Joined
Feb 19, 2013
Messages
16,610
think the issue is the OP's posts are being moderated
 
Uncle Gizmo

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 07:23
Joined
Jul 9, 2003
Messages
16,274
With regard to this part of your question:-

TMBIT said:
In the current Excel "solution" each row has contact information for Contacts, with columns for email(s), phone(s), address(es), etc. Sometimes the user entered multiple emails/phones/etc into the same relevant column for a Contact, sometimes they added another row for the same Contact.
Click to expand...

I created a "Normalization Tool" for importing column data like you describe above, and placing it vertically in a table the way it should be structured in MS Access. However it shifts the data into a new table.

Seeing as you want the data put into an existing table, and you also want to maintain referential integrity, then all I can suggest give it a try and then see if you can shift the data from the new tables created by this tool into your tables.

There's more information about the problems of importing Excel data into Access in my blog here:- Excel In Access
 
Uncle Gizmo

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 07:23
Joined
Jul 9, 2003
Messages
16,274
The videos in my blog here:- Parts 1, 2 and 3 as Video Instructions actually describes and demonstrates the process of moving telephone numbers from fields like "Home", "Work", "Mobile", "Skype", "Fax" in to a correctly structured table...
 
P

Pat Hartman

Super Moderator
Staff member
Local time
Today, 02:23
Joined
Feb 19, 2002
Messages
43,233
To summarize,
If the spreadsheet has data that needs to be imported into different tables, you will need a separate append query for each target table.

I presume this is a one time event so once you're done, you're done. Given that, I generally create a procedure that runs a set of queries one after the other so when I've got everything all tested, I can just push a button and do it one final time for production.

I start with the highest level data. I import the spreadsheet and add a new column for the generated Autonumber. I create a select query that groups to the level I want to import. So it would select company name, company number (if there is one), address, and whatever else belongs to the company and occurs only once. Then I change the select query to an append. Run the append query and append the company data to the company table. Then I create a query that joins the new company table with the raw import data and update the "ID" field on the raw table with the autonumber from the company table. That simplifies the next step where I do the same thing for contacts. This time, I group by "ID" and the contact name and phone number and append that data to the contact table.
 
You must log in or register to reply here.

Similar threads

R
Solved Importing and calculating the data
Replies
12
Views
494
Robrobby
R
C
exporting access table data to excel
Replies
10
Views
398
raziel3
raziel3
M
Importing data from excel with file name
Replies
15
Views
784
Gasman
Gasman
S
Solved Importing from Excel into a table
Replies
7
Views
248
SwampyNZ
S
W
Solved Appending data from Excel
Replies
3
Views
301
WorkDatabase
W

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom