October 5, 2017 at 3:36 am
Hello,
I have an excel which as some columns with money amounts.
I am importing as string (500) and then, inside a data conversion task I change it to be a numeric of precision 28 and scale 12.
Inside my database the value should be stored as decimeal (28,12).
But I always get an error on the data conversion task saying that data would be lost.
Here is an example of the value we can find in excel:
43,415,184.70
Can you please help me understand?
Thank you
October 5, 2017 at 3:46 am
So do you have a data conversion operator as part of your data flow, or do you import directly to a table with a varchar(500) column and do the conversion from there? Certainly if you attempt to convert the example value you supplied to decimal(28,12), you get a different error from the one you described: Error converting data type varchar to numeric. This is because of the comma separators in the value. I would recommend that you remove those before attempting the conversion.
John
October 5, 2017 at 3:59 am
Hello,
Thank you for your reply.
The value comes like this from excel file . which is a str (500). Then I am trying to have a data conversion task to convert the value into numeric of precision 28 and stale 12 as in my database the filed is decimal (28,12).
You think that I should replace first the commas by nothing and only after do the conversion?
October 5, 2017 at 4:03 am
Yes, that's what I'm suggesting. But you haven't confirmed whether you're doing the conversion as part of the data flow, or after the data flow has moved the data into SQL Server.
John
October 5, 2017 at 4:20 am
thank you for your answer.
Data is pulled using source flat file and after I have a derivated column (where I do the replace of the commas. After I have a data conversion.
October 5, 2017 at 4:25 am
So its part of the flow , before I send it to the database
October 5, 2017 at 4:31 am
I'm hazarding a bit of a guess here, but I'm I imagine that SSIS doesn't like the commas. As it is importing the column as a string, and (true) numbers don't contain commas, it just fails (SSIS is very picky).
Instead, try removing the commas first and then converting the value. Working from memory, something like:=(DT_DECIMAL,12) (REPLACE([YourColumn], ",",""))
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 5, 2017 at 4:33 am
So in your data flow you remove the commas and then you do the conversion, and you're still getting errors? You'll probably need to set up a data watcher to see what the values look like after the column removal. Either that, or import directly into the database and do the cleanup there. What is the largest number you are attempting to import? Do all values have exactly two digits to the right of the decimal point? Could the fact that you're importing from Excel yet are using a flat file connection manager have anything to do with it?
John
October 5, 2017 at 4:52 am
I have just poted another post as I may have additional issue, maybe some columns are not treated as they should because they are inside the excel (csv file) as
"121,211,22.12"
Probably I will need to tell excel that commas should not be looked
October 5, 2017 at 6:18 am
I think I am not doing the things right in terms of replacement of the comma by null
How do you replace "12,122,222.22" commas for notjing , so that they don't appear ?
I am using a replacenull but this is putting everything to null instead of presenting me the result as: "12122222.22"
What is the best way of doing this inside a derivated column?
Can you please point me the right functional that I should use?
Thank you
October 5, 2017 at 6:23 am
river1 - Thursday, October 5, 2017 6:18 AMI think I am not doing the things right in terms of replacement of the comma by nullHow do you replace "12,122,222.22" commas for notjing , so that they don't appear ?
I am using a replacenull but this is putting everything to null instead of presenting me the result as: "12122222.22"
What is the best way of doing this inside a derivated column?
Can you please point me the right functional that I should use?
Thank you
Have a look at my previous post on how to use REPLACE.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 5, 2017 at 6:24 am
river1 - Thursday, October 5, 2017 6:18 AMI think I am not doing the things right in terms of replacement of the comma by nullHow do you replace "12,122,222.22" commas for notjing , so that they don't appear ?
I am using a replacenull but this is putting everything to null instead of presenting me the result as: "12122222.22"
What is the best way of doing this inside a derivated column?
Can you please point me the right functional that I should use?
Thank you
Use the expression that Thom posted, substituting your situation's column name for the "YourColumn" that appears in his example.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 5, 2017 at 6:28 am
Solved. I found the right replacing function and with your help of having to replace commas in order to work, thinks are now solved. Thank you for the support
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply