Please help of the job design

[venkata pradeep]

Hi,

The requirement is we have a .csv file which comes from external server with having the data like
123,10,10,20," 465,45 "
123,10,10,10," (456,45)" (amount field)
and if i open the same .csv file with XL sheet it will show the amount field data for this " (456,45)" as -456,45.

for " 465,45 we kept the logic with the combination of convert(',','',convert('"',''(col)) and is working fine.

But when using the similar kind of logic it is not working for the second row of data.i am not able to recognize with index function.
Please help me on the design to remove the " ( or - symbols and need to populate the value as decimal value.

Please help on this ASAP.
Thanks in Advance.

[tsamui]

Use Trim fuction with option A.
Trim(col, '-', A). Repeat the same thing for ')' ans '('.

[venkata pradeep]

Hi thanks for u r reply..

But need to put this in if condition .......?

Rd,
pr.

[tsamui]

Use the below function. It will remove '-', '(', '(' and double quote and comma.

Convert('(),-"','', col)

I have tested with the value "(-123,45)" and it returning 12345.

Let me know if it satisfy your requirement.

[venkata pradeep]

Actaully it will come either way like if we see it from unix box it will showing " (123,45)" and if we open directly from windows with XL it is showing -123,25.
so finally i should move this record to target like decimal value(00000.00).

Please note the there is a space between quote(") and brace('(').please give me some idea if u come across sutiation like this.

Rd,
pr.

[chulett]

Parens are an accounting mechanism to show negative numbers, so you'll find () around a negaive number rather than a minus sign. You'll need a mechanism to recognize that the parens are there and if found, remove them and then after converting the string to decimal, multiply the end result by -1.

Excel understands this and does that automatically... which doesn't really matter as you'll be processing the csv file.

[qt_ky]

Good catch, Craig. Venkat, don't change the data. First understand the data and take appropriate action.