Vb.NET Read Excel Missing Data

Question

Private Sub readexcel()
    Dim cnn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & txtFileName.Text & "; Extended Properties=Excel 12.0;")

    Dim oconn As New OleDbCommand("select * from [Sheet1$]", cnn)
    cnn.Open()
    Dim adp As New OleDbDataAdapter(oconn)
    Dim dt As New DataTable()
    Dim ds As DataSet
    adp.Fill (dt)

    dgvExcelData.SelectionMode = DataGridViewSelectionMode.FullRowSelect
    dgvExcelData.EditMode = DataGridViewEditMode.EditProgrammatically
    dgvExcelData.DataSource = dt
End Sub

The above code reads Excel into Grid View,but some data will missing. Could someone tell me why?

enter image description here

Try it with IMEX=1 in your connection string. IMEX=1; tells the driver to always read "intermixed" (numbers, dates, strings etc) data columns as text. See this — Siddharth Rout
– Siddharth Rout, Commented Apr 26, 2013 at 19:13
@SiddharthRout Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & txtFileName.Text & "; Extended Properties=Excel 12.0;IMEX=1; correct? — John Walker
– John Walker, Commented Apr 26, 2013 at 19:18
@SiddharthRout same,data still missing, i uploaded my .xlsx dl-web.dropbox.com/get/… — John Walker
– John Walker, Commented Apr 26, 2013 at 19:42

Siddharth Rout · Accepted Answer · 2013-04-26 20:57:56Z

2

By default, ACE reads the first 8 rows of data and use that to decide the data type of each column.

If the data in those 8 rows in a column is of the same type then that type is assumed else it will assume text by default. After 8 columns it doesn't check it.

If your columns have mixed data types then you will have to make a small change in the registry.

Go to

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Excel

and change the value of the key TypeGuessRows from 8 to 0

enter image description here

Now test the below code

Private Sub Button1_Click(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles Button1.Click
    Dim strCon As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & txtFileName.Text &
                           "; Extended Properties=""Excel 12.0 XML;HDR=YES;IMEX=1;"""

    Dim cnn As New OleDbConnection(strCon)

    Dim oconn As New OleDbCommand("select * from [Sheet1$]", cnn)
    cnn.Open()

    Dim adp As New OleDbDataAdapter(oconn)
    Dim dt As New DataTable()
    adp.Fill(dt)

    dgvExcelData.SelectionMode = DataGridViewSelectionMode.FullRowSelect
    dgvExcelData.EditMode = DataGridViewEditMode.EditProgrammatically
    dgvExcelData.DataSource = dt
End Sub

And this is the output

enter image description here

edited Apr 26, 2013 at 20:57

answered Apr 26, 2013 at 19:36

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

John Walker Over a year ago

how come the NO K/P LAMA are auto rounded...example 8051815355155 will become 8051815355160

Siddharth Rout Over a year ago

Sorry? I am not sure what are you asking?

John Walker Over a year ago

change the NO KP LAMA to Number Format, then the number will auto rounded...why?(i have saved the dataset data to excel file)

Collectives™ on Stack Overflow

Vb.NET Read Excel Missing Data

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related