Forum Discussion
OLE DB or ODBC error: [DataFormat.Error] We couldn't convert to Number...
Hi Sai_Akash ,
Difficult to say exactly without more information, but the first thing I'd do is go to the query that is failing with this error, find any columns that are converted to a number type (Decimal or Whole Number/Integer) during the transformation, select them one by one and go to the Home tab > Keep Rows (dropdown) > Keep Errors. This should hopefully help you identify rows where a conversion error has occurred.
Pete
Hi Sai,
The error you posted implies that you still have a row with a non-numerical value in it.
Try adding a custom column like this, which you can then filter on to identify offending rows:
if try Value.Is(Number.From([ID]), type number) otherwise false then "Numeric" else [ID]Pete
Hi Sai_Akash ,
Difficult to say exactly without more information, but the first thing I'd do is go to the query that is failing with this error, find any columns that are converted to a number type (Decimal or Whole Number/Integer) during the transformation, select them one by one and go to the Home tab > Keep Rows (dropdown) > Keep Errors. This should hopefully help you identify rows where a conversion error has occurred.
Pete
- Hi Pete,
Thank you for your suggestion! I followed your advice and checked the query, focusing on columns that are being converted to a number type during the transformation. However, even when I specifically applied this to the id column, I’m still encountering the same issue.
I went to the Home tab, selected the “Keep Errors” option, and despite this, I’m unable to isolate the root cause of the conversion error for this column.
Would you be able to provide further guidance or suggest additional steps I can take to troubleshoot this? If possible, I’d like to discuss this in a DM personally to explore potential solutions more efficiently.
Thanks again,
Sai AkashHi Sai,
The error you posted implies that you still have a row with a non-numerical value in it.
Try adding a custom column like this, which you can then filter on to identify offending rows:
if try Value.Is(Number.From([ID]), type number) otherwise false then "Numeric" else [ID]Pete
