convert string to float in azure data factory in copy activity

Question

I have a csv file in mapping all source column ha string type and in destination some column have float ,varchar ,int type. When i run the pipeline activity fail and show error.
Error message:
ErrorCode=TypeConversionFailure,Exception occurred when converting value '' for column name 'BENE_HIC_NUM' from type 'String' (precision:, scale:) to type 'Double' (precision:15, scale:255). Additional info: Input string was not in a correct format.

Answer 1

Hello @Jamshed Salik ,

Thanks for the question and using MS Q&A platform.

From your error - it looks like there are null values in your field - '' - null string - is encountering the error while converting to float.

You could try the @Nandan Hegde approach - if my understanding is correct he has not mentioned explicitly the type as float to perform the copy activity. If this is not working for you, we will have to make use of the Mapping Data flow.

Because copy activity in Azure data factory only allow us to perform data movement as is. Handling type conversion from string to float directly is not available in copy activity. To do any kind of check or transformation we should consider data flows.

You can consider derived column transformation for your case to check if value is empty or not and type cast using the toFloat function. For all other strings, you can set it as a zero or any value as per your requirement

iif(isFloat(Field1),toFloat(Field1),0)  

Hope this will help. Please let us know if any further queries.

------------------------------

• Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
• If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators

Answer 2

Hey,
Assuming all the values in that column can be explicitly cast into numerical data type and there are no string values that are causing the issue,
you can use the mapping part in copy activity and us the below mapping example format :

{"type":"TabularTranslator","mappings":[{"source":{"name":"Period_FYTD"},"sink":{"name":"Period_FYTD"}},{"source":{"name":"Col2","type": "Decimal"},"sink":{"name":"Col2"}}]}