Reading/Editing Excel in a Script Task

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Reading/Editing Excel in a Script Task

Author

Topic

skoom
Starting Member

1 Post

Posted - 2007-04-11 : 22:34:28

Hey Guys,

Does anyone know how I can do this? I tried adding the Microsoft.Office.Interop.Excel reference to the script task but it doesn't allow me to. It only lets me pick from a selection of references that are already there (not allowing me to add new ones)

I do have this reference and have used it in VB and C# app's

If there is any other way to read an excel file please let me know, ill move onto that

YogeshDesai
Posting Yak Master

136 Posts

Posted - 2007-05-31 : 05:56:44

try this one

' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.

Imports System
Imports System.IO
Imports System.Data.OleDb
Imports Microsoft.Office.Interop.Excel
Imports System.Data
Imports System.Data.SqlClient
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.

Public InExcelName As String
Public InSheetName As String
Public InRowName As String
Public InColName As String
Public cnt As Integer
Public appIn As Microsoft.Office.Interop.Excel.ApplicationClass
Public InworkBook1 As Workbook
Public InexcelSheets As Sheets
Public Inworksheet1 As Worksheet
Public InRange, OutRange As Range
Public Insheetname1 As Worksheet
Public FieldName, FieldValue As String
Public DTable As System.Data.DataTable
Public Drow As DataRow
Public XlConnection As System.Data.SqlClient.SqlConnection
Public MyAdapter As System.Data.SqlClient.SqlDataAdapter

Public Sub Main()
'
' Add your code here
'
'InExcelName = Dts.Variables("ExcelFile").Value.ToString '"c:\test.xls"
'MsgBox(InExcelName)
'InSheetName = Dts.Variables("ExcelTable").Value.ToString
'MsgBox(InSheetName)
Call XLGetRecord()
If Not (DTable Is Nothing) Then
If DTable.Rows.Count > 0 Then
Dim xlFilePath As String = String.Empty
For Each Drow As DataRow In DTable.Rows
Dim xlNextFilePath As String = Drow.Item("excelpath").ToString().Trim()
If String.Compare(xlFilePath, xlNextFilePath) <> 0 Then
xlFilePath = xlNextFilePath
MsgBox(xlFilePath)
Dim dvExcel As New DataView(DTable, "excelpath = '" + xlFilePath + "'", Nothing, DataViewRowState.CurrentRows)
'''MsgBox("DatavIew")
Call Getdata(xlFilePath, dvExcel)
End If

Next
''Insert values from excel to database. Values are stored in dTable
Call XltoDB()
End If
End If

MsgBox(FieldValue)

Dts.TaskResult = Dts.Results.Success

End Sub
Public Sub Getdata(ByVal xlFilePath As String, ByRef dvExcel As DataView)
Dim InExcelName As String
Dim InSheetName As String
Dim IFieldName As String
Dim IRowNum As Integer
Dim IColNum As Integer
Try
appIn = New Microsoft.Office.Interop.Excel.ApplicationClass
'''Open excel sheet to read
InworkBook1 = appIn.Workbooks.Open(xlFilePath, 0, False, 5, "", "", True, XlPlatform.xlWindows, "", False, False, 0, True, False, False)
Dim dvcounter As Integer
For dvcounter = 0 To dvExcel.Count - 1
'''MsgBox("DVLoop")
'''MsgBox("" + DTable.Columns(0).ColumnName + "," + DTable.Columns(1).ColumnName + "," + DTable.Columns(2).ColumnName + "," + DTable.Columns(3).ColumnName + "," + DTable.Columns(4).ColumnName)
InSheetName = dvExcel.Item(dvcounter).Item("sheetname").ToString()
IFieldName = dvExcel.Item(dvcounter).Item("columnName").ToString()
IRowNum = Convert.ToInt32(dvExcel.Item(dvcounter).Item("rownumber"))
IColNum = Convert.ToInt32(dvExcel.Item(dvcounter).Item("columnNumber"))
'''MsgBox("read1")
InexcelSheets = InworkBook1.Worksheets
Inworksheet1 = CType(InexcelSheets(InSheetName), Worksheet)
Inworksheet1.Activate()
'''MsgBox("entered")

