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.
Please help of the job design
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 7
- Joined: Tue Jul 27, 2010 5:50 am
- Location: bangalore
Please help of the job design
p.v.pradee preddy
Re: Please help of the job design
Use Trim fuction with option A.
Trim(col, '-', A). Repeat the same thing for ')' ans '('.
Trim(col, '-', A). Repeat the same thing for ')' ans '('.
Thanks&Regards
-------------------
Tsamui
-------------------
Tsamui
-
- Participant
- Posts: 7
- Joined: Tue Jul 27, 2010 5:50 am
- Location: bangalore
Re: Please help of the job design
Hi thanks for u r reply..
But need to put this in if condition .......?
Rd,
pr.
But need to put this in if condition .......?
Rd,
pr.
p.v.pradee preddy
Re: Please help of the job design
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.
Convert('(),-"','', col)
I have tested with the value "(-123,45)" and it returning 12345.
Let me know if it satisfy your requirement.
Thanks&Regards
-------------------
Tsamui
-------------------
Tsamui
-
- Participant
- Posts: 7
- Joined: Tue Jul 27, 2010 5:50 am
- Location: bangalore
Re: Please help of the job design
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.
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.
p.v.pradee preddy
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.
Excel understands this and does that automatically... which doesn't really matter as you'll be processing the csv file.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers