• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 964
  • Last Modified:

Import Data from MS Excel into MSQL

Hi Wiz,

How to import MS Excel data into MS SQL using VB?
but without OPENDATASOURCE or OPENROWSET SQL function

please share your ideas...thanks
0
jilim
Asked:
jilim
  • 4
  • 4
  • 3
1 Solution
 
poayseeCommented:
You may use the DTS(Data Transformation Services)
0
 
jilimAuthor Commented:
any example?
0
 
poayseeCommented:
It is a GUI. You can open your SQL Server Enterprise Manager
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

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

Thanks for the suggestion.
0
 
poayseeCommented:
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
0
 
nmcdermaidCommented:
Why not use OPENDATASOURCE or OPENROWSET? they are there for your benefit. You can still do it in VB.

0
 
jilimAuthor Commented:
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...
0
 
nmcdermaidCommented:
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.
0
 
jilimAuthor Commented:
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."

0
 
nmcdermaidCommented:
That error message indicates that you have the driver but you need to enable adhoc queries.

This explains how to fix that:

http://support.microsoft.com/default.aspx?kbid=266008


There is also moer information at this post:
http://www.experts-exchange.com/Web/Web_Languages/ASP/Q_20554649.html

0
 
poayseeCommented:
Hi,

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

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

  • 4
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now