Skip to Content
0
Jun 14, 2020 at 05:06 AM

Importing Excel Data using HanaBulkCopy from an Excel File to SAP Hana Schema Invoice AR Table

436 Views

Hi everyone.

I've been trying to import the excel data that have been exported from mysql on cloud into hana schema table of sap b1 hana Database using a synchronizing application.

SAP hana version is 1.0 that is used for SAP B1 Hana. The excel data will be used to create the invoice a/r in the SAP B1 hana application.

I do not use any DI APi connection method of SAP B1 Hana because the syn. application is only used to export data from mysql database to excel file and the data is imported to SAp Hana database schema. Both databases will use the same field codes, same schema and same table names. Here is the code I am using :

Private Sub ImportToHana(FullFileName As String)

        Dim filenam As String
        filenam = FullFileName
        Dim ExcelConnection As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filenam + " ;Extended Properties=""Excel 12.0 Xml;HDR=Yes""")
        ExcelConnection.Open()
        Dim expr As String = "SELECT * FROM [Sheet1$]"
        Dim objCmdSelect As OleDbCommand = New OleDbCommand(expr, ExcelConnection)
        Dim objDR As OleDbDataReader

        Dim SQLconn As New HanaConnection()
        Dim sConn As String = ""
        sConn = "Server=" & sSAPServerName & _
                                ";Current Schema =" & sSAPDbName & _
                                ";UseTrusted=False" & _
                                ";UserID=" & sSAPDbUserName & _
                                ";Password=" & sSAPDbPassword

        SQLconn.ConnectionString = sConn
        SQLconn.Open()

        Using bulkCopy As HanaBulkCopy = New HanaBulkCopy(SQLconn)
            bulkCopy.DestinationTableName = "AR_Invoice"

            Try
                objDR = objCmdSelect.ExecuteReader
                bulkCopy.WriteToServer(objDR)
                objDR.Close()
                SQLconn.Close()
                MsgBox("Data's are imported to SQL Succesfully", MsgBoxStyle.Information)
            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try
        End Using
    End Sub

The import is failed. Please help me to resolve this issue. I'd appreciate it so much.

Rgds,

Steve