How to insert bulk data into an existing data base?

[Hi5]

Hi,

I am new to access I usedto work in Oracle and Mysql.

I am after a way that enables me to populate a database I designed in
access with lots of data which can be sorted in excel sheets,

The source of all data is a very large size (100*2000) excel spread
sheet In which some data types are absulotely Irelevant to access data
types in my DB there fore I changed It to a number of excel sheets
which are relevant to my DB.

Is there any known solution for this?

Many thanks

[Rich P]

I will show you two methods for this. The first methods only retrieves
data from one Excel sheet to one Access table. The second method uses
array so that you can retrieve data from multiple Excel Sheets and
populate multiple Access tables.
----------------------------------------------------
'--method 1 -- one Excel Sheet to one Access Table
Sub GetDataFromExcel()
Dim dbsTemp As Database
Dim tdfLinked As TableDef

Set dbsTemp = CurrentDb
Set tdfLinked = dbsTemp.CreateTableDef("tblSheet1")
tdfLinked.Connect = _
"Excel 5.0;HDR=YES;IMEX=2;DATABASE=C:\Dir1\Book1.xls"
tdfLinked.SourceTableName = "Sheet1$"
dbsTemp.TableDefs.Append tdfLinked
DoCmd.RunSql "Select * Into tbl1 From tblSheet1"
End Sub
----------------------------------------------------

Basically you are connecting to a respective Excel sheet using the
connection string here (replace C:\Dir1\Book1.xls with the actual path
to your Excel Workbook). Oh, and HDR=YES means the first row in the
Excel sheet is a header row.

"Excel 5.0;HDR=YES;IMEX=2;DATABASE=C:\Dir1\Book1.xls"

This same connection string works with Excel97 or Excel2000 or
Excel2002. Note: when you reference your Excel Sheet name ("Sheet1" in
my example) you have to append a $ sign to it. The actual sheet name is
"Sheet1" (or whatever your sheet name is). You append the $ and in the
code above (and below) you are now referencing "Sheet1$". Once you
have connected to your Excel sheet you can use a simple Select * Into...
statement to retrieve the Excel data from your connected Excel table
into a native Access table.

In method 2 I am using an array of Excel Sheets and 2 arrays of Access
tables. Then I loop through the arrays to get data from multiple
sheets.

----------------------------------------------------
'--method 2 multiple Excel sheets to multiple Access Tables
Sub GetDataFromExcel2()
Dim dbsTemp As Database
Dim tdfLinked As TableDef, i As Integer
Dim arrShts As Variant, arrTblsXls As Variant
Dim arrTblsAcc As Variant

arrShts = Array("Sheet1$", "Sheet2$", "Sheet3$")
arrTblsXls = Array("tblSheet1", "tblSheet2", "tblSheet3")
arrTblsAcc = Array("tbl1", "tbl2", "tbl3")
Set dbsTemp = CurrentDb
For i = 0 To Ubound(arrShts)
Set tdfLinked = dbsTemp.CreateTableDef(arrTblsXls(i))
tdfLinked.Connect = _
"Excel 5.0;HDR=YES;IMEX=2;DATABASE=C:\Dir1\Book1.xls"
tdfLinked.SourceTableName = arrShts(i)
dbsTemp.TableDefs.Append tdfLinked
DoCmd.RunSql "Select * Into " & arrTblsAcc(i) & " From " & arrTblsXls
End Sub
--------------------------------------------------

HTH
Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Rich P]

I left out the Next in the For Loop.

--method 2 multiple Excel sheets to multiple Access Tables
Sub GetDataFromExcel2()
Dim dbsTemp As Database
Dim tdfLinked As TableDef, i As Integer
Dim arrShts As Variant, arrTblsXls As Variant
Dim arrTblsAcc As Variant

arrShts = Array("Sheet1$", "Sheet2$", "Sheet3$")
arrTblsXls = Array("tblSheet1", "tblSheet2", "tblSheet3")
arrTblsAcc = Array("tbl1", "tbl2", "tbl3")
Set dbsTemp = CurrentDb
For i = 0 To Ubound(arrShts)
Set tdfLinked = dbsTemp.CreateTableDef(arrTblsXls(i))
tdfLinked.Connect = _
"Excel 5.0;HDR=YES;IMEX=2;DATABASE=C:\Dir1\Book1.xls"
tdfLinked.SourceTableName = arrShts(i)
dbsTemp.TableDefs.Append tdfLinked
DoCmd.RunSql "Select * Into " & arrTblsAcc(i) & " From " & arrTblsXls
Next
End Sub

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[David W. Fenton]

"Hi5" <fk***************@yahoo.com> wrote in
news:11**********************@z14g2000cwz.googlegr oups.com:

I am new to access I usedto work in Oracle and Mysql.

I am after a way that enables me to populate a database I designed
in access with lots of data which can be sorted in excel sheets,

The source of all data is a very large size (100*2000) excel
spread sheet In which some data types are absulotely Irelevant to
access data types in my DB there fore I changed It to a number of
excel sheets which are relevant to my DB.

Is there any known solution for this?

It really doesn't matter what format the source data is in (Excel,
delimited file, Word merge data file) -- what you seem to be
describing is a case where you are trying to import a denormalized
(i.e., flat) data set into a normalized data structure (i.e.,
multiple tables that get rid of the duplicated data).

The basic method for this that I use is:

1. import the source data file into a single Access data table. Give
it an Autonumber PK. Call it ImportID.

2. add ImportID as a field to your top-level table in your
normalized data structure.

3. create a DISTINCT query that includes the columns for the
top-level table and include the ImportID field. Append those records
to the top-level table.

4. walking your way down the hierarchy of your data structure,
create a DISTINCT query for each of your sub-tables, linked by
ImportID to the top-level table. This way, you can link the child
records to the PK in your new structure via the old ImportID.

