Data import from Excel to GridView

Question

Hi,

 

with the below code im importing data from excel to gridview, Data is importing successfully, but if column have text and number data then it will take only either number or text data, if take number data then shows blank in text field data.

 

Example below excel file

Item Code	Item Description
'000001	Item A
2	Item B
3	Item C

 

In this case First row 000001 shows blank in GridView 

 

Dim conn As OleDbConnection

Dim dtr As OleDbDataReader

Dim dta As OleDbDataAdapter

Dim cmd As OleDbCommand

Dim dts As DataSet

Dim excel As String

Dim OpenFileDialog As New OpenFileDialog

OpenFileDialog.InitialDirectory = My.Computer.FileSystem.SpecialDirectories.MyDocuments

OpenFileDialog.Filter = "All Files (*.*)|*.*|Excel files (*.xlsx)|*.xlsx|CSV Files (*.csv)|*.csv|XLS Files (*.xls)|*xls"

If (OpenFileDialog1.ShowDialog(Me) = System.Windows.Forms.DialogResult.OK) Then

excel = OpenFileDialog1.InitialDirectory + OpenFileDialog1.FileName

conn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excel + ";Extended Properties=Excel 12.0;")

dta = New OleDbDataAdapter("Select * From [Sheet1$]", conn)

dts = New DataSet

dta.Fill(dts, "[Sheet1$]")

DataGridView1.DataSource = dts

DataGridView1.DataMember = "[Sheet1$]"

conn.Close()

End If

 

Thanks

Basit.