converting datatype varchar to numeric
Daniel asked 6 years ago
Getting an error “converting datatype varchar to numeric”. Importing csv files from a number of source, so I’m using a folder source and once and a while some data in the amount fields are blank in the file instead of a 0 or an amount. Is there anyway to convert this before importing the data? Does anyone know of a script I could use?
If I add a task to run before each document, will it slow down the process?
Thanks, Dan
If I add a task to run before each document, will it slow down the process?
Thanks, Dan
Answers
Ethan Sorenson Staff answered 6 years ago
Hello Daniel,
I would recommend using a calculated column in your destination to check first if the field contains numbers, and if not to then insert a 0, or whatever default value you want. Below is a brief sample of the script you would need to run. Replacing “_Amounts” with whatever field you want to evaluate.
if _AMOUNTS.ToString() = string.empty or not IsNumeric(_AMOUNTS) then
return 0
else
return _AMOUNTS
end if
I would recommend using a calculated column in your destination to check first if the field contains numbers, and if not to then insert a 0, or whatever default value you want. Below is a brief sample of the script you would need to run. Replacing “_Amounts” with whatever field you want to evaluate.
if _AMOUNTS.ToString() = string.empty or not IsNumeric(_AMOUNTS) then
return 0
else
return _AMOUNTS
end if
Daniel replied 6 years ago thanks for the script, I’m sure it would work, but the following worked in my case:
return CInt(_sourcefield)
thanks again
Jim replied 5 years ago I’m having the same issue working with an Excel source file, but neither scrip is working for me.
On the first one when trying to validate the script I’m getting the message ‘IsNumeric is not declared’.
The second script will validate but when the map runs it’s coming back with the error about converting varchar to numeric.
Any further suggestions?