ISOLATING ERROR: OLE DB or ODBC error: type mismatch (exception from HRESULT: 0x80020005

[cjklatt]

Is there an efficient method to isolate an offending column or value mismatch?

OLE DB or ODBC error: type mismatch (exception from HRESULT: 0x80020005 (DISP_E_TYPEMISMATCH))

Spent better part of 2 days trying to load a new Excel source into an existing appended table with 100+ other excel sources and 1k columns. I knew what I was looking as far as a value that was out of spec but eventually and very inefficiently removed/split the data and reloaded until I got to the offending column which had a Text value in a Number column.

I updated the Excel file to remove the Text value and new file loaded with the Appended table no further errors.

Does anyone have any tricks to short cut this type of isolation when working with large combined datasets?

Many thanks in advance!

[DataInsights]

@cjklatt,

One approach is to set the column's data type as Text to allow both numbers and text to be imported. Then, create a custom column in Power Query that checks whether the value is a number:

Value.Is(Value.FromText([ColumnOfMixedValues]), type number)

[cjklatt]

Thanks - had thought about that but have over 1k columns so would be a lot to run this for every column. For the most part the data I am collecting from PM trackers is clean. But every so often in collection a numbers and texts and dates get mixed and could be in any of the columns.

In Query -the initial acquistion always works, the append doesnt error. Once the load runs the error hits at the end but cant tell where its at, just seems like there would be an easier way to isolate.