Insert From Excel Recordset to SQL Server table 1

[AccessHelp123]

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.

 

[JerryKlmns]

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.

 

[AccessHelp123]

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.

 

[xlbo]

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 before you ask a question

 

[AccessHelp123]

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 .

 

[xlbo]

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

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.

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 before you ask a question

 

[AccessHelp123]

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.?

 

[xlbo]

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

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 before you ask a question