Mixed Data in Datagridview from Excel

Ok here is the scenario.

My Excel File has 30k rows with mixed data. I am using this code to populate the DGV from Excel

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

---

The code works fine but DGV misses some data. On searching, I came across THIS post by jm. I did the registry fix and every thing was ok.

I know I can use the Open XML SDK but that is not the reason for this post. I am simply curious.

My Question

I want to know if there is a way where the user doesn't need to modify the registry and the above code works for xl2007+ Files (Not text/csv Files so not sure if schema.ini can be used for xlsx/xlsm)

Let me know if anyone wants a sample data?

OS: Win 7 64 Bit
VS 2010 Ultimate 64 Bit
MS Office(not that it matters since using ole): 32 bit

Schema.ini will not work since it's only used with the Text file driver.

If you don't want to edit the registry so that the driver scans more lines than the default 8 (the valid values are 0 - 16, where 0 will scan up to 16384 lines) to determent the data type you only have two possible solutions. Either manipulate the data and insert a dummy row at the top containing data of the expected data type or change the formatting of the cells in Excel from General to Text (or even better: Don't use Excel to store flat file data :)).

Quote:

---

Either manipulate the data and insert a dummy row at the top containing data of the expected data type or change the formatting of the cells in Excel from General to Text.

---

Thank JA. Yes I am aware of these two things but what if the user doesn't have MS Office installed then how would the user edit the data? The idea is to make this as painless as possible.

The formatting should then be provided by the one that has created the file. However if this is not possible the only thing I can think of is that you add each column to the DataTable before you fill it with the adapter. Obviously you need to know the column names for this to work.

Code:

---

dt.Columns.Add("ColumnName1").DataType = System.Type.GetType("System.String")
dt.Columns.Add("ColumnName2").DataType = System.Type.GetType("System.Int32")

---

1. I guess in that case the user is stuck as he/she wouldn't know the headers is the file is being generated automatically.
2. I even thought of programmatically changing the registry value for TypeGuessRows but realized that if 16384 lines have the same format and then 16385th line has a different format then changing TypeGuessRows to 0 also won't help!

Guess there is no simple way around it other than what has already been discussed above.

I will still keep this thread open for couple of days in case someone comes up with a clever way to handle this.

If the Excel file has a header you can read the very first line (with HDR=No; in the ConnectionString), create a DataTable with the same headers and correct type, reconnect and read it into the DataTable

Perhaps the following might work. Not sure as I don't have a sheet with that much data here at home.

Code:

---

Dim Builder As New OleDbConnectionStringBuilder With
    {
        .DataSource = "Your File Name",
        .Provider = "Microsoft.ACE.OLEDB.12.0"
    }
Builder.Add("Mode", "Read")
Builder.Add("Extended Properties", "Excel 12.0; HDR=Yes;TypeGuessRows=0;")

---

The problem isn't in the connection string, it's in reading the data. The ACE and Jet engine reads the first 8 rows (by default) to determent the type of each column. Say that the first column only contains integers in the first 8 rows but have a string later on, in that case that particular field will be set to NULL instead of getting populated with the actual string value (it will not throw any exception but just fail to read the date) since it assumed that the field should be an integer. In other words, you lose that data.

See how this might be a starting point for reading mixed types

SkyDrive VS2010 project