When you try to extract the contents of an excel sheet to a datatable through .Net code you may find some datatable columns having no or some values in it.
The datatable is not having all the values as it is there in the excel sheet it is extracting from.

The actual reason behind it is that the column is having values of different datatypes.

For extracting  records from such Excel sheets developer need to use following connection string:

conn = New System.Data.OleDb.OleDbConnection( _
“provider=Microsoft.Jet.OLEDB.4.0; ” & _
“data source=” & strExcelFilePath & “; ” & _
“Extended Properties=””Excel 8.0;IMEX=1;HDR=NO;TypeGuessRows=0;ImportMixedTypes=Text”””)

Vb.Net Code Snippet:

[Code Start]

Dim conn As System.Data.OleDb.OleDbConnection
Dim oDataset As New System.Data.DataSet
Dim oDataTable As New DataTable
conn = New System.Data.OleDb.OleDbConnection( _
“provider=Microsoft.Jet.OLEDB.4.0; ” & _
“data source=” & strExcelFilePath & “; ” & _
“Extended Properties=””Excel 8.0;IMEX=1;HDR=NO;TypeGuessRows=0;ImportMixedTypes=Text”””)
If conn.State = ConnectionState.Closed Then
conn.Open()
End If
‘ Select the data from Sheet1 of the workbook.
dAdapter = New System.Data.OleDb.OleDbDataAdapter(“SELECT * FROM [Sheet1$]”, conn)
dAdapter.Fill(oDataset)
oDataTable = oDataset.Tables(0)
conn.Close()
dAdapter.Dispose()
oDataset.Dispose()

[Code End]

Note: strExcelFilePath  is the string holding the selected excel file path.