Excel to DataTable importing wrong values

Question

I'm developing a program that will import excel to mysql. In the application, the user first of all has to load an excel file and then he will see that file load on a DataGridView.

The problem is that when the file is load it shows other values instead of excel one.

Here is an example in the excel file the value in red square is 1,49 While when the excel is load in DataTable > DataGridView it's 1,39...

Here is the method where I load excel in to DataGridView the dataGridView is called MetroGrid1

Private Sub Upload_Click(sender As Object, e As EventArgs) Handles BtnUpload.Click
    Dim fd As OpenFileDialog = New OpenFileDialog()
    Dim strFileName As String

    fd.Title = "Seleziona file.."
    fd.InitialDirectory = "C:\"
    fd.Filter = "File Excel|*.xls;*.xlsx;*.xlsm"
    fd.FilterIndex = 2
    fd.RestoreDirectory = True

    If fd.ShowDialog() = DialogResult.OK Then
        strFileName = fd.FileName
        TxtUpload.Text = ""
        TxtUpload.Text = strFileName

        Dim dt As New DataTable
        Try


            Dim constring As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFileName & ";Extended Properties=""Excel 12.0;HDR=YES;"""
            Dim con As New OleDbConnection(constring & "")

            con.Open()

            Dim myTableName = con.GetSchema("Tables").Rows(0)("TABLE_NAME")


            Dim sqlquery As String = String.Format("SELECT * FROM [{0}]", myTableName)
            Dim da As New OleDbDataAdapter(sqlquery, con)
            da.Fill(dt)


            MetroGrid1.DataSource = dt
            For Each c As DataGridViewColumn In MetroGrid1.Columns
                c.ReadOnly = True
                c.SortMode = DataGridViewColumnSortMode.NotSortable
            Next
            PictureBox1.Visible = False


            con.Close()
        Catch ex As Exception
            MsgBox(Err.Description, MsgBoxStyle.Critical)
        End Try
    End If


End Sub

Actually I think that the value is wrong as in excel 1,49 is get from a formula =D2*(1-0,01*E2)*(1-0,01*F2)*2,44 but if the problem is the following, how can I import the right value?

UPDATE: Actually i've found the problem, the data in client excel file is formatted to 0,09 so the value of 1,39 become 1,49 so the value was the right one.

UPDATE 2 By setting IMEX=1 and HDR=NO i get the correct value but i still need the header.

Looks like a float conversion problem. I believe Excel defaults to float. You want decimal probably. Try creating the columns (with data types) before you populate the data table. — Jacob H, Sep 03 '19 at 14:33
@JacobH actually i can't define datatable datatype as each loaded excel file will be different so the column names will ever be different and even the data in datatable.. — NiceToMytyuk, Sep 03 '19 at 14:35
You can try [this](https://stackoverflow.com/questions/10102149/what-is-imex-within-oledb-connection-strings). I think a bigger problem is how you are going to get the data into MySQL if you don't really know the columns or data types to begin with. — Jacob H, Sep 03 '19 at 14:43
@JacobH actually that part yet works, the user after loading excel file will choose which excel column (DataGridView column) will correspond to database columns names — NiceToMytyuk, Sep 03 '19 at 14:45
Have you accounted for cases where a user would choose text data to be inserted into an integer column? Or are you using character types for all columns? — Jacob H, Sep 03 '19 at 14:47
@JacobH actually after the user defined Excel column with MySQL columns i set the data in another dataTable with correct dataTypes, anyway i just updated the question, the issue is that that client excel file has format type set to 0,09 instead of numeric.. — NiceToMytyuk, Sep 03 '19 at 14:49

score 1 · Accepted Answer · answered Sep 03 '19 at 16:31

The issue was that the values in excel was formatted so they wasn't numeric.

The solution was to set HDR to NO and IMEX to 1 in connection string of OLEDB

As i anyway needed the header from excel i just done the following by manupulating the datatable before loading DataGridView source

            Dim i As Integer = 0
            For Each col As DataColumn In dt.Columns
                col.ColumnName = dt.Rows(0)(i).ToString
                i += 1
            Next
            dt.Rows.Remove(dt.Rows(0))

            MetroGrid1.DataSource = dt

Excel to DataTable importing wrong values

1 Answers1