Failed to import data to a hana schema table using vb.net

[former_member193355]

Hi Experts,

I want to import excel sheet directly into an internal table in the hana schema of sap hana 1.0 that is used by SAP Business One 9.3 For Hana. The excel sheet consists of some data. There is only one sheet and have 20 columns.

The excel sheet is exported from mysql using an application that developed by using vb.net of vs. 2015 or 2010 and then import it to hana schema table. The table name is Invoice_AR in hana schema and in mysql schema.

When the importing take places, the system prompts error. The screen shot of error message as follows:

I have created same column types between mysql schema table and hana schema table. I didn't use double in mysql schema table fields. I also don't use column type 'system.init32'.

Is there any change during the Importing of excel data in the system?

This 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

Please help to solve this issue.

Thanks in advance.

Rgds,

Steve

[jeff_albion]

Hello Steve,

Can you clarify the schema you're using for "AR_Invoice" in HANA and the data fields used in the source from Excel? We need the types to match when doing bulk copying and it sounds like there is a subtle mismatch right now.

Thanks,

Jeff

[former_member193355]

Hello Jeff,

Thank you for your reply. I have created a schema in hana studio by using query referenced by this link:

https://answers.sap.com/questions/9629291/creating-a-schemadatabase-on-hana-simple-please.html

the schema name is DB_ARINV. The table name that I created in the DB_ARINV schema is AR_Invoice.
The excel file is exported from mysql. The data fields used in the source have been created as same as in Hana AR_Invoice table.

Here is the query I used to create it:

use DbSQL
CREATE TABLE AR_Invoice
(
Id int , 
LineNo int,
WksCode NCHAR(20) , 
ARType nchar(20),
ARId NChar(20),
ARInvAll NChar(20),
FedTaxNo NChar(20),
ItemName NCHAR(100),
ItemGroup NCHAR(100),
TotalAmnt decimal(8,2),
InvARDate datetime,
OcrCode NCHAR(20),
OcrCode2 NCHAR(20),
RegToSAP tinyint(1),
RegFromSql tinyint(1),
DateExpToSql datetime,
DateExpToHana datetime,
ExpDocNum NCHAR(20),
ExpNote NCHAR(100),
ExpStatus NCHAR(20))

In Hana Schema, I am using this query to create table AR_Invoice and grant access:

set schema DB_ARINV;
CREATE TABLE AR_Invoice(Id int,LineNo int,WksCode CHAR(20),ARType CHAR(20),ARId CHAR(20),ARInvAll CHAR(20),
FedTaxNo VARCHAR(20),ItemName CHAR(100),ItemGroup CHAR(100),TotalAmnt DECIMAL(8,2),InvARDate SECONDDATE, OcrCode CHAR(20),
OcrCode2 CHAR(20),RegToSAP tinyint, RegFromSql tinyint,
DateExpToSql SECONDDATE,DateExpToHana SECONDDATE,ExpDocNum CHAR(20),ExpNote  CHAR(100),ExpStatus CHAR(20))

In the excel file, there's no specific format for each of those columns (exported from mysql), except the date column. The format is custom. The other column format is general.

Please help me to solve this issue. Thanks in advance.

Rgds,

Steve

[former_member193355]

I found error message again after I created hana schema table with data type double:

I don't know if the hanabulkcopy still have bugs or can be mismatch when using it. Is it tested before ? Is it passed QC Check before launched to use in the sap hana ?

[former_member193355]

Do someone have work around to solve this issue ? I need your help please.

Instead of using hanabulkcopy, is there any other solution ? Could you please share sample code to import excel to hana schema table in sap hana 1.0 ? Thanks in advance

[former_member186095]

Hi Steve,

you can drop hanabulkcopy and use other method instead.

You can try to use this sample (it is in C#):

using System.IO;
using System.Data;
using System.Data.OleDb;
using System.Data.HanaClient;
using System.Configuration;

protected void Upload(object sender, EventArgs e)
{
 //Upload and save the file
  string excelPath = Server.MapPath("~/Files/") + 
  Path.GetFileName(FileUpload1.PostedFile.FileName);
  FileUpload1.SaveAs(excelPath);

string conString = string.Empty;
string extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
switch (extension)
{
    case ".xls": //Excel 97-03
        conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
        break;
    case ".xlsx": //Excel 07 or higher
        conString = 


ConfigurationManager.ConnectionStrings["Excel07+ConString"].ConnectionString;
        break;

}
conString = string.Format(conString, excelPath);
using (OleDbConnection excel_con = new OleDbConnection(conString))
{
    excel_con.Open();
    string sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, 
    null).Rows[0]["TABLE_NAME"].ToString();
    DataTable dtExlData= new DataTable();

    //[OPTIONAL]: It is recommended as otherwise the data will be considered 
    as String by default.
    dtExlData.Columns.AddRange(new DataColumn[3] { new DataColumn("Id", 
    typeof(int)),
            new DataColumn("Name", typeof(string)),
            new DataColumn("Salary",typeof(decimal)) });

    using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT * FROM [" + 
   sheet1 + "]", excel_con))
    {
        oda.Fill(dtExlData);
    }
    excel_con.Close();

    string consString = 
    ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (HanaConnection con = new HanaConnection(consString))
    {
        using (HanaBulkCopy HanaBulkCopy = new HanaBulkCopy(con))
        {
            //Set the database table name
            HanaBulkCopy.DestinationTableName = "dbo.AR_Invoice";

            //[OPTIONAL]: Map the Excel columns with that of the database table
            HanaBulkCopyColumnMappings.Equal("EntryId", "EntryId");
            HanaBulkCopyColumnMappings.Equal("CardCode", "CardCode");
            HanaBulkCopyColumnMappings.AddEqualPriceTotal", "PriceTotal");
            con.Open();
            HanaBulkCopy.WriteToServer(dtExlData);
            con.Close();
        }
    }
}
 }

Good luck

Rgds,

Jimmy