Writing Data Back to SQL from Excel Sheet

Writing data back to SQL from Excel sheet

If you want to have the Excel file do all of the work (retrieve from DB; manipulate; update DB) then you could look at ActiveX Data Objects (ADO). You can get an overview at:

http://msdn.microsoft.com/en-us/library/ms680928(VS.85).aspx

How to update Sql table from excel directly?

There are many ways to do this. I'd recommend something like this, to push data from Excel to SQL Server.

Sub ButtonClick()
'TRUSTED CONNECTION
On Error GoTo errH

Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strPath As String
Dim intImportRow As Integer
Dim strFirstName, strLastName As String

Dim server, username, password, table, database As String


With Sheets("Sheet1")

server = .TextBox1.Text
table = .TextBox4.Text
database = .TextBox5.Text


If con.State <> 1 Then

con.Open "Provider=SQLOLEDB;Data Source=" & server & ";Initial Catalog=" & database & ";Integrated Security=SSPI;"
'con.Open

End If
'this is the TRUSTED connection string

Set rs.ActiveConnection = con

'delete all records first if checkbox checked
If .CheckBox1 Then
con.Execute "delete from tbl_demo"
End If

'set first row with records to import
'you could also just loop thru a range if you want.
intImportRow = 10

Do Until .Cells(intImportRow, 1) = ""
strFirstName = .Cells(intImportRow, 1)
strLastName = .Cells(intImportRow, 2)

'insert row into database
con.Execute "insert into tbl_demo (firstname, lastname) values ('" & strFirstName & "', '" & strLastName & "')"

intImportRow = intImportRow + 1
Loop

MsgBox "Done importing", vbInformation

con.Close
Set con = Nothing

End With

Exit Sub

errH:
MsgBox Err.Description
End Sub

You can also try this, which uses a Where Clause.

Sub InsertInto()

'Declare some variables
Dim cnn As adodb.Connection
Dim cmd As adodb.Command
Dim strSQL As String

'Create a new Connection object
Set cnn = New adodb.Connection

'Set the connection string
cnn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=Northwind;Data Source=Excel-PC\SQLEXPRESS"
'cnn.ConnectionString = "DRIVER=SQL Server;SERVER=Excel-PC\SQLEXPRESS;DATABASE=Northwind;Trusted_Connection=Yes"

'Create a new Command object
Set cmd = New adodb.Command

'Open the Connection to the database
cnn.Open

'Associate the command with the connection
cmd.ActiveConnection = cnn

'Tell the Command we are giving it a bit of SQL to run, not a stored procedure
cmd.CommandType = adCmdText

'Create the SQL
strSQL = "UPDATE TBL SET JOIN_DT = '2013-01-22' WHERE EMPID = 2"

'Pass the SQL to the Command object
cmd.CommandText = strSQL

'Execute the bit of SQL to update the database
cmd.Execute

'Close the connection again
cnn.Close

'Remove the objects
Set cmd = Nothing
Set cnn = Nothing

End Sub

INSERT data from Excel into SQL DB

You can do a lot with ADO:

Dim cn As New ADODB.Connection

''You should probably change Activeworkbook.Fullname to the
''name of your workbook
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
& ActiveWorkbook.FullName _
& ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"

cn.Open strCon

s = "INSERT INTO [ODBC;Description=TEST;DRIVER=SQL Server;" _
& "SERVER=Server;Trusted_Connection=Yes;" _
& "DATABASE=test].SomeTable ( Col1, Col2, Col3, Col4 ) " _
& "SELECT a.Col1, a.Col2, a.Col3, a.Col4 " _
& "FROM [Sheet2$] a " _
& "LEFT JOIN [ODBC;Description=TEST;DRIVER=SQL Server;" _
& "SERVER=Server;Trusted_Connection=Yes;" _
& "DATABASE=test].SomeTable b ON a.Col1 = b.Col1 " _
& "WHERE b.Col1 Is Null"
cn.Execute s

You can also use the ACE connection: http://www.connectionstrings.com/ or OPENROWSET and an SQL Server connection. In all cases, you may have problems with mixed data types in columns, depending on your registry settings (http://forum.lessthandot.com/viewtopic.php?f=17&t=12043&p=59669&hilit=excel#p59669)

transfer data from Excel to SQL Server

You can do this easily using SSIS, you can refer to these two links for full details.

  1. Link 1
  2. Link 2

[EDIT]

If you have Express then you can try the below commands to setup a linked server and get the data

EXEC sp_addlinkedserver ExcelData,'Jet 4.0','Microsoft.Jet.OLEDB.4.0','C:\MyData.xls', NULL, 'Excel 5.0;'
GO

Then you can select the data into your tables

INSERT INTO Listings ...
SELECT column1 AS intListingID, <put all columns here> FROM ExcelData...Data
GO

For other options check this link

Generate sql insert script from excel worksheet

I think importing using one of the methods mentioned is ideal if it truly is a large file, but you can use Excel to create insert statements:

="INSERT INTO table_name VALUES('"&A1&"','"&B1&"','"&C1&"')"

In MS SQL you can use:

SET NOCOUNT ON

To forego showing all the '1 row affected' comments. And if you are doing a lot of rows and it errors out, put a GO between statements every once in a while

How to create an SQL table and and populate it with Excel spreadsheet data?

If the data is not that big and if it is a simple table, easiest way is to create and open the table in SQL Server Management Studio and copy paste the excel data into it.

Other solutions are using DTS or using SSIS..



Related Topics



Leave a reply



Submit