Importing excel data in access table

[khurram7x]

Importing excel data/relationship

I'm looking to import huge excel sheets in access, but normalization process in Access has forced me to divide all the columns in Excel to about 12 tables in MS Access.
How how could I import data from excel sheet columns to 12 different tables??

Thanks in advance.

 

[plog]

You import it once into a staging table, then you use APPEND queries to put data from that table into the correct tables.

 

[Anakardian]

Before you start appending the data to the new tables you should convert all the future foreign keys to their new values.
This will ensure you can catch any problems before they crop up in your new data tables.
This means that instead of a country name in your import table, you would change it to the corresponding country id. Any names that are not converted to a number (assuming you are using an autonumber as id) means you are missing something in your country table.

It does mean you need to import the data in the right order but you can save the queries you use to convert and move the data so if you ever need to do it again you can just fire off your queries again.

 

[CJ_London]

assuming excel does not have more that 255 columns, create a linked table to excel, then write 12 different append queries, one for each table.

Anakardian makes a good point, but if your ID's are autonumbers then you may not be able to update these - however if you have inconsistency in the data (e.g. two customers, same name and no other value to distinguish them) you will need to do this.

One way would be to ensure the order you import data is correct - say your excel file consists of customers, invoice headers and invoice detail. Append customers first, then when you come to import invoice headers, use a Dlookup or subquery to find the customerID based on say customer name or account number.

 

[khurram7x]

Thanks, I'm doing it now.

One more question in relation to this. I've observed that I can copy form Excel and paste in Access, then why we're not using this technique to map our Excel tables in Access?

And is there any way to keep the table structure as clean as possible?? I can see 10's o tables before I finish the project, is it going to be really messy!!

 

[plog]

And is there any way to keep the table structure as clean as possible?

Yes, its called normalization:

http://en.wikipedia.org/wiki/Database_normalization

I'm sensing you're not heading down that path. I fear you have a bunch of spreadsheets with similar structure, but differ by name. For example, JuneSales, JulySales, AugustSales. If that's the case and all those spreadsheets contain the same fields, you would simply have a Sales table and then a SalesMonth field to distinguish what month each record is for.

Are those 10's of tables going to have the exact same structure?

 

[khurram7x]

Before you start appending the data to the new tables you should convert all the future foreign keys to their new values.
This means that instead of a country name in your import table, you would change it to the corresponding country id. Any names that are not converted to a number (assuming you are using an autonumber as id) means you are missing something in your country table.

You mean, I'll add another column as a countryID if i've a column with country name, or shall I change the country names as countryID's??? If yes, why would I do that if I need country names??
Sorry, I tried to understand hard but not getting the point!!

 

[plog]

My point is you wouldn't have 10 tables all structured the same but named for each country:

tblCanada
tblMexico
tblFrance

Depending on the amount of data associated with each country, you may or may not have a seperate table to hold all your counries. If you do have a seperate table (tblCountries) then yes you would use a primary key there and a numeric foreign key in external tables.

 

[khurram7x]

Re: Importing excel data/relationship

Thanks for the advice. I'll keep it in mind.
This is my first database and is extremely difficult structured. For an Electrical/Instrumentation company and I'm stuck with that.
I've posted the first Excel file I received and requested for advices in another post 'Database Design', but I guess I was not able to explain good and seen very few replies.

 

[plog]

I don't know enough about your data to comment on specic issues of your design, so I can only go on general things I see with the structure you posted.

I don't see any similiarly structred tables like I first mentioned, so that's good. I do see an issue with how all your tables relate however.

There should only be one path from one table to another. Look at tblCableSchedule_Jo... and tblCableSchedule_ManPowe.... I can see 2 paths between them, 1 directly and 1 via tblCableSchedule_CableSc... That is incorrect. either they shouldn't be directly linked, or they should be. But you should only have 1 way to get from one to the other, not multiple paths.

I see that issue with tblCableSchedule_SUM_Ma... as well, even though you haven't established the relationship. It still exists because it has a Scope field. Scope and ActivityID should only exist together in 1 table.

 

[khurram7x]

Re: Importing excel data\designing

