Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.sqlserver.datamining,microsoft.public.sqlserver.datawarehouse
|
|||
|
|||
SQL Server -- Bulk Insert from Excel to SQL Server
Hi, for bulk insert, I guess, you have to export your excel data as a TAB
separated text file and then try using SQL Server's bcp utility to choose the table into which you want to insert the data and the data file. This should take care of the issue. For further details you can look into SQL Server's help documentation on bulk insert or bcp. "Doctorjones_md" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Running Excel 2000 VBA Application on Excel 2003 | Excel Worksheet Functions | |||
How do I insert AutoText in an Excel worksheet? | Excel Discussion (Misc queries) | |||
TRYING TO SET UP EXCEL SPREADSHEET ON MY COMPUTER | New Users to Excel | |||
i want to connect excel with sql server 2000 as database with macr | Excel Discussion (Misc queries) | |||
Challenging Charting | Charts and Charting in Excel |