CODEWITHSUNDEEP
excelvb.netdatatable
Vengat
Vengat

Reputation: 235

Import from excel to data table vb.net

I have imported the excel sheet to data table by using the below code but the few cell value could not be loaded properly. instead of the cell value it is displayed as null.
Please refer the attached the image of the excel and data table.

Dim conStr As String = ""

conStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes'"
conStr = String.Format(conStr, Path)
Dim connExcel As New OleDbConnection(conStr)
Dim cmdExcel As New OleDbCommand()
Dim oda As New OleDbDataAdapter()
cmdExcel.Connection = connExcel
'Get the name of First Sheet
connExcel.Open()
dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
Dim SheetName As String = ""
If dtExcelSchema.Rows.Count > 0 Then
    SheetName = dtExcelSchema.Rows(dtExcelSchema.Rows.Count - 1)("TABLE_NAME").ToString()
End If
connExcel.Close()
'Read Data from First Sheet
connExcel.Open()
cmdExcel.CommandText = "SELECT * From [" & SheetName & "]"
oda.SelectCommand = cmdExcel
oda.Fill(dt)
dt.TableName = SheetName.ToString().Replace("$", "")
connExcel.Close()
Return dt

enter image description here

enter image description here

Upvotes: 2

Views: 24309

Answers (1)

Hadi
Hadi

Reputation: 37358

You are facing this issue because your column contains mixed data types, so the OLEDB provider will take the dominant data type and convert all other datatype values to NULL.

Try adding IMEX=1 to the extended properties in the connection String:

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'"

If it doesn't work, try adding a dummy row after the Header, in this row add a string value ex xyz and in the code delete this row after reading the data.

Upvotes: 3

Related Questions