November 16, 2007 at 9:41 am
We're running SQL 2005 64-bit and I have never been able to get the "Import" function to work with Excel files. I have resigned myself to importing them into Access and then from there into SQL. Luckily I don't have to do it too often....if I did, I'd look at SSIS or OPENROWSET.
August 27, 2008 at 1:26 pm
Is there any way to do this with T-SQL? I have to frequently update a table on my the database from an excel file, the problem is that all the records on the table have to be deleted prior importing the data from the excel file, and the table has to be available with all the records the most possible. Importing the data thru Enterprise Manager is a litlle slow because the step by step method..
Thanks a lot in advance.
August 27, 2008 at 6:05 pm
Paul Mc
A very similar question was asked in another forum and here is the most flexible approach given as the answer. This may also be the answer to your question. Look at
http://qa.sqlservercentral.com/Forums/Topic559811-149-1.aspx#bm559856
by the way the above was posted today
August 28, 2008 at 9:11 am
Thanks a lot..
After posting, I made a script.. here´s it..
delete from mytable
insert into mytable (fieldame1, fieldame2, fieldame3, fieldame4) SELECT xlsfield1 as fieldname1, xlsfield2 as fieldname2, xlsfield3 as fieldname3, xlsfield4 as fieldname4 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=d:\myxlsfile.xls','SELECT * FROM [mysheetname$]')
September 8, 2008 at 4:13 pm
Hi, I created a macro into Excel to save data into SQL server as follow:
sqlstring = "UPDATE SNOP_ForecastProcesoAgosto Set Fp01 =" & nFp01 & ", Fp02=" & nFp02 & ", Fp03=" & nFp03 & ", Fp04=" & nFp04 & ", Fp05=" & nFp05 & ", Fp06=" & nFp06 & ", Fp07=" & nFp07 & ", Fp08=" & nFp08 & ", Fp09=" & nFp09 & ", Fp10=" & nFp10 & ", Fp11=" & nFp11 & ", Fp12=" & nFp12 & " WHERE rut='" & crut & "' and codigo='" & csku & "'"
connstring = _
"ODBC;DSN=SNOP;UID=;PWD=;Database=snop;Trusted_connection=Yes"
With ActiveSheet.QueryTables.Add(Connection:=connstring, Sql:=sqlstring, Destination:=Range ("DK1"))
.Refresh
End With
the problem with this code is that after this end, let's the Excel quite slow, is there another way, I have to work from Excel since changes needs to be refreshed on line, I cannot import them using Enterprice manager,
thanks
Juan
September 11, 2008 at 1:52 am
Or you could link to the table in your SQL Server db from a new access database, and import into the Access linked table just like you do for any standard Access table.
September 11, 2008 at 8:23 am
I have no too much experience with Access, but I already created a linked table into access with SQL Server, now I don't how to link Excel with the Access's dable for read/write,
TKS
Juan
September 11, 2008 at 10:14 am
Due to security bcp is disabled so you need to use SSIS packages.
Use script command and write code in VB
The example i have is to write in excel from SQL server. You can do opposite
Public Sub Main()
Dim ExcelObject As Object
Dim i As Integer, j As Integer = 0
Dim sPO As Long, sReason As String, sShiptoName As String, sShiptoAddress1 As String, sShiptoAddress2 As String, sShiptoCity As String
Dim sShiptoState As String, sShiptoPostal As String, sSourceCode As String, sAttentionTo As String
Dim sDelInstr1 As String, sDelInstr2 As String, sProductCode As String, iQuantity As Integer
' Dim connectionString As String = "Driver={Microsoft Excel Driver (*.xls)};DBQ=D:\Clients\Template\Book1.xls"
Dim sString As String
Dim sqlConnectionString As String = "Data Source=Server;Initial Catalog=DB;User Id=;Password=;"
Dim ExcelConnString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\server\e$\Campaigns\Working_Folder\Orders.xls;Extended Properties=Excel 8.0"
Dim SQL As String
Dim ssql As String = "SELECT * FROM Table" '
' Create connection object
Dim conn As New OleDb.OleDbConnection(ExcelConnString)
'Dim cmd As New OleDb.OleDbCommand(SQL)
'cmd.Connection = conn
'conn.Open()
'cmd.ExecuteNonQuery()
Dim sqlconn As New SqlClient.SqlConnection(sqlConnectionString)
Dim ordercmd As New SqlClient.SqlCommand(ssql)
ordercmd.Connection = sqlconn
sqlconn.Open()
Dim reader As SqlClient.SqlDataReader = ordercmd.ExecuteReader()
While reader.Read()
sPO = reader.GetInt64(0)
sReason = reader(1).ToString
sShiptoName = reader(2).ToString
sShiptoAddress1 = reader(3).ToString
sShiptoAddress2 = reader(4).ToString
sShiptoCity = reader(5).ToString
sShiptoState = reader(6).ToString
sShiptoPostal = reader(7).ToString
sSourceCode = reader(8).ToString
sAttentionTo = reader(9).ToString
sDelInstr1 = reader(10).ToString
sDelInstr2 = reader(11).ToString
sProductCode = reader(12).ToString
iQuantity = reader.GetInt32(13)
If j = 0 Then
For i = 1 To 24
SQL = "insert into [Sheet1$] (PO,REASON_CODE,SHIPTOCUSTOMERNAME,SHIPTOADDRESSLINE1,SHIPTOADDRESSLINE2,SHIPTOCITY, SHIPTOSTATE,SHIPTOPOSTAL,SOURCECODE,ATTENTIONTO, DELINSTR1,DELINSTR2, PRODUCTCODE,QUANTITY) values ('','','','','','','','','','','','','','')"
j = 1
Next
End If
SQL = "insert into [Sheet1$] (PO,REASON_CODE,SHIPTOCUSTOMERNAME,SHIPTOADDRESSLINE1,SHIPTOADDRESSLINE2, " _
& "SHIPTOCITY, SHIPTOSTATE,SHIPTOPOSTAL,SOURCECODE,ATTENTIONTO, DELINSTR1,DELINSTR2, PRODUCTCODE,QUANTITY)VALUES(" _
& "'" & sPO & "','" & sReason & "'," _
& "'" & sShiptoName & "','" & sShiptoAddress1 & "'," _
& "'" & sShiptoAddress2 & "','" & sShiptoCity & "'," _
& "'" & sShiptoState & "','" & sShiptoPostal & "'," _
& "'" & sSourceCode & "','" & sAttentionTo & "'," _
& "'" & sDelInstr1 & "','" & sDelInstr2 & "'," _
& "'" & sProductCode & "','" & iQuantity & "')"
Dim cmd As New OleDb.OleDbCommand(SQL)
cmd.Connection = conn
conn.Open()
cmd.ExecuteNonQuery()
conn.Close()
End While
reader.Close()
conn.Close()
Dts.TaskResult = Dts.Results.Success
End Sub
September 11, 2008 at 10:15 am
Due to security bcp is disabled on machines so you need to use SSIS packages.
Use script command and write code in VB
The example i have is to write in excel from SQL server. You can do opposite
Public Sub Main()
Dim ExcelObject As Object
Dim i As Integer, j As Integer = 0
Dim sPO As Long, sReason As String, sShiptoName As String, sShiptoAddress1 As String, sShiptoAddress2 As String, sShiptoCity As String
Dim sShiptoState As String, sShiptoPostal As String, sSourceCode As String, sAttentionTo As String
Dim sDelInstr1 As String, sDelInstr2 As String, sProductCode As String, iQuantity As Integer
' Dim connectionString As String = "Driver={Microsoft Excel Driver (*.xls)};DBQ=D:\Clients\Template\Book1.xls"
Dim sString As String
Dim sqlConnectionString As String = "Data Source=Server;Initial Catalog=DB;User Id=;Password=;"
Dim ExcelConnString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\server\e$\Campaigns\Working_Folder\Orders.xls;Extended Properties=Excel 8.0"
Dim SQL As String
Dim ssql As String = "SELECT * FROM Table" '
' Create connection object
Dim conn As New OleDb.OleDbConnection(ExcelConnString)
'Dim cmd As New OleDb.OleDbCommand(SQL)
'cmd.Connection = conn
'conn.Open()
'cmd.ExecuteNonQuery()
Dim sqlconn As New SqlClient.SqlConnection(sqlConnectionString)
Dim ordercmd As New SqlClient.SqlCommand(ssql)
ordercmd.Connection = sqlconn
sqlconn.Open()
Dim reader As SqlClient.SqlDataReader = ordercmd.ExecuteReader()
While reader.Read()
sPO = reader.GetInt64(0)
sReason = reader(1).ToString
sShiptoName = reader(2).ToString
sShiptoAddress1 = reader(3).ToString
sShiptoAddress2 = reader(4).ToString
sShiptoCity = reader(5).ToString
sShiptoState = reader(6).ToString
sShiptoPostal = reader(7).ToString
sSourceCode = reader(8).ToString
sAttentionTo = reader(9).ToString
sDelInstr1 = reader(10).ToString
sDelInstr2 = reader(11).ToString
sProductCode = reader(12).ToString
iQuantity = reader.GetInt32(13)
If j = 0 Then
For i = 1 To 24
SQL = "insert into [Sheet1$] (PO,REASON_CODE,SHIPTOCUSTOMERNAME,SHIPTOADDRESSLINE1,SHIPTOADDRESSLINE2,SHIPTOCITY, SHIPTOSTATE,SHIPTOPOSTAL,SOURCECODE,ATTENTIONTO, DELINSTR1,DELINSTR2, PRODUCTCODE,QUANTITY) values ('','','','','','','','','','','','','','')"
j = 1
Next
End If
SQL = "insert into [Sheet1$] (PO,REASON_CODE,SHIPTOCUSTOMERNAME,SHIPTOADDRESSLINE1,SHIPTOADDRESSLINE2, " _
& "SHIPTOCITY, SHIPTOSTATE,SHIPTOPOSTAL,SOURCECODE,ATTENTIONTO, DELINSTR1,DELINSTR2, PRODUCTCODE,QUANTITY)VALUES(" _
& "'" & sPO & "','" & sReason & "'," _
& "'" & sShiptoName & "','" & sShiptoAddress1 & "'," _
& "'" & sShiptoAddress2 & "','" & sShiptoCity & "'," _
& "'" & sShiptoState & "','" & sShiptoPostal & "'," _
& "'" & sSourceCode & "','" & sAttentionTo & "'," _
& "'" & sDelInstr1 & "','" & sDelInstr2 & "'," _
& "'" & sProductCode & "','" & iQuantity & "')"
Dim cmd As New OleDb.OleDbCommand(SQL)
cmd.Connection = conn
conn.Open()
cmd.ExecuteNonQuery()
conn.Close()
End While
reader.Close()
conn.Close()
Dts.TaskResult = Dts.Results.Success
End Sub
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply