SQL Server -- Bulk Insert from Excel to SQL Server

D

Doctorjones_md

I'm trying to export data from an Excel worksheet into SQL Server. I've
heard that Bulk Insert or bcp might be the way to go, but I don't know
anything about how to go about setting that up. If this would be the most
efficient method for transfering data from Excel to SQL, could some please
point me in the right direction for modifying my existing code (Shown
Below).

I recently attempted to follow someones suggestion to used Stored
Procedures -- my 1st attempt at writing these .... The latest code that
I've tried using gave me the following error:
"Runtime error '3001'
Arguments are of the wrong type, are out of acceptable range, or are in
conflict with one another.

Any ideas into what's causing the error? Could it be a conflict in data
types that's causing this?

Here's the code I was running:
====================================
1. The following Stored Procedure on SQL Server
==================================
create procedure dbo.usp_Insert_Upload_Specific
@Loc vchar(5),
@PType vchar(5),
@Quant integer,
@PName vchar(25),
@Style vchar(5),
@Features vchar(25)

AS

INSERT INTO Upload_Specific
(
Location,
[Product Type],
Quantity,
[Product Name],
Style,
Features
)
VALUES
(
@Loc,
@PType,
@Quant,
@PName,
@Style,
@Features,
)

GO

1. The following VBA code in Excel Module
===============================

Sub InsertData()
Dim oConn As Object
Dim sSQL As String
Application.ScreenUpdating = False
Set wsSheet = ActiveWorkbook.Sheets("Product Tracking")
Set oConn = CreateObject("ADODB.Connection")
oConn.Open = "Provider=sqloledb;" & _
"Data Source=xx.x.xx.xx;" & _
"Initial Catalog=xxx_xxx;" & _
"User Id=xxxx;" & _
"Password=xxxx"

' NEW CODE HERE
Dim intParams As Integer
Dim objCmd As New ADODB.Command

' Connect the Command object to the data source.
objCmd.ActiveConnection = objConn

' Set CommandText equal to the stored procedure name.
objCmd.CommandText = "dbo.usp_Insert_Upload_Specific"
objCmd.CommandType = adCmdStoredProc

' Automatically fill in parameter info from stored procedure.
objCmd.Parameters.Refresh

' Get the count of required parameters SHOULD BE 20
intParams = objCmd.Parameters.Count - 1 'first one is RETURN value

DIM rng as Range
DIM ccell as Range
set rng = range("A2:T20") ' change this range to include all your data
For Each ccell in rng
' call the stored procedure

for x = 1 to intParams
objCmd(x) = ccell.offset(0,x-1)
next x

' now that all the parameters have been assigned values
' execute the query
objCmd.execute

next ccell

'close the connection
oConn.Close
Set oConn = Nothing
End Sub
==================
Initially, I tried this code (which effectively does the following):

1. Deletes all rows having a value of "0" in column C
2. Uploads the data in Row 2 to my SQL Server

What I need for the code to do is to upload all rows on the worksheet --
how would I modify the code to upload all rows, or iterate on each row
having data?

Here's my code:
========================
Private Sub DeleteBlankRows()

Dim lastrow As Long
Dim r As Long
lastrow = Range("C" & Rows.Count).End(xlUp).Row
For r = lastrow To 2 Step -1
If Application.CountIf(Cells(r, "C").Resize(1, 1), 0) = 1 Then
ActiveSheet.Rows(r).Delete
End If
Next

End Sub

Sub InsertData()
Dim oConn As Object
Dim sSQL As String
Application.ScreenUpdating = False
Set wsSheet = ActiveWorkbook.Sheets("Product Tracking")
Set oConn = CreateObject("ADODB.Connection")
oConn.Open = "Provider=sqloledb;" & _
"Data Source=xx.x.xx.xx;" & _
"Initial Catalog=xxx_xxx;" & _
"User Id=xxxx;" & _
"Password=xxxx"
sSQL = "INSERT INTO Upload_Specific " & _
"([Location], [Product Type], [Quantity], [Product Name], [Style],
[Features]) " & _
" VALUES ('" & Range("A2").Value & "', '" & Range("B2").Value & "', '"
&
Range("C2").Value & "', '" & Range("D2").Value & "', '" &
Range("E2").Value
& "', '" & _
Range("F2").Value & "')"
oConn.Execute sSQL
oConn.Close
Set oConn = Nothing
End Sub

