Data Transformation Parsing Delimited File Financial Figures?
Answered
I have a CSV file that I would like to import to my data warehouse using Yellowfin's Data Transformations. That data has financial figures formatted using standard accounting formatting including dollar signs and parentheses for negative values.
How the heck am I supposed to parse these?
When I try to convert "$3.00" to numeric it chokes. I can't use a right substring because I don't know how long the number will be. The data calculator offers only numeric operators (+ - * /) so those are no help and there is no way to use free-hand SQL. Not sure how I would handle the harder case of parsing "($0.60)".
The same question
Hi Stephen,
I found that the "Find and Replace" utility did the job for me:
I made 3 rules:
Find ( and replace it with <nothing>
Find ) and replace it with <nothing>
Find $ and replace it with <nothing>
If that doesn't work for you then I guess your spreadsheet is formatted differently than mine, in which case could you please send it across to me (or part of it)
regards,
David
Hi Stephen,
I found that the "Find and Replace" utility did the job for me:
I made 3 rules:
Find ( and replace it with <nothing>
Find ) and replace it with <nothing>
Find $ and replace it with <nothing>
If that doesn't work for you then I guess your spreadsheet is formatted differently than mine, in which case could you please send it across to me (or part of it)
regards,
David
That might work. If you replace the left parentheses with a negative sign instead of nothing it might even get the sign right.
That being said, I've decided to explore alternative ETL tools that have deeper feature sets.
That might work. If you replace the left parentheses with a negative sign instead of nothing it might even get the sign right.
That being said, I've decided to explore alternative ETL tools that have deeper feature sets.
yes, that's a much better idea than mine, definitely replace the left parentheses with a negative sign.
Please let us know how your exploration for deeper features goes.
regards,
David
yes, that's a much better idea than mine, definitely replace the left parentheses with a negative sign.
Please let us know how your exploration for deeper features goes.
regards,
David
Replies have been locked on this page!