convert string data to int
-
i am trying to import an access table in sql server. One of the columns Age is defined as Integer in SQL Server.
In Access, this feild has non-numeric values in some rows. I need to either enter 0 where there is non-numeric value or i need to replace it with some integer.
pls assist.
Pradeep Singh -
It depends! And there is no standard formula as per my knowledge. But I would prefer to import access table in a dummy sql table with varchar type of Age field and then perform all the update script with the help of
ISNUMERIC() function (to find all non numeric values). and then transfer data to the original table and then drop the dummy table.
Regards,
Sudhir
-
You can do it in SSIS by putting a Data Conversion component that converts the strings to int.
Map the error rows to a derived column that replaces the strings with a 0, and then merge the two flows together using a UNION ALL.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
Either the Data Conversion or the Conditional Split would work for this. Try to convert the value to the appropriate type, similar to:
(DT_I4)[input_column_name]If the cast is successful, the integer value would be added to the data pipeline. IF not, the conversion would fail and send the row to the component's error output. Make sure you configure the component to "Redirect Rows to Error Output" rather than "Fail Component", since the latter is the default behavior.
hth,
Tim
Tim Mitchell
TimMitchell.net | @tmitch.net | Tyleris.com
ETL Best Practices -
Sudhir's idea seems the least cumbersome to me ...
Or (and I am not sure whether this is even possible, because I have not tried importing from Access for ages...) use an Access query as your data source and do the required conversions and substitutions there.
-
I imported the access db in a staging database. From there all conversions were smooth. database didnt have huge number of records so used normal select into statement with conditions
Pradeep Singh
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply