Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Importing a flat file table into multi-table design

Posted on 1999-06-29
25
Medium Priority
?
396 Views
Last Modified: 2012-05-04
I want to import a single flat file table of names and addresses into my one-to-many relational database. My information is split into three tables: tblOrganisation, tblAddress, tblPerson.

What's the best approach to splitting the flat table so that the Organisation field goes to tblOrganisation, the Address fields to tblAddress, and the name fields to tblPerson, and that corresponding joins are maintained?
0
Comment
Question by:louison
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 4
  • 4
  • +2
25 Comments
 
LVL 10

Expert Comment

by:brewdog
ID: 1998001
I would probably set up three import specs -- you have the option to skip fields in your creation of the spec -- and then just make sure I import into the main table(s) first, then the related tables, for your integrity.

From the main database window, go to File | Get External Data -> Import . . . and then (after finding your file) click Advanced. This will let you enter the field names (enter all the fields that will appear in your flat file). Then check the ones you want to skip and choose Save As to make your first spec. Repeat the procedure for the other two specs/tables.

Then all you'll have to do is create a macro or run code to do the import.

Hope that helps . . .

brewdog
0
 
LVL 7

Expert Comment

by:Dedushka
ID: 1998002
What address is used? Address to organizations or person's address. If persons can work in several organizations, the relationship must be "many-to-many". Any way, I first imported  the whole external table in the temporary table, then has select necessary information and append it to appropriate tables. When result will be satisfactory, temporary table possible to delete.
0
 
LVL 5

Expert Comment

by:patrickl
ID: 1998003
I would also use one import and then three (or perhaps more) "make table" or "append" queries to fill the tables.

The biggest problem usually is in filling the unique keys. If you only have autonumber keys you will be in some trouble. Best way to go then is to add an autonumber to the imported table. You can then use that one to link the persons, organisations and adresses together.

For instance you might have:

org: MicroSoft person:Bill Gates

the org would go to the org table and get an autonumber of 1 now you would need some way to get the 1 into the persons table to link it to the organisation. Perhaps you can use the organisation name for this, but I have found that often a unique key is not readily available hence I add another key. This key would then need to be store (temporarily) in the three tables as well.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 7

Expert Comment

by:Dedushka
ID: 1998004
It is seriously problem to link all three tables. patricl gives you a direction to move. If you need detail assistance then me, patricle or brewdog can provide it. Brewdog was the first and should to assist you, I think :-)
0
 
LVL 5

Expert Comment

by:patrickl
ID: 1998005
Yes he seems to be afraid that the question gets deleted on him again. So he posted an "answer" to begin with. Personnally I don't think his is the right way to go, but this wouldn't be the first time we disagree. (or he just missed the trouble with linking the tables together)

Anyway, I must admit louison, this is hardly something you can begin explaining through this medium. Either you need some expertise in Access yourself or this is gonna take a long time.
0
 
LVL 10

Expert Comment

by:brewdog
ID: 1998006
actually, patrickl, as long as the relationships between the three tables are all right, i.e., one main table and two related, my approach is just as good if not better than the "import and use append queries" idea. If the relationships are more complex, then neither of our solutions will work very well, as you point out. At that time, I'd counsel louison to reject my answer and someone could help him/her work through the related issues.

My personal approach to the answer vs. comment decision is that, if I have a solution I deem to be the best or equal to the best, and I am the first one on the question, I'll typically submit an answer. If anyone else has submitted a possible solution as a comment, I'll make mine a comment. if either you or Dedushka had been here before me, my answer would be a comment, though I'd still think it preferable.

:) Oh, by the way, I posted the question for cawa based on a question I had seen you post for a similar situation. :)
0
 
LVL 7

Expert Comment

by:Dedushka
ID: 1998007
patricle, I don't need expertise in Access, maybe i need expertise in English :-(
0
 
LVL 5

Expert Comment

by:patrickl
ID: 1998008
Brewdog,

The inlembo question was not for me, but for cloackwatcher, but Indeed DSegard posted a similar complaint for a question where I got "stiffed". It seems things are starting to go wrong again. It wasn't meant as a pun though. It's just that I normally don't see you post an answer "out of the blue".

But back to the question, I agree that when the relations are simple it doesn't matter. However I would guess there are autonumber involved and they would be very difficult to fix if you start out with three seperate tables. Since you will then have lost the link that was there in the first place. But then again, perhaps the problem is much easier than I thought.

Perhaps louison can shed some (more) light on the problem and then we'll know for sure. (maybe an idea of what the tables would look like, especially on the keys and how the relations would work)

0
 

Author Comment

by:louison
ID: 1998009
Just to clarify the table structure: one organisation can be linked to many addresses, and one address can be linked to many persons, but an address can only link to one organisation and a person can only be linked to one address. There link between a person and an organisation is via the address table. Primary key fields in all three tables are autonumbers. In the imported flat table there's no key, and there are duplicate organisations and addresses. I'm using Access 97.

I don't quite understand Brewdog's description of an import spec. I tried importing the table as a .dbf and clicked on the Advanced but all you see is various Find options. So I can't really tell whether this proposed solution works.

Clearly it needs some complicated work with temporary keys to retain the relationships between tables when the data is imported, which I guessed would be inevitable. I just thought that as this must be a fairly common task, there might be a well tried procedure for it.


0
 

Author Comment

by:louison
ID: 1998010
Just to clarify the table structure: one organisation can be linked to many addresses, and one address can be linked to many persons, but an address can only link to one organisation and a person can only be linked to one address. There link between a person and an organisation is via the address table. Primary key fields in all three tables are autonumbers. In the imported flat table there's no key, and there are duplicate organisations and addresses. I'm using Access 97.

I don't quite understand Brewdog's description of an import spec. I tried importing the table as a .dbf and clicked on the Advanced but all you see is various Find options. So I can't really tell whether this proposed solution works.

Clearly it needs some complicated work with temporary keys to retain the relationships between tables when the data is imported, which I guessed would be inevitable. I just thought that as this must be a fairly common task, there might be a well tried procedure for it.


0
 
LVL 5

Expert Comment

by:patrickl
ID: 1998011
Well it is well tried, but it remains  (for the largest part) a job that needs some manual work done.

Is the file that holds the flat table a dbf file? Then you don't need the import specs. They are for importing text files. He (and I as well) probably just assumed you had a text file holding the data.

First of all you will need a unique key on the flat table. First you import the dbf file. Then add a field that will serve as the primary key and make it an autonumber.

Then the rest depends on whether the tables are empty now or not.

Again, it will probably be very difficult to get all this explained this way. It would be best if you send the structure of the three tables and the flat file (or a few records as an example) through e-mail. Perhaps its best to check who wants this done. I would like to help, but are not going to "war" over this. If brewdog feels he has the "rights" to this question than perhaps he can help you further. Otherwise I will be glad to help you further.
0
 
LVL 10

Expert Comment

by:brewdog
ID: 1998012
Yeah, before any of us proceed, it would be helpful to get an idea of what this flat file looks like. Is it a dbf file, or a text file? Regardless, you wouldn't want to hit the Advanced button where you did: it sounds like you clicked Advanced on the screen where you are simply supposed to be locating the file to import. After you've selected the file and Access starts the Import Wizard, then you'll get an Advanced button you can use to set up an import spec.

Please, a little more information on the flat file, including:

format (dbf, txt, etc.)
source
frequency at which you'll have to do this import

Thanks -- and if this does get more complicated, I'll have you reject my answer so others can have equal opportunity.
0
 

Author Comment

by:louison
ID: 1998013
The tables I will need to import will normally be dbf or mdb files, so I've rejected brewdog's answer about importing text files - but thanks anyway. I'm OK when it comes to adding a primary key to the imported table, the bit I'm trying to figure out is how to append this table's data into the three tables in such a way that the correct foreign keys are created: tblOrgn has
OrgnID - primary key (autonumber)
OrgName etc

tblAddress has
AddressID - primary key (autonumber)
OrgnID - foreign key
Address1, etc

tblPerson has
PersonID - primary key (autonumber)
AddressID - foreign key.
Title, FirstName etc

The flat file is just OrgName, Title, FirstName,etc Address1 etc, with some duplicates of the Organisation and Address fields.

Let me know if you need any more info - I can email the mdb if necessary. it's just a prototype at the moment.

Cheers louison

0
 
LVL 5

Expert Comment

by:patrickl
ID: 1998014
As I said before, the easiest would be if you have a second "unique" field in all the tables. So if for instance OrgName for tblOrgn , Firstname+LasteName(+"?") for tblPerson and Address1 for tblAddress would be unique. Then it would be a matter of writing three append queries.

For filling the tblOrgn use something like:

INSERT INTO tblOrgn ( OrgName )
SELECT DISTINCT tblFlat.OrgName
FROM tblFlat;

