Importing unique IDs from CSV (1 Viewer)

jaryszek

Registered User.
Local time
Today, 10:27
Joined
Aug 25, 2016
Messages
756
Hi Guys,

i have table which is imported from CSV.
And i want to use uniqueIDs there for each OperatingSystem which i have there. But rows in CSV can be deleted or added or updated.

How the process should look like and temp_table in Access?
I should have autonumber Primary Key and additional UniqueID field in temp table?

This table is related as one to many to other table.

Please help,
Jacek
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:27
Joined
May 7, 2009
Messages
19,169
temp_table for the CSV.

the final table:

autonumber(primary key)
uniqueID (text, no duplicate)
description (short text)

use the final table and update it constantly from newly imported csv (temp_table).
 

jaryszek

Registered User.
Local time
Today, 10:27
Joined
Aug 25, 2016
Messages
756
Thank you very much arnelgp.

uniqueID should have index as unique?

So constraint (1 to many) should be from uniqueID field (1) into second table (many) yes?

Jacek
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:27
Joined
May 7, 2009
Messages
19,169
yes, should be unique so next time you update, only new records will be added.

1->many, yes.
 

jaryszek

Registered User.
Local time
Today, 10:27
Joined
Aug 25, 2016
Messages
756
Ok,

so assume that i have second table - t_junction.

And in this t_junction i have JunctionID, UniqueID and Atribute.

So i have to create relationship from UniqueID in t_OperatingSystems (1) to UniqueID in t_Junction (many) as TEXT fields? So join text fields?

Thank you,
Jacek
 
Last edited:

jaryszek

Registered User.
Local time
Today, 10:27
Joined
Aug 25, 2016
Messages
756
In other words -> Can i use text field - UniqueID as foreign Key?

It will be something like here:


Best,
Jacek
 

Attachments

  • Screenshot_9.png
    Screenshot_9.png
    92.9 KB · Views: 201

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:27
Joined
May 7, 2009
Messages
19,169
which one will be the Master table, the testTable?
then you need to reverse the connection arrow.

1 rec (testable) -> many of imported csv
 

jaryszek

Registered User.
Local time
Today, 10:27
Joined
Aug 25, 2016
Messages
756
TestTable lets say it is a junction table.
Why the knowledge if this is a master data is important?

I am adding example and please help me to set up this correctly.
ImportedCSVtemp is table when i will be importing data from CSV.

So updated,deleted or updated rows will be in OperatingSystems table.
I can add query which will be checking delta between ImportedCSV and OperatingSystems.

But from OperatingSystems the relationship should be also from text uniqueID text field?
Or i should only use prmiary autonumber key? But how to match the fields then?

Please help,
Jacek
 

Attachments

  • Screenshot_10.png
    Screenshot_10.png
    78 KB · Views: 183
  • Database21.accdb
    448 KB · Views: 90

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:27
Joined
May 7, 2009
Messages
19,169
I think you don't need the test table.
you're just making the chain long, when you can
get all info from os table.
 

jaryszek

Registered User.
Local time
Today, 10:27
Joined
Aug 25, 2016
Messages
756
I can handle it.
But this is ok with importing CSV table to OperatingSystems from CSV?
In set up like in example?

So createg FK from UniqueID text field?



Jacek
 
Last edited:

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 12:27
Joined
Feb 28, 2001
Messages
26,999
If the text is short enough for your NameID field, I see no technical problem in making it into a key for a table to do lookups.

"Short enough" by my personal standards would be no more than four characters for the NameID. After that, it is cheaper and easier to use LONG (numeric) keys. The issue is, of course, that a text key can be bigger than that, but you don't want keys to be very big at all, because otherwise they lose efficiency during searches. But for short tables with few records and RELATIVELY short keys, shouldn't be a performance issue either.
 

jaryszek

Registered User.
Local time
Today, 10:27
Joined
Aug 25, 2016
Messages
756
The_Doc_Man thank you very much.

Nice explanation!
Why arnelgp wants to use string there? Whati s purpose? I would use number as first choice?

Jacek
 

Users who are viewing this thread

Top Bottom