Read Data from Excel with Mixed Data Type

?? does it err in getting the ColumnHeaders??
because the data seems to be same

Also When you read the data in Dataset it automatically judges the dataType, for eg in your case the Second Column should automatically be Integer/Double (it goes for Double as it is safer in case large values are to be accommodated)

I think HDR=Yes is the default is it ???

because I didn't give it in the Connection String and it still picked up the Column headers properly....

Hi Arif_eqbal, Yes it should be the default, but since he still gets it in his dataset. Unless it is not the first row. Thats why I also included the IMEX=1

right

Hi,

I tried to include Extended Properties="Excel 8.0;HDR=Yes;IMEX=1"
but problem could not be solved.

Infact, my Excel sheet NOT only first row contains columnnames (refer below example).
The 1st column contains all Text datatype, so no problem.
But column2 and column3 contain mixed datatype, it will be treated as Number columns hence return NULL for the headername.
If manually reformat the column2 and column3 as Text datatype, then I can get the columnheader
but all the numbers data would become NULL.
Not sure if I have made myself clear....any help would be much appreciated. Thanksss


=================
Data in Excel :--
=================
Column1Header  Column2Header  Column3Header
A                          1                          1,000
B                          2                          2,000
C                          3                          3,000

Column1Header  Column2Header  Column3Header
X                         4                          4,000
Y                         5                          5,000
Z                         6                          6,000

=======================
But the return data in dataset:--
=======================
Column1Header  Null                            Null
A                          1                          1,000
B                          2                          2,000
C                          3                          3,000

Column1Header  Null                           Null
X                         4                          4,000
Y                         5                          5,000
Z                         6                          6,000

I don't know but I tried this code I am posting with a file with Mixed dataType as you say, and it's picking up things just fine.
It smartly takes the headers and the data.

Try this code just pass your file name to the function and see if the DataGrid is filled properly

Private Function FillDataTable(ByVal FlName As String) As Boolean
        Dim CnStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
           "Data Source=" & FlName & ";" & _
           "Extended Properties=""Excel 8.0;"""
        Dim DT As New DataTable
        Dim DA As New OleDbDataAdapter("Select * from [Sheet1$]", CnStr)
        Try
            DA.Fill(DT)
            Me.DataGrid1.DataSource = DT

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        Return True
    End Function

Yep, I tried it too with your data, and it works just fine.

Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
MyConnection = New System.Data.OleDb.OleDbConnection( _
                  "provider=Microsoft.Jet.OLEDB.4.0; " & _
                  "data source=c:\test.xls; " & _
                  "Extended Properties=""Excel 8.0;IMEX=1;HDR=YES""")

        Dim DT As New DataTable
        Dim DA As New OleDbDataAdapter("Select * from [Sheet1$]", MyConnection )
        Try
            DA.Fill(DT)
            Me.DataGrid1.DataSource = DT

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        Return True
    End Function