There should only be one path from one table to another. Look at tblCableSchedule_Jo... and tblCableSchedule_ManPowe.... I can see 2 paths between them, 1 directly and 1 via tblCableSchedule_CableSc... That is incorrect. either they shouldn't be directly linked, or they should be. But you should only have 1 way to get from one to the other, not multiple paths.

I see that issue with tblCableSchedule_SUM_Ma... as well, even though you haven't established the relationship. It still exists because it has a Scope field. Scope and ActivityID should only exist together in 1 table.

Thanks, attaching after modifications. Does it look better??

What i'm trying to achieve is a bit complex to explain. this is about a Cable Scheduling job tblCableSchedule_CableS... where each cable tblCableSchedule_cabletype... should be pulled between areas define in tblCableSchedule_G&TCode table. then they're calculating manhours tblCableSchedule_manpower_... based on the total scope of work and again producing Summary of manhours tblCableSchedule_SUM_...
This is what i could explain in minimum words.

I've an Excel sheet from where I'm collecting bits and pieces to produce this access table. And this sheet is only the first of many!

Khurram

 

[plog]

That fixed the issues I saw. Again, not familiar enough with your data for any other advice, but I don't see any glaring errors like the multi-path one I saw earlier.

 

[khurram7x]

That fixed the issues I saw. Again, not familiar enough with your data for any other advice, but I don't see any glaring errors like the multi-path one I saw earlier.

Thank you, means I've started to understand things )... can you have a final look at last version of relationship table i'm attaching and advice if i've made any mistake in structure please?

Also I've made 1 column tables along with ID field, is it ok??
I've done this because otherwise there would be lot of repetitions.
Another thing i see that to reach WorkScopeID_PK field in ...SUM_PullingCodes table from tblCableSchedule_Index table, i need to go throught ...ManPower_HoursWorked table. Is it correct approach?? I see that some times i've to go across few tables to get the required data, it'll generate complex queries... can you advice on this please?? is it correct approach??

one more thing, when I'll copy data from Excel sheet I feel I need to generate a foreign key against each ID.
Let's say, on very left column I'll assign a JP_ID_PK manually.
Next, in ...cableScheduleID I need the specific JPNumber along each CableSchedule column.
As I see i need to do it manually, and for big table like I'm working with... it is going to be lot of work.
Is there anyway to get this done automatically via queries please??

Thank you, I really appreciate your support.

Khurram

 

[plog]

First, you've reintroduced multiple paths. There's 2 ways to get from tblCableSchedule_Index to tblCableSchedule_KKTSCodes--straigth across using tblCableSchedule_KKTSCodeDetails and going down through tblCableSchedule_ManPower_HoursWorked. There should only be one possible path between 2 tables.

Also, I do not think you should have a table with 1 field. I don't think tblCableSchedule_JobPack is necessary, nor is tblCableSchedule_KKTSCodes. Just use the value in table instead of using an ID and linking.

I see that some times i've to go across few tables to get the required data, it'll generate complex queries... can you advice on this please?? is it correct approach??

Yes, this is the correct approach, as long as you have laid out your tables properly. From what I know, it looks fine, going through multiple tables is perfectly acceptable.

For your JP_ID_PK creation I would really need to know the process you are using to populate your tables. Generally, if it was a self-contained database and users were entering/editing data via forms, you would assign an autonumber primary key and Access would keep it sorted out. If you have some sort of import process that brings in multiple files, then you might have to manually assign IDs to each record.

Can you explain your import process? Is this a one time deal, or will it be ongoing?

 

[khurram7x]

Thank you. I've redesigned and removed the multiple paths as indicated.
Attaching once again, and attaching the Excel sheet too which i'm using to design this database.

I've removed the ...CodeDetails Table and created a new column in ...Index table as 'ActivityName'. It is same a detailed description of codes introduced in ...KTKSCodes table.

I've attached Excel file so that you can have a look and advise if it is still advisable to remove ..._KKTSCodes and ..._JobPack tables. If i'll do that it'll bring lot of repeating values. This is what I think, but at the same time i'm at a beginners level only. so your words are valuable.

Thanks again.

 

[plog]

You still have multiple paths. There's two ways to get from tblCableSchedule_J... to tblCableSchedule_ManPow...