Thanks in advance.
 
R

Robin Hammond

An alternative approach would be to get an empty recordset with the correct
fields set against the table in question (select top 0 * FROM my_table),
populate the recordset with your new values, then do an updatebatch on the
rs. This assumes that there would be no conflict between old and new data of
course.

--
Robin Hammond
www.enhanceddatasystems.com


Doctorjones_md said:
I'm trying to export data from an Excel worksheet into SQL Server. I've
heard that Bulk Insert or bcp might be the way to go, but I don't know
anything about how to go about setting that up. If this would be the most
efficient method for transfering data from Excel to SQL, could some please
point me in the right direction for modifying my existing code (Shown
Below).

I recently attempted to follow someones suggestion to used Stored
Procedures -- my 1st attempt at writing these .... The latest code that
I've tried using gave me the following error:
"Runtime error '3001'
Arguments are of the wrong type, are out of acceptable range, or are in
conflict with one another.

Any ideas into what's causing the error? Could it be a conflict in data
types that's causing this?

Here's the code I was running:
====================================
1. The following Stored Procedure on SQL Server
==================================
create procedure dbo.usp_Insert_Upload_Specific
@Loc vchar(5),
@PType vchar(5),
@Quant integer,
@PName vchar(25),
@Style vchar(5),
@Features vchar(25)

AS

INSERT INTO Upload_Specific
(
Location,
[Product Type],
Quantity,
[Product Name],
Style,
Features
)
VALUES
(
@Loc,
@PType,
@Quant,
@PName,
@Style,
@Features,
)

GO

1. The following VBA code in Excel Module
===============================

Sub InsertData()
Dim oConn As Object
Dim sSQL As String
Application.ScreenUpdating = False
Set wsSheet = ActiveWorkbook.Sheets("Product Tracking")
Set oConn = CreateObject("ADODB.Connection")
oConn.Open = "Provider=sqloledb;" & _
"Data Source=xx.x.xx.xx;" & _
"Initial Catalog=xxx_xxx;" & _
"User Id=xxxx;" & _
"Password=xxxx"

' NEW CODE HERE
Dim intParams As Integer
Dim objCmd As New ADODB.Command

' Connect the Command object to the data source.
objCmd.ActiveConnection = objConn

' Set CommandText equal to the stored procedure name.
objCmd.CommandText = "dbo.usp_Insert_Upload_Specific"
objCmd.CommandType = adCmdStoredProc

' Automatically fill in parameter info from stored procedure.
objCmd.Parameters.Refresh

' Get the count of required parameters SHOULD BE 20
intParams = objCmd.Parameters.Count - 1 'first one is RETURN value

DIM rng as Range
DIM ccell as Range
set rng = range("A2:T20") ' change this range to include all your data
For Each ccell in rng
' call the stored procedure

for x = 1 to intParams
objCmd(x) = ccell.offset(0,x-1)
next x

' now that all the parameters have been assigned values
' execute the query
objCmd.execute

next ccell

'close the connection
oConn.Close
Set oConn = Nothing
End Sub
==================
Initially, I tried this code (which effectively does the following):

1. Deletes all rows having a value of "0" in column C
2. Uploads the data in Row 2 to my SQL Server

What I need for the code to do is to upload all rows on the worksheet --
how would I modify the code to upload all rows, or iterate on each row
having data?

Here's my code:
========================
Private Sub DeleteBlankRows()

Dim lastrow As Long
Dim r As Long
lastrow = Range("C" & Rows.Count).End(xlUp).Row
For r = lastrow To 2 Step -1
If Application.CountIf(Cells(r, "C").Resize(1, 1), 0) = 1 Then
ActiveSheet.Rows(r).Delete
End If
Next

End Sub

