×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Insert From Excel Recordset to SQL Server table

Insert From Excel Recordset to SQL Server table

Insert From Excel Recordset to SQL Server table

(OP)
Hi,

I am trying to insert values in an excel sheet located on the local computer into Database on a server. This is what I have so far in the excel macro..

Set ConnDB = New ADODB.Connection
Set ConnDB = CreateObject("ADODB.Connection")
    
ConnDB.ConnectionString = "Driver={SQL Server};Server=PLPST1209;User ID=abc;Password=abc123;Database=Customer_Info"
ConnDB.Open

'open xls connection
Set Connxls = New ADODB.Connection
    
Connxls.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=C:\Customer.xls;" & _
        "Extended Properties=Excel 8.0"

'open recordset
Set Rst = Connxls.Execute("select * from [sheet1$]")

I need help Inserting the Excel recordset Rst into table Customer_info on SQL Server? Can I insert the whole recordset at once like "Insert into Customer_info Select * from " & Rst in Excel VBA?

Thanks.


RE: Insert From Excel Recordset to SQL Server table


Nope! You have to loop the reading (forward-only, read-only, server-side) from excel recordset and on each step add a new record to a writing (Static, batch optimistick,  client-side)
recordset to SQL server. At the end, fire UpdateBatch for the writting recordset. Close recordsets + connections and destroy them.

Or "export" the records in a text file and fire a BCP on SQL server.

RE: Insert From Excel Recordset to SQL Server table

(OP)
Thanks JerryKlmns .. The excel sheet has around 50000 rows and my concern is that it wil take a long time to complete from a user perspective ..could I do something like this instead
    
Set cn = New ADODB.Connection
cn.ConnectionString = "Driver={SQL Server};Server=PLPST1209;User ID=abc;Password=abc123;Database=Customer_Info"

cn.Open

StrSQL = "Insert into Customer_Info Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', " & _
    "'Excel 8.0;Database=C:\Customer.xls;HDR=YES', 'SELECT * FROM [SheetName$]')".

cn.Execute StrSQL


The only problem with this is that the File will be located on the users local computer and the database is on a server ... so for the statement
Database=C:\Customer.xls;HDR=YES .. the insert process is looking for an excel file on the server. Is there any way to get around this?

Thanks.

RE: Insert From Excel Recordset to SQL Server table

You need to loop through the rows and execute the insert statement for each row of data 1 at a time so something more like:

dim arrUpload as variant
dim c as range
For each c in Range("UploadRange").columns(1)
 arrUpload = range(cells(c.row,1),cells(c.row,range("Uploadrange").columns.count))

Set cn = New ADODB.Connection
cn.ConnectionString = "Driver={SQL Server};Server=PLPST1209;User ID=abc;Password=abc123;Database=Customer_Info"

strSQL = "Insert into Customer_Info Select " & arrUpload

cn.Execute (strSQL)

set cn = nothing
Next

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244: How to get the best answers before you ask a question

RE: Insert From Excel Recordset to SQL Server table

(OP)

Thanks xlbo ..this seems to be a faster way of doing it... but I get an error when this statement executes

strSQL = "Insert into Customer_Info Select " & arrUpload

ConnDB.Execute (StrSQL)

Any ideas about the strSQL syntax? the error is type mismatch .

RE: Insert From Excel Recordset to SQL Server table

I was giving a rough example, not finished code

Quite possibly, one issue will be the seperators between upload values. There will need to be ' ' around the text elements to upload

Here is an example that I use to update a table in code using ADO:

The following variables are actually passed through to the function but I include them hard coded to help illustrate the syntax used for the INSERT command

CODE

RepName = "Area Overview"
strWhen = "PD3"
strOH = "I13"

strSQL = "INSERT INTO KPIBOBJ_Retrieval_Log ( Report, TimePer, OH, UserID, Retrieved ) SELECT '" & RepName & "', '" & strWhen & "', '" & strOH & "', 'Unretrieved', " & "{ ts '" & Format(Now, "yyyy-mm-dd hh:mm:ss") & "'}"

Rather than using:
arrUpload = range(cells(c.row,1),cells(c.row,range("Uploadrange").columns.count))

Which basically loads the cell values as an array, you may have to create a comma seperated string yourself e.g.

CODE

For each c in Range("UploadRange").columns(1)
 for each rc in Range("UploadRange").rows(c.row)
   strArr = rc.Value & ","
 next
  
  strArr = left(strArr,Len(strArr)-1)

  strSQL = "Insert into Customer_Info (Field1, Field2, Field3, etc) Select " & strArr

The trick will be in creating the correct SQL string to do the INSERT. The loop is pretty basic...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244: How to get the best answers before you ask a question

RE: Insert From Excel Recordset to SQL Server table

(OP)
Thanks xlbo .. I created the sql string and it inserts a few rows .. but I ran into tricky situation ..some of the cells in excel have the character ' in them.. this is messing up the string completely... I am out of ideas ... have you run into this situation before.?

RE: Insert From Excel Recordset to SQL Server table

You need to replace them with either '' or '' (can't remember which off the top of my head. So:

CODE

if instr(rc.Value,"'")<> 0 then
 strArr = replace(rc.Value,"'","''") & ","
else
 strArr = rc.Value & ","
end if

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244: How to get the best answers before you ask a question

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close