For filling the tblAddress:

INSERT INTO tblAddress ( Address1, OrgnID )
SELECT DISTINCT tblFlat.Address1, tblOrgn.OrgnID
FROM tblOrgn INNER JOIN tblFlat ON tblOrgn.OrgName = tblFlat.OrgName;

and for filling the tblPerson:

INSERT INTO tblPerson ( Title, FirstName, AddressID )
SELECT DISTINCT tblFlat.Title, tblFlat.FirstName, tblAddress.AddressID
FROM tblAddress INNER JOIN tblFlat ON tblAddress.Address1 = tblFlat.Address1;


Of course you would need to create the proper joins (I called the table containing the flat data tblFlat)

If you can't use some fields of the flat table as a unique key you will need to insert an extra field in all 4 tables. If you need that let me know. But perhaps this description already gets you there.
0
 
LVL 1

Expert Comment

by:Deverill
ID: 1998015
The solutions posted will get the data in the tables, but how to get the links from Bill Gates to Microsoft?  I'll do only 2 tables for simplicity but this should work for more as well.  This requires importing extra fields in tables, BUT they can be removed later.

Table 1 (tblPerson):
   Name
   OrganizationName
   OrganizationID

Table 2 (tblOrganization):
   UniqueIDNo
   Name
   Category
   etc.

Then do an update query.  Something like this:
UPDATE tblOrganization
  INNER JOIN tblPerson
  ON tblOrganization.Name = tblPerson.OrganizationName
SET tblPerson.OrganizationID = [tblOrganization].[uniqueid]
WHERE (([tblorganization].[name]=[tblPerson].[organizationname]))

In basic english, match the textual org. names and then do an update of your person.orgidno to the idno of the org whose name matches.  

When you're finished you can delete the org names from the person table.  

I hope this is clear enough and that it helps!
  Dev
0
 
LVL 5

Expert Comment

by:patrickl
ID: 1998016
Did you miss the comment from louison Deverill? Did you read my suggestion?

it states:

tblPerson has
PersonID - primary key (autonumber)
AddressID - foreign key.
Title, FirstName etc

There is no link from tblPerson to tblOrg. And I did fill the tblPerson.AddressID and tblAdress.OrgnID in my example. And if there was it would be a small extrapolation from my example anyway.
0
 
LVL 1

Expert Comment

by:Deverill
ID: 1998017
The solutions posted will get the data in the tables, but how to get the links from Bill Gates to Microsoft?  I'll do only 2 tables for simplicity but this should work for more as well.  This requires importing extra fields in tables, BUT they can be removed later.

Table 1 (tblPerson):
   Name
   OrganizationName
   OrganizationID

Table 2 (tblOrganization):
   UniqueIDNo
   Name
   Category
   etc.

Then do an update query.  Something like this:
UPDATE tblOrganization
  INNER JOIN tblPerson
  ON tblOrganization.Name = tblPerson.OrganizationName
SET tblPerson.OrganizationID = [tblOrganization].[uniqueid]
WHERE (([tblorganization].[name]=[tblPerson].[organizationname]))

In basic english, match the textual org. names and then do an update of your person.orgidno to the idno of the org whose name matches.  

When you're finished you can delete the org names from the person table.  

I hope this is clear enough and that it helps!
  Dev
0
 
LVL 1

Expert Comment

by:Deverill
ID: 1998018
Patrickl, if you want to be condescending and squelch out attempts at answers then let me give you my email address (jcsewell@bigfoot.com) and spare EE the snide sarcasm.

I was trying to provide what I thought was a workable answer to a problem.  Usually people appreciate attempts to help them even when the answer turns out not to help.  ...and people wonder why folks are shy to answer problems.

A simple "He doesn't need a link to organization" or just ignoring me would be much more peaceful but not nearly as ego boosting as your reply.
0
 
LVL 5

Expert Comment

by:patrickl
ID: 1998019
Well it is pretty annoying to see someone post something I already posted just as if I hadn't posted anything. How do you think it makes me feel when you pull a stunt like that?
It would take only a shred of common decency to at least read the last few comments.

So in the light that people don't read comments very well in general it is not wise to poste a single line. People tend not to understand those. Perhaps I should have put one of those silly smileys at the end.

Actually, funny you obviously pressed and refrained from commenting the first time. Why do so the second time you read it?

I really see no reason for you to get mad at me. You made the error and I just pointed it out. Don't blame your own shame on me.
0
 
LVL 1

Expert Comment