You should remove any tables that have only 1 real field (autonumber primary keys don't count). So if JPNumber is the only real field, then you don't need the whole table. I see a table called tblCableSchedule_KKT... with a KKTSCode and Description field--that table is fine to remain because it contains more than 1 real field.

Honestly, you've got a really complex data set I can't really get my mind around it to help you out more specifically. Can you explain what real world process this database represents? Don't talk about queries or tables, just tell me what real life process this is for.

 

[khurram7x]

Thank you. Attaching screenshot once again, I hope relationship looks well now?? )

Well it is my 1st project, and it really is complex for a 1st project. Like I mentioned earlier, it is for a Cable Scheduling job for our Engineering Dept. and the guy tried to explain me how the process works. I'll try to repeat. You'll need to take a look at attached Excel file too, to make sense out of things.

We basically are electrical/instrumentation company and when our company gets a Job, it goes through Work Breakdown Structure (WBS) and it divides in few Job Packs, depending upon the nature of work. These Job Packs are then assigned with a Job Numbers. Cable Schedule is a table of information about cables for a particular installation. This can relate to cables for power, lighting, communication, security, fire, etc. We also name the job as cable scheduling job.

Work Scope shows the type of work, i.e. Electrical/Instrumentation or some other, and Work Category is a job discipline within work scope area. These things will get more clear if you look at the attached Excel file too.

Then we need Cable Types which needs to be pulled/installed for a particular job. G&TCodes are glanding and termination points information, like From where To what point cable need to be pulled. Index table shows information about installation of cables, support trays and same kind of things. KKTSCodes table shows the different codes assigned to different activities and its description, inside the company. Then ActivtyName column in ..._Index table shows further details of the work description described in KKTSCodes table. Rest of the tables are man hour worked calculations.

Do you need more explanation on anything, let me know please.
Really appreciate your support.

Thanks.

 

[plog]

Yeah, that's complex for sure. I really don't think I am going to be able to wrap my head around it, so the way to proceed is to import your data into your tables and see if it works for you.

I do see you eliminated the multiple paths, but the multiple relationships still sort of exist. You have 2 forieng keys in tblCableSchedule_Index (Job_ID_FK & KKTSCodeFK) that shouldn't be. Maybe you left them in there just to be safe as we worked through the structure, but because they are there, it sort of violates that multiple path rule (even though you haven't defined the relationship).

Like I said, I think the next step is for you to try import a small set of data into this structure and see if you can get out what you expect. By that I mean, see if you can construct a few queries that will be the foundation for the reports you will need to run. If you can get those queries to populate the correct data you need, I would have faith in the table structure being correct.

 

[plog]

One more thing, and this is just nitpicky--but you have a few field names that are reserved words (http://support.microsoft.com/kb/286335).

Date, Type and From are all words that mean something to Access and you shouldn't use them as field names. I'd just prefix them with what data they are for (e.g. IndexDate, CableFrom, CableType). It will just make life easier when you write code/queries that reference those fields.

Also, get rid of special characters in names (spaces, parenthesis, ampersands, etc.). Try to use only alphanumeric characters in names (e.g. DetailsFrom, DetailsTo, GandTcodes). Underscores are fine to keep.

 

[khurram7x]

I do see you eliminated the multiple paths, but the multiple relationships still sort of exist. You have 2 forieng keys in tblCableSchedule_Index (Job_ID_FK & KKTSCodeFK) that shouldn't be.

Does it mean that we cannot use Foreign Key in a table which doesn't have a direct path from Primary Key to Foreign Key??

I left Job_ID_FK and KKTSCodeFK in index table because they're required in index sheet, and if i don't do that then I'll need complex query to get these values from ..._KKTSCodes ..._JobPack table. I remember you said complex queries are ok and that is why we use queries. Same applies in this case too?

I've redone the table, attaching. Can you comment now please? And how about conventions please??

I missed your question earlier, you asked whether importing in these tables will be a one time deal or will it be a continues process. Does it mean different design approaches please??
Import maybe required all the time as I see, because if we start a new database for a new project then we might be adding HR data, equipment information and other similar repeating structures.

Thanks