Import Csv Into Multiple Related Tables, Access 2013
Jan 11 2017, 03:21 PM
Joined: 11-January 17
Hi all, If this has a good answer somewhere else, please point me to it. I have a csv file that is imported into my db frequently.
The csv is generated from an online registration portal.
It can be anything from daily to weekly.
It is imported into a temporary table called tblTemp_Import.
From here the data needs to go to several related tables. tblContacts tblPhone and tblEvent. I have attached a screenshot showing the layout of the 4 tables. As you can see, the Primary Key [Customer ID] from tblContacts is the foreign key used in the other two related tables. You will also see that new data can be new customers or previous customers adding a new event. I have created the queries that will give me the new data in the tblContacts without creating duplicates. For this I used an Unmatched Query and an Append Query to get the new contacts into tblContacts Now the part I'm stuck on. How do I get the rest of the data from tblTemp_Import to the proper related tables and maintain relational integrity? (i.e., copy the PK from tblContacts.[Customer ID] to the related field in the tblPhone and tblEvent tables?
I am not too far in, I am pretty flexible as to how this is done, so if my import method needs to change, it's not to late.
Screenshot__9_.png ( 303.96K )Number of downloads: 15
Jan 11 2017, 03:26 PM
Joined: 11-January 17
I almost forgot. Not all Names (First + Last) are unique. SO there can be two Joe Smith. I am handling this in the first set of queries where I look for duplicates and get input from the user whether to keep or not.
Jan 16 2017, 01:57 PM
Joined: 11-January 17
Ok, I figured something out. Let me know what you think. There is one more field on the *_Imports table and the tblContacts table. Both have a [Customer Name] Field that is calculated from First and Last Name.
I set up several Queries to manipulate the data then used a form button to run all the querries.
Step1 - Find unmatched entries in the tblContacts, that way I am not duplicating contacts. So, qryUnmatched:
SELECT tblTemp_Import.[Customer Name], tblTemp_Import.[First Name], tblTemp_Import.[Last Name], tblTemp_Import.Address, tblTemp_Import.City, tblTemp_Import.[State/Province], tblTemp_Import.[Zip/Postal Code]
FROM tblTemp LEFT JOIN tblContacts ON (tblTemp_Import.[Customer Name] = tblContacts.[Customer Name]) AND (tblContacts.[Address] = tblTemp_Import.[Address])
WHERE (((tblContacts.[Customer Name]) Is Null));
Step2 - Append the data to the tblContacts. qry Append_Contacts
INSERT INTO tblContacts ( [First Name], [Last Name], [Address], City, State, Zip,)
SELECT [First Name], [Last Name], Address, City, [State/Province], [Zip/Postal Code]
Step3 - Update the [Customer IDFK] field so the Primary key in tblCustomer is now the foreign Key in the tblTemp_Import. I did add this field to tblTemp_Import, it is not shown in the original info I posted. qryUpdate_IDFK
UPDATE tblTemp_Import INNER JOIN tblContacts ON [tblContacts].[Customer Name] = [tblTemp].[Customer Name] SET tblTemp.[Customer IDFK]= [tblContacts].[Customer ID];
Step4 - Update tblPhone to add the new phone data to the correct table and keep referential integrity. One change here. In the actual tblTemp_Import I am using, the phone # comes in a field called [Mobile Phone] , but there is the option to have home or business phone to each contact in the table. Therefore I still need to identify the type of phone. qryUpdate_Phone
UPDATE tblPhone RIGHT JOIN tblTemp_Import ON (tblPhone.[Customer IDFK] = tblTemp_Import.[Customer IDFK]) AND (tblPhone.[Phone Number] = tblTemp_Import.[Mobile Phone]) SET tblPhone.[Phone Number] = [tblTemp_Import].[Mobile Phone], tblPhone.Email = [tblTemp_Import].[Email], tblPhone.[Customer IDFK] = [tblTemp_Import].[Customer IDFK], tblPhone.[Phone Type] = "Mobile";
Next I update the tblEvent with the remaining data. qryUpdate_Event
UPDATE tblEvent RIGHT JOIN tblTemp_Import ON ( tblEvent.[Event] = tblTemp_Import.[Event]) AND ( tblEvent.[Customer IDFK] = tblTemp_Import.[Customer IDFK]) SET tblEvent.Event = tblTemp_Import.Event, tblEvent.[# Adults] = tblTemp_Import.Adult, tblEvent.[# Child]= tblTemp_Import.Child, tblEvent.[Customer IDFK] = tblTemp_Import.[Customer ID]
To run this , I have a form with a button that runs this code:
Some codebefore this
Set dbs = CurrentDb
' Execute runs saved queries
dbs.Execute "qry Append_Contacts", dbFailOnError
dbs.Execute "qryUpdate_IDFK", dbFailOnError
dbs.Execute "qryUpdate_Phone", dbFailOnError
dbs.Execute "qryUpdate_Event", dbFailOnError
some more code after this
This is the general concept, I did add a couple other things to the button, but this handles the data as I needed.
|Search Top Lo-Fi||19th January 2017 - 02:36 PM|