Importing to DB from excel file several columns from different tables

[c_sarmento]

Hello.
First of all i have to say that i'm not only a noob at access or VB, but also
portuguese, so sorry for my future mistakes

I'm going to explain my situation in order to be easier to explain my
problems:

I've created a simple database in Access with several tables, in order to
store some data that will be added later with forms, (which i didn't created
yet) or imported from excel files.

the excel files i'm working with have several sheets, and repeated columns
between themselves, so i created more one or two tables in order to reduce
the redundancy of the data in the DB and thus its size.

My problem is that i can't find the way to import "automatically" data from
that sheets specifically in to the colums i want.

I'm gonna give an example to be easier: the sheets in excel have for example
an entity column and other columns with entity data such as addresses, phone,
that are common for several sheets of that file so i created an entity table
in access DB in order to reduce the redundancy of the data in the DB and thus
its size, like i said above, but i don't know how to import just that columns
to the entity table in access DB... Can someone help me?

 

[Piet Linden]

Hello.
First of all i have to say that i'm not only a noob at access or VB, but also
portuguese, so sorry for my future mistakes

I'm going to explain my situation in order to be easier to explain my
problems:

I've created a simple database in Access with several tables, in order to
store some data that will be added later with forms, (which i didn't created
yet) or imported from excel files.

the excel files i'm working with have several sheets, and repeated columns
between themselves, so i created more one or two tables in order to reduce
the redundancy of the data in the DB and thus its size.

My problem is that i can't find the way to import "automatically" data from
that sheets specifically in to the colums i want.

I'm gonna give an example to be easier: the sheets in excel have for example
an entity column and other columns with entity data such as addresses, phone,
that are common for several sheets of that file so i created an entity table
in access DB in order to reduce the redundancy of the data in the DB and thus
its size, like i said above, but i don't know how to import just that columns
to the entity table in access DB... Can someone help me?

Before you start, read Ken Snell's page on this problem:
http://www.accessmvp.com/KDSnell/EXCEL_ImpExp.htm#ImpAllWkshts

Start there and see if any of those solve your problem. If not, post
back...

 

[Peter Hibbs]

If this is a one-off operation then you could have a look at the Excel
to Access Converter Utility at :-
http://www.rogersaccesslibrary.com/...?TID=183&SID=e81a164e2314187z3e8587231e28cb7d

Peter Hibbs.

 

[c_sarmento via AccessMonster.com]

Hello.
First of all i have to say that i'm not only a noob at access or VB, but also

[quoted text clipped - 20 lines]

its size, like i said above, but i don't know how to import just that columns
to the entity table in access DB... Can someone help me?

Before you start, read Ken Snell's page on this problem:
http://www.accessmvp.com/KDSnell/EXCEL_ImpExp.htm#ImpAllWkshts

Start there and see if any of those solve your problem. If not, post
back...

there is code there that will definitively help me to import from that single
excel file to different tables, especially int his one:
http://www.accessmvp.com/KDSnell/EXCEL_ImpExp.htm#ImpAllWktsSepTbl
but a point is still missing: how can i choose the columns i want from that
sheets to be imported?
there are columns that repeat data (are common for several sheets), so i want
to choose between all the ones that really need to be imported...
Or do you think that it would be easier to simply import like this (all
sheets into separate tables) and then write some code to erase the columns i
don't need in access (alfter all sheets are imported), like a filter?
Thank you very much for the atention,

regards,

Carlos Sarmento

 

[c_sarmento via AccessMonster.com]

If this is a one-off operation then you could have a look at the Excel
to Access Converter Utility at :-
http://www.rogersaccesslibrary.com/...?TID=183&SID=e81a164e2314187z3e8587231e28cb7d

Peter Hibbs.

This is an operation that it will be done everytime the users of the DB want
to, so i think your sugestion can't be apllied, am I right?

 

[c_sarmento via AccessMonster.com]

If this is a one-off operation then you could have a look at the Excel
to Access Converter Utility at :-
http://www.rogersaccesslibrary.com/...?TID=183&SID=e81a164e2314187z3e8587231e28cb7d

Peter Hibbs.

This is an operation that it will be done everytime the users of the DB want
to, so i think your sugestion can't be apllied, am I right?

 

[c_sarmento via AccessMonster.com]

Before you start, read Ken Snell's page on this problem:
http://www.accessmvp.com/KDSnell/EXCEL_ImpExp.htm#ImpAllWkshts

Start there and see if any of those solve your problem. If not, post
back...

There is also another problem i'm facing... after importing into separate
tables in the first time, i need to import whenever the user wants to import
from other excel files with the same "template" but instead of creating new
tables, updating the ones that are already in access (the ones that were
filled in first importation), the objective is to "update" by adding new data
in the bottom of each table...

Is this "doable"?

 

[c_sarmento via AccessMonster.com]

If this is a one-off operation then you could have a look at the Excel
to Access Converter Utility at :-
http://www.rogersaccesslibrary.com/...?TID=183&SID=e81a164e2314187z3e8587231e28cb7d

Peter Hibbs.

Also forgot to ask, does that utility works with access 2003 or 2007 and
excel 2003 or 2007?

 

[c_sarmento via AccessMonster.com]

There is also another problem i'm facing... after importing into separate
tables in the first time, i need to import whenever the user wants to import
from other excel files with the same "template" but instead of creating new
tables, updating the ones that are already in access (the ones that were
filled in first importation), the objective is to "update" by adding new data
in the bottom of each table...

Is this "doable"?

How/where do i Put this code in the links?
i'm sorry, i'm a total noob

 

[Peter Hibbs]

Yes it does work with Access 2003 and 2007
and you are correct, it would not really be suitable for regular
imports. You would need to write some code to do that.

Peter Hibbs.

 

[c_sarmento via AccessMonster.com]

Yes it does work with Access 2003 and 2007
and you are correct, it would not really be suitable for regular
imports. You would need to write some code to do that.

Peter Hibbs.
[quoted text clipped - 4 lines]

Also forgot to ask, does that utility works with access 2003 or 2007 and
excel 2003 or 2007?

My work changed now, i will only need to import from an excel file once, so i
supposed i could use your program...
but once i've done this: "double click the Excel-Access Converter 2000.mde
file as normal. The main form will be displayed as shown in Figure 1 below "
the window that is opened isn't the one you referred in your manual, but the
same window that when we open microsoft access.exe

 

[Peter Hibbs]

I have no idea. What version/s of Access have you got installed on
your PC?

You could try the 2003 version at the same Web site if you think it
will make a difference although I don't think it will.

Have you tried opening Access first and then running the Utility from
there?

If you are using A2007, have you saved the database file in a 'Trusted
Location'?

Don't know what else to suggest. I can always send you the .mdb
version if you want to debug it but you should be able to get the .mde
version working.

Peter Hibbs.

Yes it does work with Access 2003 and 2007
and you are correct, it would not really be suitable for regular
imports. You would need to write some code to do that.

Peter Hibbs.

If this is a one-off operation then you could have a look at the Excel
to Access Converter Utility at :-

[quoted text clipped - 4 lines]

Also forgot to ask, does that utility works with access 2003 or 2007 and
excel 2003 or 2007?

My work changed now, i will only need to import from an excel file once, so i
supposed i could use your program...
but once i've done this: "double click the Excel-Access Converter 2000.mde
file as normal. The main form will be displayed as shown in Figure 1 below "
the window that is opened isn't the one you referred in your manual, but the
same window that when we open microsoft access.exe