[RESOLVED] excel issue importing to dataset, cuts down values.

[sapator]

Hello.
I am using somthing like this to import excel to a dataset

("provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + TextBox1.Text + "';Extended Properties=""Excel 8.0;IMEX=1""")

This has an issue that on some cells it cut's down the complete value of it. So a value that will show as 3,55 but on the fx tab will show as 3,5566 will import as 3,55.
The issue can be fixed if I set IMEX=0 but on that case I am loosing all the initial rows. With IMEX =0 the fields would (as I see) take there initial form so It will not show 37,44 € string but D (DECIMAL) 37,445423

Another thing is that this will show OK on some worksheets and bad on other. So the question is how would I take the full fx value without changing IMEX=1 to IMEX=0 , is there another extended property to insert?

Thanks.

[sapator]

Seems that I have seen the problem.

The cells are in format "accounting number" with 2 places while other worksheets are in number format.

Regardless of that, is there a way to bypass the formatting and take the full number on cells?

[ChrisE]

Hi

try it like this

Public Class excelToListview
    Private objConnection As OleDbConnection
    Private objCommand As OleDbCommand
    Private objDataAdapter As OleDbDataAdapter
    Private objDataTable As System.Data.DataTable
   
    Private PrmPathExcelFile As String = "C:\TestExcelToAccess.xls"
    Private strConnectionString As String = _
    "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & PrmPathExcelFile & ";" & "Extended Properties=""Excel 8.0;"""
    '--------------------------------------------------------

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        objConnection = New OleDbConnection(strConnectionString)
        'select sheet
        objCommand = New OleDbCommand("SELECT * FROM [Sheet1$]", objConnection)
        'select Range
        'objCommand = New OleDbCommand("SELECT * FROM [Sheet1$A1:D10]", objConnection)

        'select with Letter
        'objCommand = New OleDbCommand("SELECT Artikelname, Liefereinheit FROM [Sheet1$] Where Artikelname Like 'p%'", objConnection)

        objDataAdapter = New OleDbDataAdapter
        objDataAdapter.SelectCommand = objCommand
        objDataTable = New System.Data.DataTable
        objDataAdapter.Fill(objDataTable)
        ListViewFillFromDataTable(ListView1, objDataTable)

        'Clean(up)
        objDataAdapter.Dispose()
        objDataAdapter = Nothing
        objCommand.Dispose()
        objCommand = Nothing
        objConnection.Dispose()
        objConnection = Nothing

    End Sub
    Public Sub ListViewFillFromDataTable(ByVal Lvw As ListView, ByVal Dt As System.Data.DataTable, _
                                        Optional ByVal UseItemStyleForSubItems As Boolean = False, _
                                        Optional ByVal ColumnsAutoSize As Boolean = True, _
                                        Optional ByVal FormatDecimal As String = "0.00")

        With Lvw
            .View = View.Details
            .GridLines = True
            .HideSelection = False
            .FullRowSelect = True
            .LabelEdit = False
            .Items.Clear()
            .Columns.Clear()
            'Header
            For i As Integer = 0 To Dt.Columns.Count - 1
                Dim Key As String = Dt.Columns(i).ColumnName
                .Columns.Add(Key)
                Select Case Dt.Columns(i).DataType.Name
                    Case "String", "DateTime", "Char", "Boolean"
                    Case Else
                        .Columns(i).TextAlign = HorizontalAlignment.Right
                End Select
            Next
            .BeginUpdate()
            .SuspendLayout()
            'fill Listview
            For i As Integer = 0 To Dt.Rows.Count - 1
                Dim Li As New ListViewItem
                Dim Row As DataRow = Dt.Rows(i)
                Li.Tag = Row
                Li.UseItemStyleForSubItems = UseItemStyleForSubItems
                For j As Integer = 0 To Row.ItemArray.GetUpperBound(0)
                    Dim s As String = Nothing
                    If Not Row.IsNull(j) Then
                        s = CStr(Row.Item(j))
                        If Row.Item(j).GetType.Name = "Decimal" Then
                            If Not String.IsNullOrEmpty(FormatDecimal) Then
                                s = Convert.ToDecimal(s).ToString(FormatDecimal)
                            End If
                        End If
                    End If
                    If j = 0 Then
                        Li.Text = s
                    Else
                        Li.SubItems.Add(s)
                    End If
                Next
                .Items.Add(Li)
            Next

            If ColumnsAutoSize Then
                .AutoResizeColumns(ColumnHeaderAutoResizeStyle.HeaderSize)
            End If
            .ResumeLayout()
            .EndUpdate()
        End With
    End Sub

regards
Chris

[sapator]

I see that you fill the listview with the datatable and do manips.
That will not do as the cut down values are already in the datatable from excel import.
Actually this might work as you have not used IMEX=1 on the connectionstring but as I've said, I need IMEX=1

[ChrisE]

well give it a try and see if it will work,
or just Jet.OLEDB

where do you want to Import the data to?

regards
Chris

[sapator]

Data is imported to a dataset -- datatables
Why are we using Jet 4.0? Isn't that an old version? I don't think it will run on 64bit machines or even read xlsx.
Again your sample will work but it does not have IMEX=1 . Without IMEX=1 I am loosing a couple or startup rows and columns as IMEX suggest multi formatting - mixed data columns that I have in the excel.
The problem is that on cell's the formatting is accounting number 2 decimals places after dot or comma separator. This will give me back for example 43.77 and not 43.7732, if I go to the excel sheet and format the cells as accounting number 4 decimals places then I get the correct result. I'm trying to completely ignore the formatting on columns and that will be done with not using IMEX but this disappear - extinguish important columns and rows. I has struggling with another bank excel sheet in the past and I was loosing data from cell's that will only be fixed with IMEX=1 , so every sample must use IMEX=1 .

Thanks.

[sapator]

So OK. Nothing will work with the standard oledb driver.

It works with custom .net excel dll apps though. So the solution is to switch to another excel dll .

[ChrisE]

Hi

have you tried to Insert the Excel sheet to a new Table in the Database?
you will have to edit the Provider and Excelversion

Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
        Dim sSql As String
        Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=D:\NWind.mdb")

        '//Import from Excel to new  Table
        sSql = "SELECT * INTO Tabelle1D From [Sheet1$] In " & _
                               "'C:\artikelliste.xls' 'EXCEL 8.0;' "

        '//Import from Excel and add to the same table from above
        'sSql = "Insert Into Tabelle1D Select * From [Sheet1$] In " & _
        '     "'C:\artikelliste.xls' 'EXCEL 8.0;' "
        con.Open()
        ExecuteSQL(con, sSql)
        con.Close()
        con = Nothing
    End Sub
    Public Function ExecuteSQL(ByVal Con As OleDb.OleDbConnection, _
                                     ByVal sSQL As String, _
                                     Optional ByRef ErrMessage As String = Nothing, _
                                     Optional ByVal TransAction As  _
                                     OleDb.OleDbTransaction = Nothing) As Integer
        ErrMessage = Nothing
        Try
            Dim Result As Integer = 0
            Using Cmd As New OleDb.OleDbCommand(sSQL, Con, TransAction)
                Result = Cmd.ExecuteNonQuery
            End Using
            Return Result
        Catch ex As Exception
            ErrMessage = ex.Message
            Return 0
        End Try
    End Function

regards
Chris

[sapator]

Why would I insert it to a database?
This way I must insert take back the database data, manipulate and insert again.
Also how would I insert to a database, something that is like this:

Column ----- Column --- Column
-Simulated Rowcolumn Simulated Rowcolumn Simulated Rowcolumn ------non inserted data
-Simulated Rowcolumn Simulated Rowcolumn Simulated Rowcolumn ------non inserted data
-----Non row inserted data
No row insert - Row inserts - row inserst
No row insert - Row inserts - row inserst
No row insert - Row inserts - row inserst
No row insert - Row inserts - row inserst

-----Empty non inserted row
No row insert - Row inserts - row inserst
No row insert - Row inserts - row inserst

--------Empty non inserted row, simulate end of file

-----Inserted rows that will not be inserted as end of file row has been simulated