Shell program

[Vande Matram]

Hi,
what I need to do is this.
1. Read a file called Mar28.csv, the file contains only two filed. For example,
Citi Bank Payment,-30
Deposit,1349.2

2. If the second field contains a negative amount then I need to change it to
Citi Bank Payment,,-30
else if the second field is a positive number then I need to change it to
Deposit,1349.2,
I am trying to do this using shell programming.
I have been going round and round in circle for a while now without much success and if someone is kind enough to show it to me by giving an example I would really appreciate it.

[Paula J Frazer-Campbell · Accepted solution]

Hi

To pick up the negative amount and add an extra , then:-


cat Mar28.csv | sed 's/,-/,,-/' >Mar28.csv-new

This will change

Citi Bank Payment,-30

to:-

Citi Bank Payment,,-30

Can you attach and example of your cvs file?

Paula

[Steven Sim Kok Leong]

Hi,

Off the top of my head, following is one most inefficent way which I typed in a rush (untested):

#!/sbin/sh

lineno=1
lastline=`wc -l $1|awk '{print $1}'`

while [ "$lineno" -le "$lastline" ]
do
line=`head -$lineno $1|tail -1`
if echo $line|awk -F\, '{print $2}'|grep '\-' >/dev/null
then
echo $line|awk -F\, '{print $1,",,",$2}'
else
echo $line
fi
lineno=`expr $lineno + `
done

Hope this helps. Regards.

Steven Sim Kok Leong

[Steven Sim Kok Leong]

Hi,

Typo in my quick draft of a script:

lineno=`expr $lineno + `

should be:

lineno=`expr $lineno + 1`

Hope this helps. Regards.

Steven Sim Kok Leong

[A. Clay Stephenson]

Probably the best tool for something like this is awk (or Perl). The attached bank.sh creates an awk script 'on the fly' and calls awk.

bank.sh < Mar28.csv > Newfile
STAT=$?

if ${STAT} is non-zero, bad input data was detected.

[Ceesjan van Hattum]

awk 'BEGIN{FS=","} {printf "%s,",$1
if ($2<0) printf ",%s\n",$2
else printf "%s,\n",$2
}' INPUTFILE

Regards,
Ceesjan

[Rodney Hills]

Paula's sed is the most direct. However, If you wanted to use perl, then-

perl -pie 's/[^,],-/,,-/' thefile.csv

This will make the change and put the results back into the same file. The additional feature this has over Paula's is that if you forget and run the process twice on the same file, it will leave the file in the proper format. Paula's would just keep adding commas everytime is was run.

perl... Try it.. You'll like it...

-- Rod Hills

[Vande Matram]

Hi guys! Thank you very much for all your help.
I appreciate your feedback very much, and so everyone gets 10 points. However, to be frank with you lot of the stuff you said, went over my head because I am just starting out learning UNIX. :-) However, I ended up using a much simpler approach using awk utility. It is so simple it is amazing :-) and makes me proud of myself!! You won't believe it but this one line did all the work for me!
awk -F',' '{if ($2 > 0) {print $2","} else {print ","$2}}' Mar28.csv
Are you surprised? Thanks guys...you all are awesome!

[Vande Matram]

It's too bad I can't assign myself 10 pts! ;-)

[Ceesjan van Hattum]

Hi Vande,
Do you see any differences between your solution and mine? It's a good thing not to assign points to yourself.
Keep dreaming...