Now, if you have multiple levels of child data, you'll need to apply
the ImportID to another level down the hiearchy. This means that you
may need to create an intermediary table with a second set of import
PKs for middle levels of your data structure.

But the concept is the same. The key is that you need to be able to
link back to the denormalized data to get the link to the new PK
value.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Hi5]

Hi Rich,
Many Thanks foe the answer, now there is a couple of questions :

1-What is the language used for the above lines ?

2-How can I find the compiler for it?

3-How to run the codes?

I sloud think it sounds stupid , but it is like that for me and I
supose for some other Oracle,Mysql developers.

Many thanks

Kindest regards

[Bas Cost Budde]

Hi5 wrote:

1-What is the language used for the above lines ?
Visual Basic for Applications
2-How can I find the compiler for it?
built-in with Access (and dialects in the rest of the Office family)
3-How to run the codes?
Either invoked "directly" from the debug window, or inside an event
procedure for a button (probably)
I sloud think it sounds stupid , but it is like that for me and I
supose for some other Oracle,Mysql developers.

Kol hatchil kashe. I mean, you have to start somewhere, and the
surroundings are fairly new right?

Have you got time to read through some Help? I don't know quick start
articles, and am inclined to think that quick start books are either too
wordy or too simple to be of fast use to experienced developers.

Well, maybe just keep posting questions. Keeps us off the street anyway :-)
--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html

[Hi5]

Hi David,

Many Thanks for your solution, It seems very clever !

How should I structure the query?

Is there any exaple?

Many thanks

Kindest regards

[David W. Fenton]

"Hi5" <fk***************@yahoo.com> wrote in
news:11*********************@z14g2000cwz.googlegro ups.com:

How should I structure the query?

Is there any exaple?

Well, the top-level query will include the fields you want in the
top-level table, along with the ImportID, and you'll go to the query
properties and set UNIQUE VALUES to YES.

You can then convert the query from a SELECT to a MAKETABLE query
and that will create a table with the unique records.

The rest should be self-explanatory, once you know how to do that.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Hi5]

Dear David,

I am knid of lost from 3rd step could you please be more specific in
the following?

1- DISTINCT query? what is it? and how to create one?

2- What are the top level tables and other levels of tables with regard
to the table Excelsheet and the other tables in the databse?

3- How to append data with regard to data structure ?

4- What are the subtables?

I would be grateful if you could make theose more clearer that I could
understand.

Many thanks

Kindest Regards

Hi5

[David W. Fenton]

"Hi5" <fk***************@yahoo.com> wrote in
news:11**********************@z14g2000cwz.googlegr oups.com:

I am knid of lost from 3rd step could you please be more specific
in the following?

1- DISTINCT query? what is it? and how to create one?
A DISTINCT query gives you all the unique rows in a query result.

If a regular SELECT gives you:

EXAMPLE 1
NAME CITY STATE
John Smith New York NY
John Smith New York NY
John Smith St. Louis MO
Jane Doe Las Vegas NV
Jane Doe Las Vegas NV

A DISTINCT query will give you:

EXAMPLE 2
NAME CITY STATE
John Smith New York NY
John Smith St. Louis MO
Jane Doe Las Vegas NV

Since you're working from data that has repeating fields, as in Ex.
1, you need to eliminate the duplicates from the subset of data that
you're inserting into the top-level table.

The SQL statements would look like this:

EXAMPLE 1a
SELECT tblPerson.NAME, tblPerson.CITY, tblPerson.State
FROM tblPerson;

EXAMPLE 2a
SELECT DISTINCT tblPerson.NAME, tblPerson.CITY, tblPerson.State
FROM tblPerson;

Now, if you're not comfortable working in the SQL window, just view
the query's properties and set UNIQUE VALUES to YES (you'd think
UNIQUE RECORDS would be what you want, but it's actually not -- that
would give you a DISTINCTROW query, which is specific to Access and
useful for a completely different purpose).
2- What are the top level tables and other levels of tables with
regard to the table Excelsheet and the other tables in the
databse?
You tell me! I can't say, because I know nothing about the natural
structure of your data.
3- How to append data with regard to data structure ?
Once you have the tables designed, you have to append the data with
APPEND queries.
4- What are the subtables?
The tables hierarchically related to the top-level tables.
I would be grateful if you could make theose more clearer that I
could understand.

I strongly suggest you hire someone with experience. You clearly
don't have enough knowledge to do this without major handholding,
and that's not something I'm willing to do.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Hi5]

Hi,

Although the language used still is not clear, and it is a bit
offensive in the end final lines.
but many thanks for reading and answering the questions.

I am strongly intended to do it on my own because it is a charity that
I took the resposibility to design the database for and I am learning
this Stupid MS package and its abilities and the funny names used for
simplest things!!!

I have done all these before in Pure Oracle SQL on Unix machines.

Anyway thanks for your friendly comments on my knowledge and
abilities!!!!!!

But I sure am willing to do that handholding for anyone with problems
on Webdesign, XHTml, CSS, XML, Perl, CGI, Javascript.

Cheers!!!

[Hi5]

Hi,

Although the language used still is not clear, and it is a bit
offensive in the final lines.
but many thanks for reading and answering the questions.
I am strongly intended to do it on my own because it is a charity that
I took the resposibility to design the database for and I am learning
this Stupid MS package and its abilities and the funny names used for
simplest things!!!

I have done all these before in Pure Oracle SQL on Unix machines.

Anyway thanks for your friendly comments on my knowledge and
abilities!!!!!!

But I sure am willing to do that handholding for anyone with problems
on Webdesign, XHTml, CSS, XML, Perl, CGI, Javascript.

Cheers!!!