473,890 Members | 1,971 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

Nov 13 '05 #1
11 11302
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 GetDataFromExce l()
Dim dbsTemp As Database
Dim tdfLinked As TableDef

Set dbsTemp = CurrentDb
Set tdfLinked = dbsTemp.CreateT ableDef("tblShe et1")
tdfLinked.Conne ct = _
"Excel 5.0;HDR=YES;IME X=2;DATABASE=C: \Dir1\Book1.xls "
tdfLinked.Sourc eTableName = "Sheet1$"
dbsTemp.TableDe fs.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.x ls 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;IME X=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 GetDataFromExce l2()
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("tblSheet 1", "tblSheet2" , "tblSheet3" )
arrTblsAcc = Array("tbl1", "tbl2", "tbl3")
Set dbsTemp = CurrentDb
For i = 0 To Ubound(arrShts)
Set tdfLinked = dbsTemp.CreateT ableDef(arrTbls Xls(i))
tdfLinked.Conne ct = _
"Excel 5.0;HDR=YES;IME X=2;DATABASE=C: \Dir1\Book1.xls "
tdfLinked.Sourc eTableName = arrShts(i)
dbsTemp.TableDe fs.Append tdfLinked
DoCmd.RunSql "Select * Into " & arrTblsAcc(i) & " From " & arrTblsXls
End Sub
--------------------------------------------------

HTH
Rich

*** Sent via Developersdex http://www.developersdex.com ***
Nov 13 '05 #2
I left out the Next in the For Loop.

--method 2 multiple Excel sheets to multiple Access Tables
Sub GetDataFromExce l2()
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("tblSheet 1", "tblSheet2" , "tblSheet3" )
arrTblsAcc = Array("tbl1", "tbl2", "tbl3")
Set dbsTemp = CurrentDb
For i = 0 To Ubound(arrShts)
Set tdfLinked = dbsTemp.CreateT ableDef(arrTbls Xls(i))
tdfLinked.Conne ct = _
"Excel 5.0;HDR=YES;IME X=2;DATABASE=C: \Dir1\Book1.xls "
tdfLinked.Sourc eTableName = arrShts(i)
dbsTemp.TableDe fs.Append tdfLinked
DoCmd.RunSql "Select * Into " & arrTblsAcc(i) & " From " & arrTblsXls
Next
End Sub

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Nov 13 '05 #3
"Hi5" <fk************ ***@yahoo.com> wrote in
news:11******** **************@ z14g2000cwz.goo glegroups.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
Nov 13 '05 #4
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

Nov 13 '05 #5
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

Nov 13 '05 #6
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

Nov 13 '05 #7
"Hi5" <fk************ ***@yahoo.com> wrote in
news:11******** *************@z 14g2000cwz.goog legroups.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
Nov 13 '05 #8
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

Nov 13 '05 #9
"Hi5" <fk************ ***@yahoo.com> wrote in
news:11******** **************@ z14g2000cwz.goo glegroups.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
Nov 13 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
1833
by: MikeY | last post by:
Hi everyone, Using C#, Windows forms. I am trying to learn how to modify existing data with in MSDE table/fields. If anyone could help me out with my code, I would appreciate it. My code is as follows: private void btnEmpModify_Click(object sender, System.EventArgs e) { DataTable thisTable = dsModifyRecord.Tables; //THIS IS WHERE I'M GETTING LOST
5
7051
by: SSP | last post by:
Dear ASP.NETers, How would I insert multiple rows of data from a web form? Are there any tute's and stuff around. Couldn't find any myself. Thanks in advance. SSP
1
1706
by: mhnazly | last post by:
i'm trying to read data from SQL Server database using data reader and assigned it to a label in my asp.net web application. but when the button is clicked, nothing appears. please help, thanks. Private Sub btnTesting_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnTesting.Click Dim cn As New SqlClient.SqlConnection Dim cm As New SqlClient.SqlCommand
3
1243
by: John | last post by:
Hi I have two data adapters bound to two separate tables. How can I; 1. Loop through all records one by one in one of them while reading column values, and 2. Insert a record from data adapter A into data adapter B via code?
20
14418
by: technocraze | last post by:
Hi guys & commnunity experts, Does anyone knw how to go about checking for existing data in an MS Acess table? I have tried out the following code using vb but doesnt seem to work that well? Can anyone ps take a look at this code / logic and if possible point out the error or make correction? What i need is to check for existing studentId and name before insertion. If the inserted value existed in the table, message "duplicate" else...
2
2443
by: Gary42103 | last post by:
Hi I need Perl Script to do Data Parsing using existing data files. I have my existing data files in the following directory: Directory Name: workfs/ams Data File Names: 20070504.dat, 20070503.dat, 20070502.dat In each of above data files there will be some millions of records. So my job is read those data files and also read first 3 letters of each record in all above data files and write into new data files.For example
1
2771
by: Luqman | last post by:
I have created a Insert Query in Sql Data Source using Oracle Database, with the parameters, and its connected with DetailView Control. When I try to Insert through DetailView Control, Illegal Variable Name/Number Error occurs. Is there any way I can trace or display on screen, what exactly the query is being passed to the Oracle with the Values. For example: If my Sql datasource has following query.
1
1834
by: san1014 | last post by:
Hi........ I am Using Oracle 10g, I want to insert bulk amount of data into the database. How can i do this Using Sql * Loader? (Tell me individually for both sequntial data and Random Data) Thanks in Adv........ Sandeep
13
32732
by: chromis | last post by:
Hi, I have a query which updates the projects table of my database, however when I try to run my query with blank values i get the following error: Data truncation: Data truncated for column 'date' at row 1 I have done some researching and I believe the problem is to do with the data that is being sent by my cfqueryparam function. Here is my create function:
2
2851
by: kostasgio | last post by:
Hello, this is my first post here, i hope i'll find this forum usefull. Although i did a search about my question, i didnt find what i need , because the question isnt exactly what it sounds. Here's my scenario I have a table that doesnt have an identity column, but does have unique rows depending on a combination of two columns. the table's name is carowner and the two columns i am talking about are iteid,cusid and they are both of...
0
11212
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10799
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10446
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9614
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
8004
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7154
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5832
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4655
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4255
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.