Converting European Currency Format to United States

Question

Converting European Currency Format to United States

Rob-880668

Mr or Mrs. 500

Points: 573
More actions
January 7, 2011 at 2:40 pm

#157383

SSIS tries to drop the CSV value into NUMERIC(20,2) field. This works well as long as the currency is written in the United States format 123456.99. But the European format 123.455,99 causes it to crash when parsing. I changed the type from NUMERIC(20, 2) to CURRENCY and SSIS returned a conversion error. So the question is this. Is there a way to have SSIS directly convert the European format or will I need to read it in as a string, then manually change the radix and remove the thousands separator and then convert it to Unites States format? Any help is appreciated.
Thanks,
Rob

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

So long as every value will have 2 decimal places (regardless of what the radix format is like), this should do it... not sure how you'd use it in SSIS but perhaps it'll spark an idea for you...

--===== Create a test table. This is NOT a part of the solution

SELECT *

INTO #TestTable

FROM (

SELECT '123.455,99' UNION ALL

SELECT '123456.99'

)testdata (Amount)

--===== Preprocess the data to "normalize" it to a U.S. format

SELECT OriginalAmount = Amount,

NormalizedAmount = CASE LEFT(RIGHT(Amount,3),1)

WHEN ',' THEN REPLACE(REPLACE(REPLACE(Amount,',','D'),'.',''),'D','.')

ELSE Amount

END

FROM #TestTable

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Rob-880668 Mr or Mrs. 500 Points: 573 More actions · Answer 2

Thanks for the reply Jeff. I have considered something very similar. Since the data is coming in as a string <technically> since it comes in from a CSV file, I can read the field as a string and use an inline script or derived column in the data flow to perform any needed modifications. It would something fairly similar to your code. I was hoping that there would be a simpler solution. But there may not be... It wouldn't be the first time. 🙂

Phil Parkin SSC Guru Points: 246960 More actions · Answer 3

I've just played around with this and there does not appear to be a way to do it without writing a bit of code, as you've already anticipated.

I think I'd do it with a derived column: first replacing '.' with '<empty string>' and then ',' with '.' (which can be done in a single expression) should do it.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Rob-880668 Mr or Mrs. 500 Points: 573 More actions · Answer 4

@Phil

I agree and can find no better way either. That seems to be the simplest way and I'm all for that.

Thanks,

Rob