Shell script to format a .CSV data

Shell Programming and Scripting
1

Hi There

I needed to write a Unix shell script which will pick up the data from a .CSV file and reformat it as per the requirement and write it to another .CSV file.

Currently I am in the proess of Data Import to "Remedy System" (A one kind of incident mangement Application) and this application accepts the Data in a particular format, Hence I want to accomplish this task with the Script without manual intervension.

My Original data loooks some what like this,

AcquiredMethod, AssetID, AssetLifecycleStatus, SC1, SC2, DC1, DC2
Vendor Owned, ATESTIMPORT10, Down, a, b, c, d

This has to be re-formatted like this

AcquiredMethod, AssetID, AssetLifecycleStatus, C, S/D, 1or2
Vendor Owned, ATESTIMPORT10, Down, a, S, 1
Vendor Owned, ATESTIMPORT10, Down, b, S, 2
Vendor Owned, ATESTIMPORT10, Down, c, D, 1
Vendor Owned, ATESTIMPORT10, Down, d, D, 2

I know I am a bad in explaining, however if you go through the excel it will be little clear.

The condition is if there is no data in any of the 4 columns (SC1 or SC2 or SC3 or SC4) the same record will not be present in the out CSV file.

Can anyone please give the script for the above task. I would really appreciate that.

Thanks in Advance,
Uday

2 
$ cat in.txt
AcquiredMethod, AssetID, AssetLifecycleStatus, SC1, SC2, DC1, DC2
Vendor Owned, ATESTIMPORT10, Down, a, b, c, d
$
$
$ cat sply.sh
#!/usr/bin/ksh

nawk -F"," 'BEGIN{OFS=","}{ if (NR==1) {
                        print "AcquiredMethod, AssetID, AssetLifecycleStatus, C, S/D, 1or2"
                } else {
                        print $1,$2,$3,$4,"S",1
                        print $1,$2,$3,$5,"S",2
                        print $1,$2,$3,$6,"D",1
                        print $1,$2,$3,$7,"D",2
                }}' in.txt



$
$
$ ./sply.sh
AcquiredMethod, AssetID, AssetLifecycleStatus, C, S/D, 1or2
Vendor Owned, ATESTIMPORT10, Down, a,S,1
Vendor Owned, ATESTIMPORT10, Down, b,S,2
Vendor Owned, ATESTIMPORT10, Down, c,D,1
Vendor Owned, ATESTIMPORT10, Down, d,D,2

HTH

3

Hi

Thanks!!. That was very quick and it works 95% correct. Only exception here is it create a record in every instance.., i.e. even if SC1 or SC2 or DC1 or DC2 is blank.

I mean we tested this script in all the possibalities, of SC1,SC2,SC3 and SC4, i.e. with all the 16 possibalities and it created total 64 records, where as it should have created only 32. Hence we need to eliminate the other 32 records.

Could you advice on this.

Once again thank you very much for your swift and accurate reply.

Thanks
Uday

4

You mention SC3 and SC4 but the data example you gave is:

AcquiredMethod, AssetID, AssetLifecycleStatus, SC1, SC2, DC1, DC2

BY DC1 and DC2 do you mean SC3 and SC4

5

Yes I am sorry you are right

.. It is DC1 and DC2

uday

6

I would prefer that you post some examples - provide sample input and expected output. Cover the cases you mentioned in your samples - that makes is easier and saves our time.

7

Hi

Please refer to the attachement attached to with this reply to see an example and all the explanation regarding this. Expecting your help with this.

Thanks and Regards
Uday

8

From your input/output, you simply don't want to print lines where any of SC1, SC2, SC3, SC4 is empty - thats simple enough that you could have done it yourself:

#!/usr/bin/ksh

nawk -F"," 'BEGIN{OFS=","}{ if (NR==1) {
                        print "AcquiredMethod, AssetID, AssetLifecycleStatus, C, S/D, 1or2"
                } else {
                        if ($4 != "")
                                print $1,$2,$3,$4,"S",1
                        if ($5 != "")
                                print $1,$2,$3,$5,"S",2
                        if ($6 != "")
                                print $1,$2,$3,$6,"D",1
                        if ($7 != "")
                                print $1,$2,$3,$7,"D",2
                }}' input.csv

The if conditions do it - try a bit harder next time.

HTH

9

Thank you for your reply, and sorry to bother you so much!..

Thank you for you advice.

Uday