Sub InsertData()
Dim oConn As Object
Dim sSQL As String
Application.ScreenUpdating = False
Set wsSheet = ActiveWorkbook.Sheets("Product Tracking")
Set oConn = CreateObject("ADODB.Connection")
oConn.Open = "Provider=sqloledb;" & _
"Data Source=xx.x.xx.xx;" & _
"Initial Catalog=xxx_xxx;" & _
"User Id=xxxx;" & _
"Password=xxxx"
sSQL = "INSERT INTO Upload_Specific " & _
"([Location], [Product Type], [Quantity], [Product Name], [Style],
[Features]) " & _
" VALUES ('" & Range("A2").Value & "', '" & Range("B2").Value & "', '"
&
Range("C2").Value & "', '" & Range("D2").Value & "', '" &
Range("E2").Value
& "', '" & _
Range("F2").Value & "')"
oConn.Execute sSQL
oConn.Close
Set oConn = Nothing
End Sub

Thanks in advance.
Click to expand...
 
J

Joe Yong

Why not just use DTS (SQL Server 2000) or SSIS (SQL Server 2005)?

Both have a wizard interface to let you specify source (Excel) and target
(SQL Server). You can also do interesting things to the data/column-mapping
in the process.



Doctorjones_md said:
I'm trying to export data from an Excel worksheet into SQL Server. I've
heard that Bulk Insert or bcp might be the way to go, but I don't know
anything about how to go about setting that up. If this would be the most
efficient method for transfering data from Excel to SQL, could some please
point me in the right direction for modifying my existing code (Shown
Below).

I recently attempted to follow someones suggestion to used Stored
Procedures -- my 1st attempt at writing these .... The latest code that
I've tried using gave me the following error:
"Runtime error '3001'
Arguments are of the wrong type, are out of acceptable range, or are in
conflict with one another.

Any ideas into what's causing the error? Could it be a conflict in data
types that's causing this?

Here's the code I was running:
====================================
1. The following Stored Procedure on SQL Server
==================================
create procedure dbo.usp_Insert_Upload_Specific
@Loc vchar(5),
@PType vchar(5),
@Quant integer,
@PName vchar(25),
@Style vchar(5),
@Features vchar(25)

AS

INSERT INTO Upload_Specific
(
Location,
[Product Type],
Quantity,
[Product Name],
Style,
Features
)
VALUES
(
@Loc,
@PType,
@Quant,
@PName,
@Style,
@Features,
)

GO

1. The following VBA code in Excel Module
===============================

Sub InsertData()
Dim oConn As Object
Dim sSQL As String
Application.ScreenUpdating = False
Set wsSheet = ActiveWorkbook.Sheets("Product Tracking")
Set oConn = CreateObject("ADODB.Connection")
oConn.Open = "Provider=sqloledb;" & _
"Data Source=xx.x.xx.xx;" & _
"Initial Catalog=xxx_xxx;" & _
"User Id=xxxx;" & _
"Password=xxxx"

' NEW CODE HERE
Dim intParams As Integer
Dim objCmd As New ADODB.Command

' Connect the Command object to the data source.
objCmd.ActiveConnection = objConn

' Set CommandText equal to the stored procedure name.
objCmd.CommandText = "dbo.usp_Insert_Upload_Specific"
objCmd.CommandType = adCmdStoredProc

' Automatically fill in parameter info from stored procedure.
objCmd.Parameters.Refresh

' Get the count of required parameters SHOULD BE 20
intParams = objCmd.Parameters.Count - 1 'first one is RETURN value

DIM rng as Range
DIM ccell as Range
set rng = range("A2:T20") ' change this range to include all your data
For Each ccell in rng
' call the stored procedure

for x = 1 to intParams
objCmd(x) = ccell.offset(0,x-1)
next x

' now that all the parameters have been assigned values
' execute the query
objCmd.execute

next ccell

'close the connection
oConn.Close
Set oConn = Nothing
End Sub
==================
Initially, I tried this code (which effectively does the following):

1. Deletes all rows having a value of "0" in column C
2. Uploads the data in Row 2 to my SQL Server

What I need for the code to do is to upload all rows on the worksheet --
how would I modify the code to upload all rows, or iterate on each row
having data?

Here's my code:
========================
Private Sub DeleteBlankRows()

Dim lastrow As Long
Dim r As Long
lastrow = Range("C" & Rows.Count).End(xlUp).Row
For r = lastrow To 2 Step -1
If Application.CountIf(Cells(r, "C").Resize(1, 1), 0) = 1 Then
ActiveSheet.Rows(r).Delete
End If
Next