by:Deverill
ID: 1998020
I apologize to the rest of you folks.  I tried to get patrickl to take this off list but obviously he wants an audience.  
Since he didn't provide an email address I'll say only this and I'm abandoning this topic.
1. You said I posted the same thing you did.  No, as you yourself point out my "solution" did a link that isn't even valid.  Besides, the approach is totally different and more of what a novice can understand rather than a lot of SQL code with no explanation.  You assume louison is an expert and that may not be the case.
2. My offering a different approach to the same end is not without a "shred of common decency"... it's called multiple ways to get the same thing and explanation with code and not just code.
3. I didn't "press and refrain" from commenting.  I left the window open for a long time, forgot I submitted, and refreshed to see new comments.  You are so smart.  NOT!
4. I "got mad" at your way of commenting, not that you pointed out an error.  'Did you read my suggestion' is like a parent scolding a child saying "Did you hear what I said!" and THAT is the root of my being offended.  Or maybe it's just a language problem since from your profile you are apparently not a native English speaker.

I have no further suggestions for the problem, you have my email, and I'll not comment here again in this thread.  If you have something to say to me do it without an audience.
0
 
LVL 5

Expert Comment

by:patrickl
ID: 1998021
Well since we are pulling profiles I see you only answered one question. That would explain the fierce defense.

I don't mind an "audience". If you try to take my suggestion down I don't see why I can't explain why it is better here? Especially since you seem to think so badly of Lousion? Since I did follow the question from the beginning I think Lousion is skilled enough to understand the suggestions posted. If not he can ask for clarification.

Again you should have read better:
1) I didn't say you posted the same thing. It's just that suggest an answer where I already did. And then you claim mine doesn't do the job and yours does. That is rather impolite. Especially if it only doesn't seem to work since you didn't read properly.
The comments before already explained what I was suggesting in SQL so that's why the explanation was not repeated.
2) Your suggestion is not a very smart suggestion, since you can do this in one go as I showed. How you can claim suggesting doing this in diferent (i.e. worse) ways helps answer the question is beyond me. It would if your suggestion at least added something.
3) So you did get the comment earlier and refrained from answering.
4) I'm not gonna defend my "short" writing style, nor that I'm not native english. Its just when you jump in at the end when the question is virtually answered; you claim the suggested answer is no good; post something that is worse than the suggestion you are trying to overclass; then you will get a response pointing out the error. No reason to start sobbing.

This is just why I'm getting out of this place. Too many non-experts that post illogical remarks and then demand your e-mail address so they can defend their lost honor and thereby annoy me even more. Why on earth would I want to receive e-mail from you? Perhaps if you were an expert you could teach me (us) something but this is not getting us anywhere.

So, you should indeed apologize for bothering us with this "discussion". If you would just have understood the first comment and went away you wouldn't have looked so dumb now.
0
 
LVL 5

Expert Comment

by:patrickl
ID: 1998022
Lousison,

Just you make sure you understand the possibilities, I would like to explain the other options. The first suggestion I made will work if you can find some unique key in the Org, Person and Address parts of the flat table. Usually you can and then you would be on your way.

If not then you have basically two options (well I can think of two, perhaps Brewdog has another. He seems to be on a different track than I). Basic point is that you should add a real unique key (i.e. a line number) to the flat table as I described before. Now:
1) You can store the line number on each Org, Person and Address record you create. That way you retain the link even after the single flat table is split into three seperate ones.
2) Or if all else fails, use a VBA function that loops through the flat table and fills all seperate tables. (I have had to resort to this only once)

Without the actual data it is a bit hard to suggest what is needed.
0
 
LVL 5

Expert Comment

by:patrickl
ID: 1998023
Hmm, I meant Louison.
0
 

Author Comment

by:louison
ID: 1998024
I've been busy on other things the last few days but returning to my EE question I find much heat has been generated. I hope you guys haven't come to blows over this, and I appreciate all your efforts to suggest a solution.

I've tried out patrickl's suggested SQL code and seems to do the trick, although as he points out it requires suitable columns in the address and person tables to provide the integrity. Orgname and Postcode can generally be relied upon for this, with maybe some preparation of the flat table beforehand and some editing afterwards.

So patrickl if you'd care to submit your comment  as an answer I'll give it the thumbs up.

Cheers

Louison
0
 
LVL 5

Accepted Solution

by:
patrickl earned 600 total points
ID: 1998025
OK, thanks.

But really, if you need some more help I'd be glad to explain more.
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question