Error: <target>.ColumnName and <source>.ColumnName have conflicting properties: DataType property mismatch.

Question

I am trying to merge multiple excel files using DataTable.Merge() option

    For Each fileName As String In Directory.GetFiles("C:\TEMP\", "*.xls")
        Dim connectionString As String = String.Format("Provider=Microsoft.Jet.OLEDB.4.0; data source={0}; Extended Properties=""Excel 8.0;HDR=NO;IMEX=1;""", fileName)
        Dim adapter As New OleDbDataAdapter("SELECT * FROM [Sheet1$]", connectionString)
        Dim ds As New DataSet
        adapter.Fill(ds, "anyNameHere")
        Dim TempTable As DataTable
        TempTable = ds.Tables.Item("anyNameHere")
        table1.Merge(TempTable)
        MsgBox(fileName)
    Next
    DataGridView1.DataSource = table1
    MsgBox(table1.Rows.Count)

But gives following error while merging

<target>.ColumnName and <source>.ColumnName have conflicting properties: DataType property mismatch.

This is due to one column in excel is read as text and another as double while both have numeric values.

To avoid this I also mentioned IMEX=1 in connection string, still getting this error. pls help

Answer 1

If the columns are numeric, correct the xls file treating that column as text.
Wouldn't you want the columns to be structurally same, when you merge the data?

Answer 2

Use MissingSchemaAction.Ignore as MissingSchemaAction parameter in Merge

table1.Merge(TempTable, True, MissingSchemaAction.Ignore)

Answer 3

thanks for your help Sachin! your suggestion regarding adding MissingSchemaAction.Ignore helped me big time!=)