Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-18-2019, 06:07 AM   #1
jaryszek
Newly Registered User
 
Join Date: Aug 2016
Posts: 608
Thanks: 300
Thanked 1 Time in 1 Post
jaryszek is on a distinguished road
Importing unique IDs from CSV

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

jaryszek is offline   Reply With Quote
Old 09-18-2019, 06:36 AM   #2
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,569
Thanks: 68
Thanked 2,745 Times in 2,630 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
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).
arnelgp is offline   Reply With Quote
The Following User Says Thank You to arnelgp For This Useful Post:
jaryszek (09-18-2019)
Old 09-18-2019, 07:43 AM   #3
jaryszek
Newly Registered User
 
Join Date: Aug 2016
Posts: 608
Thanks: 300
Thanked 1 Time in 1 Post
jaryszek is on a distinguished road
Re: Importing unique IDs from CSV

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

jaryszek is offline   Reply With Quote
Old 09-18-2019, 07:46 AM   #4
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,569
Thanks: 68
Thanked 2,745 Times in 2,630 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Importing unique IDs from CSV

yes, should be unique so next time you update, only new records will be added.

1->many, yes.
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
The Following User Says Thank You to arnelgp For This Useful Post:
jaryszek (09-18-2019)
Old 09-18-2019, 07:55 AM   #5
jaryszek
Newly Registered User
 
Join Date: Aug 2016
Posts: 608
Thanks: 300
Thanked 1 Time in 1 Post
jaryszek is on a distinguished road
Re: Importing unique IDs from CSV

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 by jaryszek; 09-18-2019 at 08:25 AM.
jaryszek is offline   Reply With Quote
Old 09-18-2019, 08:40 AM   #6
jaryszek
Newly Registered User
 
Join Date: Aug 2016
Posts: 608
Thanks: 300
Thanked 1 Time in 1 Post
jaryszek is on a distinguished road
Re: Importing unique IDs from CSV

In other words -> Can i use text field - UniqueID as foreign Key?

It will be something like here:


Best,
Jacek
Attached Images
File Type: png Screenshot_9.png (92.9 KB, 71 views)
jaryszek is offline   Reply With Quote
Old 09-18-2019, 09:11 AM   #7
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,569
Thanks: 68
Thanked 2,745 Times in 2,630 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Importing unique IDs from CSV

which one will be the Master table, the testTable?
then you need to reverse the connection arrow.

1 rec (testable) -> many of imported csv

__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 09-18-2019, 08:20 PM   #8
jaryszek
Newly Registered User
 
Join Date: Aug 2016
Posts: 608
Thanks: 300
Thanked 1 Time in 1 Post
jaryszek is on a distinguished road
Re: Importing unique IDs from CSV

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
Attached Images
File Type: png Screenshot_10.png (78.0 KB, 56 views)
Attached Files
File Type: accdb Database21.accdb (448.0 KB, 9 views)
jaryszek is offline   Reply With Quote
Old 09-19-2019, 01:09 AM   #9
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,569
Thanks: 68
Thanked 2,745 Times in 2,630 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Importing unique IDs from CSV

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.
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 09-19-2019, 01:28 AM   #10
jaryszek
Newly Registered User
 
Join Date: Aug 2016
Posts: 608
Thanks: 300
Thanked 1 Time in 1 Post
jaryszek is on a distinguished road
Re: Importing unique IDs from CSV

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 by jaryszek; 09-19-2019 at 02:57 AM.
jaryszek is offline   Reply With Quote
Old 09-19-2019, 02:57 AM   #11
jaryszek
Newly Registered User
 
Join Date: Aug 2016
Posts: 608
Thanks: 300
Thanked 1 Time in 1 Post
jaryszek is on a distinguished road
Re: Importing unique IDs from CSV

Can anybody confirm please?

Jacek
jaryszek is offline   Reply With Quote
Old 09-19-2019, 08:25 PM   #12
jaryszek
Newly Registered User
 
Join Date: Aug 2016
Posts: 608
Thanks: 300
Thanked 1 Time in 1 Post
jaryszek is on a distinguished road
Re: Importing unique IDs from CSV

Anyone please help,

Jacek
jaryszek is offline   Reply With Quote
Old 09-20-2019, 06:04 AM   #13
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,748
Thanks: 93
Thanked 1,724 Times in 1,595 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Importing unique IDs from CSV

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.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
The Following User Says Thank You to The_Doc_Man For This Useful Post:
jaryszek (09-22-2019)
Old 09-22-2019, 10:12 PM   #14
jaryszek
Newly Registered User
 
Join Date: Aug 2016
Posts: 608
Thanks: 300
Thanked 1 Time in 1 Post
jaryszek is on a distinguished road
Re: Importing unique IDs from CSV

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

jaryszek is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Counting unique records and unique with condition Magnus1982 Modules & VBA 2 08-12-2018 01:38 PM
Importing csv file / unique records Lady Phoenix Tables 1 01-24-2015 04:27 AM
Unique Records (But not UNIQUE or DISTINCT records) Pullingmyhair Queries 3 10-15-2014 03:25 PM
Query Unique and Non-Unique milkman2500 Queries 1 01-09-2013 09:43 PM
how to get the unique row qwang1115 Queries 3 12-16-2008 03:14 PM




All times are GMT -8. The time now is 01:28 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World