'''Get Column Name to Validate
'''InRange = CType(Inworksheet1.Cells(IRowNum, IColNum - 1), Range)
'''FieldValue = CStr(InRange.Value2)
'''If String.Compare(IFieldName.Trim(), FieldValue.Trim()) <> 0 Then
''' MsgBox("Invalid Column Mapping")
''' Exit Try
'''End If
'''MsgBox("Valid Column Mapping")

InRange = CType(Inworksheet1.Cells(IRowNum, IColNum), Range)
'''How to increment row no
FieldValue = CStr(InRange.Value2)
dvExcel.Item(dvcounter).Row.Item("CellValue") = FieldValue
MsgBox(FieldValue)
Next dvcounter

Catch ex As Exception
Throw ex
Finally
InworkBook1.Close()
appIn.Quit()
End Try
End Sub

Public Sub XLGetRecord()

Dim connectionString As String = "Data Source=MLBP-0059;Initial Catalog=SSIS;uid=sa;pwd=ad10sqladmin;"
XlConnection = New SqlConnection(connectionString)
MyAdapter = New SqlClient.SqlDataAdapter("getrecordSP", XlConnection)
DTable = New Data.DataTable()
MyAdapter.Fill(DTable)

End Sub
Public Sub XltoDB()
Dim connectionString As String = "Data Source=MLBP-0059;Initial Catalog=SSIS;uid=sa;pwd=ad10sqladmin;"
XlConnection = New SqlConnection(connectionString)
Dim SCommand As New SqlCommand()
SCommand.Connection = XlConnection
Dim sparam As New SqlParameter()
sparam.ParameterName = "@InExcelTempID"
sparam.SqlDbType = SqlDbType.Int
SCommand.Parameters.Add(sparam)
sparam = New SqlParameter()
sparam.ParameterName = "@InExcelTempDetailID"
sparam.SqlDbType = SqlDbType.Int
SCommand.Parameters.Add(sparam)
sparam = New SqlParameter()
sparam.ParameterName = "@InColumnValue"
sparam.SqlDbType = SqlDbType.VarChar
sparam.Size = 100
SCommand.Parameters.Add(sparam)
SCommand.CommandText = "Insert into Inputexceldata (ExcelTempID,ExcelTempDetailID,ColumnValue) values(@InExcelTempID,@InExcelTempDetailID,@InColumnValue)"
MsgBox("Commandtext")
Try

XlConnection.Open()
'''SCommand.ExecuteNonQuery()
''Dim xltransaction As SqlTransaction = XlConnection.BeginTransaction()
''MsgBox(DTable.Rows.Count)
For Each dr As DataRow In DTable.Rows
''MsgBox(dr.Item("ExcelTempID"))
''MsgBox(dr.Item("ExcelTempDetailID"))
''MsgBox(dr.Item("ColumnValue"))
SCommand.Parameters.Item("@InExcelTempID").Value = Convert.ToInt32(dr.Item("ExcelTempID"))
SCommand.Parameters.Item("@InExcelTempDetailID").Value = Convert.ToInt32(dr.Item("ExcelTempDetailID"))
SCommand.Parameters.Item("@InColumnValue").Value = Convert.ToString(dr.Item("CellValue"))
SCommand.ExecuteNonQuery()
Next
''xltransaction.Commit()
Catch ex As Exception

Finally
If Not (XlConnection Is Nothing) Then
If XlConnection.State <> ConnectionState.Closed Then
XlConnection.Close()
End If
End If

End Try

End Sub

End Class

Yogesh V. Desai. | SQLDBA|

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-31 : 09:29:04

Also refer
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources