VB.NET: Import Excel Data into SQL (Error: Could not find installable ISAM.)

[Indra PR]

I tried to make a simple application using VB.NET that is able to import excel data into sql. I got some tutorial from Bytes Forum. I've tried to follow the instruction to check msexcl40.dll, it's there but I still got an error said that the program could not find installable ISAM. Can someone help me? Private Function frmUploader_upload() As Boolean Dim strConnection As String Dim strCommand As String Dim excelConnection As System.Data.OleDb.OleDbConnection Dim excelCommand As System.Data.OleDb.OleDbCommand strConnection = String.Format("Provider=Microsoft.Jet.OLEDB.4.0; Data Source={0}; Extended Properties=Excel 8.0;Persist Security Info=False", Me.tbFile.Text) excelConnection = New System.Data.OleDb.OleDbConnection(strConnection) strCommand = "INSERT INTO [OBDC; Driver={SQL Server}; Server=" & Me.tbServer.Text & "; Database=" & Me.tbDatabase.Text & "; Trusted_Connection=yes].[Sl_Logproof_200311] SELECT * FROM [" & Me.cbSheet.Text & "$];" excelCommand = New System.Data.OleDb.OleDbCommand(strCommand, excelConnection) Try excelConnection.Open() excelCommand.ExecuteNonQuery() 'got error after this line Catch ex As Exception MsgBox(ex.Message) Return False Finally excelConnection.Close() End Try Return True End Function

Best Regard, Indra Permana Rusli

[Ashfield]

Looks like you are totally mixed up. This connection works for me

OleDbConnection con = new OleDbConnection(string.Format("Data Source={0};Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;", excelfileName));

and your insert is total rubbish as far as I can see. Try something like this

sqlToRun= string.Format("insert into mytable(mycolumn1,mycolumn2) values ('{0}','{1}'",val1,val2);
cmd = new OleDbCommand(sqlToRun, con);
cmd.ExecuteNonQuery();

Hope this helps.

Bob Ashfield Consultants Ltd

[Ashfield]

Sorry, you'll have to convert from C# :)

Bob Ashfield Consultants Ltd

[Paul Conrad]

That can't be too hard to do :rolleyes:

"The clue train passed his station without stopping." - John Simmons / outlaw programmer "Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon "Not only do you continue to babble nonsense, you can't even correctly remember the nonsense you babbled just minutes ago." - Rob Graham

[Indra PR]

I tried to follow your code and modified it with a data reader.

Public Function ExecuteQuery(ByVal query As String) As Boolean
    Dim con As System.Data.OleDb.OleDbConnection
    Dim cmd As System.Data.OleDb.OleDbCommand

    con = New System.Data.OleDb.OleDbConnection(DSN)
    cmd = New System.Data.OleDb.OleDbCommand(query, con)
    cmd.CommandType = CommandType.Text

    Try
        con.Open()
        cmd.ExecuteNonQuery()
    Catch ex As Exception
        Throw ex
    Finally
        con.Close()
    End Try

    Return True
End Function

Private Function frmUploader\_upload() As Boolean
    Dim strConnection As String
    Dim strCommand As String
    Dim query As String
    Dim excelConnection As System.Data.OleDb.OleDbConnection
    Dim excelCommand As System.Data.OleDb.OleDbCommand
    Dim dr As System.Data.OleDb.OleDbDataReader
    Dim dt As New DataTable
    Dim i As Integer = 0

    strConnection = String.Format("Provider=Microsoft.Jet.OLEDB.4.0; Data Source={0}; Extended Properties=Excel 8.0;Persist Security Info=False", Me.tbFile.Text)
    excelConnection = New System.Data.OleDb.OleDbConnection(strConnection)
    strCommand = String.Format("SELECT \* FROM \[{0}$\]", Me.cbSheet.Text)
    excelCommand = New System.Data.OleDb.OleDbCommand(strCommand, excelConnection)

    Try
        excelConnection.Open()
        dr = excelCommand.ExecuteReader
        While dr.Read
            query = String.Format("INSERT INTO Sl\_Logproof\_200311 VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}',{18},{19},{20},{21},'{22}',{23},{24},'{25}','{26}','{27}',{28})", \_
            ReplaceQuote(dr(0).ToString), ReplaceQuote(dr(1).ToString), ReplaceQuote(dr(2).ToString), ReplaceQuote(dr(3).ToString), ReplaceQuote(dr(4).ToString), ReplaceQuote(dr(5).ToString), ReplaceQuote(dr(6).ToString), ReplaceQuote(dr(7).ToString), ReplaceQuote(dr(8).ToString), ReplaceQuote(dr(9).ToString), ReplaceQuote(dr(10).ToString), \_
            ReplaceQuote(dr(11).ToString), ReplaceQuote(dr(12).ToString), ReplaceQuote(dr(13).ToString), ReplaceQuote(dr(14).ToString), ReplaceQuote(dr(15).ToString), ReplaceQuote(dr(16).ToString), ReplaceQuote(dr(17).ToString), IsEmptyText(dr(18).ToString, "0"), IsEmptyText(dr(19).ToString, "0"), IsEmptyText(dr(20).ToString, "0"), \_
            IsEmptyText(dr(21)

[Paddy Boyd]

Check the extended properties bit of your connection string - it can differ for different versions of excel.

[Ashfield]

Well, yes DTS is a lot quicker, thats what it was designed for! You may be able to tweak your code, but I doubt if it will ever outperform dts.

Bob Ashfield Consultants Ltd

[Indra PR]

Hohohoho, thanks, finally I can import it well using DTS. I am using Microsoft DTS Custom Task Object Library reference in my source code and create DTS Package with a global variable named gvDataSource in the SQL Server. Then here is my new code.

Private Function frmUploader\_upload() As Boolean
    Dim pkg As DTS.Package2

    pkg = New DTS.Package
    pkg.LoadFromSQLServer(mDBSERVER, mDBUSER, mDBPASSWORD, DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag\_Default, , , , "APLIKASI3:E\_START.Sl\_Logproof\_200311")

    For Each gv As DTS.GlobalVariable In pkg.GlobalVariables
        pkg.GlobalVariables.Remove(gv.Name)
    Next

    pkg.GlobalVariables.AddGlobalVariable("gvDataSource", Me.tbFile.Text)

    Me.Cursor = Cursors.WaitCursor
    Try
        pkg.Execute()
    Catch ex As Exception
        MsgBox(ex.Message)
        Return False
    Finally
        pkg.UnInitialize()
        pkg = Nothing
    End Try

    Me.Cursor = Cursors.Default

    Return True
End Function

Best Regard, Indra Permana Rusli

[Ashfield]

pleased to be of some use. :)

Bob Ashfield Consultants Ltd