I’m importing a table which is supposed to have an integer primary key, but the data is messy and we find things like “6TEST3” or “CREDIT3” in this field. I want to read the table and use this field as an integer, however can’t convert it because of those rows.
Is there a way to remove a row if I can’t convert a field value to integer?
Solved
Filter table on data type of column
Best answer by rogier.helmus
Hi
Let's say your primary key field is the AdressID of a table with addresses, you could apply a data selection rule on the mapped table that only loads rows for which the primary key field is a numerical value. Example:
This works if I put the following values in the AddressWithAdressIdVarchar table:
Row 1 is loaded, but rows 2 and 3 are not.
However, you have to be mindful that the ISNUMERIC() function might not be fully working for your dataset. There are characters that the function will characterize is being numeric and that will fail when you put that value in an integer field. This issue is better explained in this article: https://www.sqlshack.com/an-overview-of-the-sql-server-isnumeric-function/ . The article also has 2 alternatives for the ISNUMERIC function at the end.
Hope this helps!
Kind regards, Rogier
+1Hi
Let's say your primary key field is the AdressID of a table with addresses, you could apply a data selection rule on the mapped table that only loads rows for which the primary key field is a numerical value. Example:
This works if I put the following values in the AddressWithAdressIdVarchar table:
Row 1 is loaded, but rows 2 and 3 are not.
However, you have to be mindful that the ISNUMERIC() function might not be fully working for your dataset. There are characters that the function will characterize is being numeric and that will fail when you put that value in an integer field. This issue is better explained in this article: https://www.sqlshack.com/an-overview-of-the-sql-server-isnumeric-function/ . The article also has 2 alternatives for the ISNUMERIC function at the end.
Hope this helps!
Kind regards, Rogier
+7Hi
Ninja-ed by
I think you should be able to filter rows by using NOT LIKE '%[^0-9]%' as a filter / data selection rule in your ODX/BU if you simply only want to load column values built out of only digits. There is also ISNUMERIC() which you could use in a transformation to fill out a flag field, but this allows for many types of input (money, scientific notation, etc.).
The question is whether you want to ignore “incorrect” data, or want to qualify it as such inside your data platform.
See also: https://www.sqlservercentral.com/articles/why-doesn%e2%80%99t-isnumeric-work-correctly-sql-spackle
+5Did the answers above resolve your issue? If so could you please help us by marking a best answer above? Please let us know if you have any follow up questions
Reply
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
