Data Transformation Parsing Delimited File Financial Figures?

Stephen Johnson shared this question 8 months ago
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)".

Comments (3)

photo
1

Hi Stephen,

I found that the "Find and Replace" utility did the job for me:

/AAAAAElFTkSuQmCCAA==

I made 3 rules:

Find ( and replace it with <nothing>

Find ) and replace it with <nothing>

Find $ and replace it with <nothing>

/8DNV8sMe8SPPsAAAAASUVORK5CYIIA

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

photo
1

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.

photo
1

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