End Sub

Sub InsertData()
Dim oConn As Object
Dim sSQL As String
Application.ScreenUpdating = False
Set wsSheet = ActiveWorkbook.Sheets("Product Tracking")
Set oConn = CreateObject("ADODB.Connection")
oConn.Open = "Provider=sqloledb;" & _
"Data Source=xx.x.xx.xx;" & _
"Initial Catalog=xxx_xxx;" & _
"User Id=xxxx;" & _
"Password=xxxx"
sSQL = "INSERT INTO Upload_Specific " & _
"([Location], [Product Type], [Quantity], [Product Name], [Style],
[Features]) " & _
" VALUES ('" & Range("A2").Value & "', '" & Range("B2").Value & "', '"
&
Range("C2").Value & "', '" & Range("D2").Value & "', '" &
Range("E2").Value
& "', '" & _
Range("F2").Value & "')"
oConn.Execute sSQL
oConn.Close
Set oConn = Nothing
End Sub

Thanks in advance.
Click to expand...
 
M

Mike C#

Joe Yong said:
Why not just use DTS (SQL Server 2000) or SSIS (SQL Server 2005)?

Both have a wizard interface to let you specify source (Excel) and target
(SQL Server). You can also do interesting things to the
data/column-mapping in the process.
Click to expand...

From the look of his other posts, it appears he wants to include this as a
VBScript or macro directly in his Excel spreadsheet.
 
D

Doctorjones_md

Mike,

Thank's for your previous assistance (your suggestion was
right-on-the-money) -- in this instance, I need for the data import to be
run from the Excel workbook (as normal users won't have SQL Server
Management Studio on their workstations. I ended up using Bernie Dietrick's
suggestion -- which does what I needed it to do:
============
Here's the code:
sSQL = "INSERT INTO Upload_Specific " & _
"([Location], [Product Type], [Quantity], [Product Name], [Style],
[Features]) " & _
" VALUES ('" & Range("A2").Value & "', '" & Range("B2").Value & "', '"
&
Range("C2").Value & "', '" & Range("D2").Value & "', '" &
Range("E2").Value
& "', '" & _
Range("F2").Value & "')"
oConn.Execute sSQL

to

For i = 2 To Range("A65536").End(xlUp).Row
sSQL = "INSERT INTO Upload_Specific " & _
"([Location], [Product Type], [Quantity], [Product Name], [Style],
[Features]) " & _
" VALUES ('" & Range("A"&i).Value & "', '" & Range("B"&i).Value & "',
'" & _
Range("C"&i).Value & "', '" & Range("D"&i).Value & "', '" & _
Range("E"&i).Value & "', '" & _
Range("F"&i).Value & "')"
oConn.Execute sSQL
Next i

HTH,
Bernie
MS Excel MVP
Click to expand...
 
M

Mike C#

Doctorjones_md said:
Mike,

Thank's for your previous assistance (your suggestion was
right-on-the-money) -- in this instance, I need for the data import to be
run from the Excel workbook (as normal users won't have SQL Server
Management Studio on their workstations. I ended up using Bernie
Dietrick's suggestion -- which does what I needed it to do:
Click to expand...

Glad you found a solution Doc. You might want to revisit this solution at a
later date and parameterize it, since theoretically as it stands now a user
could enter something like the following in cell F2:

'); DELETE FROM Upload_Specific; --

And wipe out your whole table. Or something even worse (with appropriate
permissions), like

'); DROP TABLE table_name; --

Or some such and really throw a wrench in the works.

Best luck!

"... the natural motion of the earth as a whole, like that of its parts, is
towards the center of the Universe: that is the reason why it is now lying
at the center. ... " - That Genius Aristotle
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

For/Next loop to process INSERT INTO statement 4
Excel VBA Question 0
Help needed! Extracting Heading 3 and Contents from a Word Doc to Excel 0
Data Upload from Excel to SQL Server 3
commandbutton does not answer 0
Pasting a cell into bookmarked table from excel vba 1
how to code a button to export results of stored procedure to excel file 0
Access to Excel format 2

Top