Import Data from MS Excel into MSQL

You may use the DTS(Data Transformation Services)

any example?

It is a GUI. You can open your SQL Server Enterprise Manager

OK... For me I need to import the data using VB programming language.

Thanks for the suggestion.

Below is the 3 module of VB.Net's  code. You must addin the DTSkg80.dll in your program before you write in the code below:

Private Sub ExecutePackage(ByVal package As String)
        ' executes the dts package so that it is called one by one
        Dim oPKG As DTS.Package, oStep As DTS.Step
        oPKG = New DTS.Package
        Dim counter As Integer

        Dim sServer As String, sUsername As String, sPassword As String
        Dim sPackageName As String
        Dim lErr As Long, sSource As String, sDesc As String

        ' Set Parameter Values
        sServer = "PIMOSERV"
        sUsername = ""
        sPassword = ""
        sPackageName = package




        ' Load Package
        oPKG.LoadFromSQLServer(sServer, sUsername, sPassword, _
    Microsoft.SQLServer.DTSPkg80.DTSSQLServerStorageFlags.DTSSQLStgFlag_UseTrustedConnection, , , , _
    sPackageName)

        ' THIS IS UNDER CONSTRUCTION AND THIS SHOWING THE RESULT THAT CANNOT DEFINE NULL
        'Set Exec on Main Thread
        'For Each oStep In oPKG.Steps
        'oStep.ExecuteInMainThread = True
        'Next

        ' Execute the dts package
        oPKG.Execute()

        ' Get Status and Error Message
        For Each oStep In oPKG.Steps
            If oStep.ExecutionResult = Microsoft.SQLServer.DTSPkg80.DTSStepExecResult.DTSStepExecResult_Failure Then
                oStep.GetExecutionErrorInfo(lErr, sSource, sDesc)
                fMessage = fMessage & "*****  " & package & "   ERROR*******" & vbCrLf
                fMessage = fMessage & "Step """ & oStep.Description & _
                    """ Failed" & vbCrLf & _
                    vbTab & "Error: " & lErr & vbCrLf & _
                    vbTab & "Source: " & sSource & vbCrLf & _
                    vbTab & "Description: " & sDesc & vbCrLf & vbCrLf

                txtBoxFAIL.Text = fMessage
                pass = pass + 1
                lblfailpackage.Text = pass

            Else
                sMessage = sMessage & "Step """ & oStep.Name & _
                    """ Succeeded" & vbCrLf & vbCrLf
                txtBoxOK.Text = sMessage
                fail = fail + 1
                lblpasspackage.Text = fail
            End If
        Next

        oPKG.UnInitialize()

        oStep = Nothing
        oPKG = Nothing

        ' Display Results


    End Sub


Private Sub GetPackage()
        Dim strConn As String
        Dim counter As Integer

        'Build a strConnection string or any other valid formats you are comfortable
        strConn = "Database=msdb;integrated Security=True;Server=PIMOSERV"

        'Create a strConnection object
        Dim DTSConn As New System.Data.SqlClient.SqlConnection(strConn)

        'Create a command object with query and strConnection as arguments
        Dim DTSCmd As New System.Data.SqlClient.SqlCommand("EXEC sp_enum_dtspackages", DTSConn)

        'open the command's strConnection object up
        DTSCmd.Connection.Open()

        'Read all the data into a DataReader
        Dim DTSReader As System.Data.SqlClient.SqlDataReader = DTSCmd.ExecuteReader(Data.CommandBehavior.Default)

        'Loop through the collection of DTS Packages
        While DTSReader.Read()
            'Display the record

            clbDTSPackage.Items.Add(DTSReader.GetString(0))
        End While

        While counter < clbDTSPackage.Items.Count
            'check all items is here
            '*************************

            clbDTSPackage.SetItemChecked(counter, True)
            counter += 1

        End While
        clbDTSPackage.Sorted = True

        'Code Clear up activity
        DTSReader.Close()
        DTSConn.Close()
    End Sub

Sub executepackagethread()

        Dim package(34) As String
        Dim counterfiles As Integer = 0
        Dim files(34) As String
        ' To change the automation process. Please change the files and package.
        ' for automation these files is to check wether the package is valid for running.
        files(0) = "All_Planned_Order.txt"
        files(1) = "ASL.txt"
        files(2) = "Dss 1.xls"
        files(3) = "ISO.txt"
        files(4) = "Item Master.txt"
        files(5) = "On_Hand_1.txt"
        files(6) = "On_Hand_2.txt"
        files(7) = "On_Hand_3.txt"
        files(8) = "On_Hand_4.txt"
        files(9) = "On_Hand_5.txt"
        files(10) = "On_Hand_6.txt"
        files(11) = "On_Hand_7.txt"
        files(12) = "Open_PO.txt"
        files(13) = "SI.xls"
        files(14) = "WO_Unreleased_1.txt"
        files(15) = "WO_Unreleased_2.txt"
        files(16) = "WO_Unreleased_3.txt"
        files(17) = "WO_Unreleased_4.txt"
        files(18) = "WO_Unreleased_5.txt"
        files(19) = "WO_Unreleased_6.txt"
        files(20) = "WO_Unreleased_7.txt"
        files(21) = "Open_PO_1.txt"
        files(22) = "Open_PO_2.txt"
        files(23) = "Open_PO_3.txt"
        files(24) = "Open_PO_4.txt"
        files(25) = "Open_PO_5.txt"
        files(26) = "Open_PO_6.txt"
        files(27) = "Open_PO_7.txt"
        'Add in 09/07/2004
        files(28) = "REQ_1.txt"
        files(29) = "REQ_2.txt"
        files(30) = "REQ_3.txt"
        files(31) = "REQ_4.txt"
        files(32) = "REQ_5.txt"
        files(33) = "REQ_6.txt"
        files(34) = "REQ_7.txt"


        ' these package is the name of the package for running.
        package(0) = "ASCP ALL"
        package(1) = "ASL"
        package(2) = "DSS_TEMP_1"
        package(3) = "ISO_Temp_1"
        package(4) = "Item_1"
        package(5) = "On_Hand_1"
        package(6) = "On_Hand_2"
        package(7) = "On_Hand_3"
        package(8) = "On_Hand_4"
        package(9) = "On_Hand_5"
        package(10) = "On_Hand_6"
        package(11) = "On_Hand_7"
        package(12) = "Open_PO"
        package(13) = "SI"
        package(14) = "WO_1"
        package(15) = "WO_2"
        package(16) = "WO_3"
        package(17) = "WO_4"
        package(18) = "WO_5"
        package(19) = "WO_6"
        package(20) = "WO_7"
        package(21) = "PO_1"
        package(22) = "PO_2"
        package(23) = "PO_3"
        package(24) = "PO_4"
        package(25) = "PO_5"
        package(26) = "PO_6"
        package(27) = "PO_7"
        'Add in 09/07/2004
        package(28) = "REQ_1"
        package(29) = "REQ_2"
        package(30) = "REQ_3"
        package(31) = "REQ_4"
        package(32) = "REQ_5"
        package(33) = "REQ_6"
        package(34) = "REQ_7"


        If Shared1.executeday = Now.DayOfWeek Then
            Call dropTable()
        End If

        Timer1.Enabled = True
        Timer1.Start()

        'execute the DTS that will transfer data from WIP_OPEN to WIP_OPEN_HIS
        'delete the record from the table WIP_OPEN so that the table will always hav current daily data...
        Call ExecutePackage("WIP_HIS")
        Call Execute_Delete_WIP()

        While counterfiles < files.Length
            'executes the dts package one by one.
            Dim f As New IO.FileInfo("\\pimoserv\Raw File by WW\Current Week\" & files(counterfiles))

            If f.Exists = True Then
                stbStatus.Text = "Loading.... " & package(counterfiles)
                Call ExecutePackage(package(counterfiles))
            End If

            counterfiles = counterfiles + 1

        End While

        TimerAutoExec.Enabled = False
        TimerAutoExec.Stop()


        If Shared1.executeday = Now.DayOfWeek Then
            stbStatus.Text = "Running Cross Tab tables"
            Call ExecuteX_tables()
        End If

        ' moves files to backup if the execution day <> first day
        If Shared1.executeday <> Now.DayOfWeek Then
            Call Move_Files_Out()
            stbStatus.Text = "Executing the Shortage store procedure"

            'Execute the store procedure daily
            Call Execute_Fill_All()

            ' inserted here is for friday yo execute the backup
            'If Now.DayOfWeek = DayOfWeek.Friday Then
            'Call ExecutePackage("HIS_PACK_1")
            'End If

            MsgBox("Finish daily routine")
            Timer1.Stop()
            Timer1.Dispose()
            prbStatus.Value = 0
            stbStatus.Text = ""
        End If

    End Sub

Why not use OPENDATASOURCE or OPENROWSET? they are there for your benefit. You can still do it in VB.

Hi nmcdermaid,

I knew that OPENDATASOURCE or OPENROWSET need to use Linked Server, rite?... will it cause any problem to the SQL server?

Whats your opinion...

No, neither of them need a linked server.

They do need the same driver that the linked server would use though. You need the driver on the server.

This following code will insert data into a MSSQL table from an Excel file, as long as:

-c:\Test.xls exists
-it has a sheet in it called ExcelTable
-The Jet OLEDB driver is installed on the server.



INSERT INTO [YourTable]
SELECT * FROM
OpenRowSet( 'Microsoft.Jet.OLEDB.4.0',
  'Excel 8.0;Database=c:\Test.xls',
    'SELECT * FROM [ExcelTable$]') A



This is T-SQL code which means you execute it in a database connection.

Hi Poaysee,
The codes is where it auto generated using DTS from the SQL Enterprise Manager rite....I've tried that previously, but seems like it won't work.

nmcdermaid,
i gues the Jet OLEDB have to install in the server that my program is running rite?.... the message that i encounter previously was....
"Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been denied. You must access this provider through a linked server."

Hi,

 Is there any error message? It is because those code that I am using for my current loading project....