Importing data to tables using IDs

[marvo]

Hi
I have a .csv file to import - it's correctly formatted and contains all the data VALUES I need.
However, like a good boy, I have designed my access tables IDs - primary/foreign keys to other tables.

So for example, I have a database that manages sports fixtures
I want to import a csv of all the new fixtures for this season
My csv contains Values like:

Date, Home Team, Away Team, Time, Pitch
23/09/2019, Cambridge 1st XI, Oxford 1st XI, 13:00, The Sportsground

but my Access Table has
Date
HomeTeamID (which then looks up another 'Teams' table based on the ID)
OppoID (which then looks up another 'Clubs' table based on the ID)
Time
PitchID (which then looks up another 'Pitches' table based on the ID)

So in short...I can't import my data, because the csv does not have these IDs


Anyway to solve this?
Thanks

 

[theDBguy]

Hi. You would either fix the CSV or fix the data after you import them into your tables. If you have Referential Integrity enforced, you won't be able to assign a Foreign Key that doesn't exists. However, you should be able to leave the field blank. If so, you should be able to fix it with a query to enter the correct ID.

 

[pbaldy]

First either link to the csv or import it into a temp table. Then create an append query that joins that data to the teams and pitches tables, enabling you to return the ID fields instead of the names.

 

[Uncle Gizmo]

I have a Blog demonstrating how to import Excel data into MS Access. Once the data is imported the next step is to extract any repeating text values and put them in a lookup table. Next you need to replace the entry in the table where the text formerly appeared with the unique number from the lookup table.

I reckon that's the same problem you've got, (or you should have if your database is constructed properly) The text you're talking about should be stored in a lookup table and wherever the the Home Team name, or the Away Team name, or the Pitch name are displayed then you shouldn't be be displaying actual text from a field, but you should be looking up the text via the reference number (the Unique ID) and displaying the text with a combo box.

I demonstrate how to do this in the particular case of importing Excel data into Access on my blog here:- Excel in Access (Part 2) Go down the page and look for the image with the text on it:- " replace text with numbers (first step)" you should be able to see how to do it from the images and text available.

I also have set videos demonstrating the same, but with a slightly different scenario on this page:- https://www.niftyaccess.com/excel-in-access-parts-1-2-and-3-as-video-instructions/ have a look at video "3" Setting up the "Lookup" Table

 

[marvo]

SOLVED

Thank you. All your ideas gave me inspiration.
A very elegant solution in the end that I'm really happy with.

Imported the csv into a Temptable that included fields for all the values and all the IDs - even though IDs did not exist in the csv

Built a query that joined this Temptable to the various 'lookup' tables

But here's the kicker -
The Query Designer automatically joins the tables based on ID when you add them to the query
so -
1. Delete the joins that the query designer created
2. create a join between fieldnames e.g. join ClubName to ClubName (Not IDs)
3. On the 'Join Properties', select option 2 - Include all records from Temptable and only those records 'lookuptable' where the joined fields are equal
4. For your query, select 'Values' from the Temptable and IDs from the LookupTables

And hey presto!!

Once the query was working, change it to an Append Query and ta da!

Thank you all!

Editors Note: Entirely dependant on PERFECT data quality - i.e. if the ClubName in the temptable is not spelt exactly the same as in the lookup table, then the join can't work

 

[pbaldy]

Glad you got it sorted!

 

[theDBguy]

SOLVED

Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.

 

[Uncle Gizmo]

Editors Note: Entirely dependant on PERFECT data quality - i.e. if the ClubName in the temptable is not spelt exactly the same as in the lookup table, then the join can't work

Yes, this can be a big problem depending on what you are importing... I have a project, a Bank Account Reconciliation dB I've been working on for a number of years. Basically I want to be able to import all of my bank account details into a database from Comma Separated Value Files (CSV), and indeed data quality is a common problem.

In particular regarding bank statements, occasionally the code representing a particular company will change slightly, alternatively you might normally buy your groceries from Tesco's Newbury, and then one day you buy from Tesco's Reading but you want the data to go into the same group.

I've been working on a solution that looks through the lookup list, if it discovers an item in the latest (CSV) that is not in the list, then it will pop up a form and ask you what you want to do with it. Do you want to associate it with a particular group? Do you want to create a new group things like this...

It's not finished yet!