Facing issue while adding New columns to an Existing CSV file

Shell Programming and Scripting
1

Hi I am trying to add new columns to an existing csv file and values of the newly added columns will be empty, Consider below as my input csv file,

ID,Start_Date,Maturity_Date
1,23-Aug-24,13-Sep-24
2,23-Aug-24,15-Oct-24

I need to add four new headers "Product_Class,Deal_Code,Settlement_Currency,Valuation_Fixing_Date" and its values should be empty and my expected output is as below

ID,Start_Date,Maturity_Date,Product_Class,Deal_Code,Settlement_Currency,Valuation_Fixing_Date
1,23-Aug-24,13-Sep-24,,,,,
2,23-Aug-24,15-Oct-24,,,,,

i tried using below awk cmd

sed -i '1s/\r$//' CFG_CALYPSO_NONSD_METREC_06212024.csv

awk 'BEGIN {FS=OFS=","} NR==1 {print $0 ",Product_Class,Deal_Code,Settlement_Currency,Valuation_Fixing_Date"} NR>1 {print $0 ",,,,,"}' CFG_CALYPSO_NONSD_METREC_06212024.csv > temp.csv && mv temp.csv CFG_CALYPSO_NONSD_METREC_06212024.csv

its adding the new headers ,but the empty values of the new headers are getting created in the new line like below

ID,Start_Date,Maturity_Date,Product_Class,Deal_Code,Settlement_Currency,Valuation_Fixing_Date
1,23-Aug-24,13-Sep-24
2,23-Aug-24,15-Oct-24

Can someone suggest me to achieve the requirement as below,

ID,Start_Date,Maturity_Date,Product_Class,Deal_Code,Settlement_Currency,Valuation_Fixing_Date
1,23-Aug-24,13-Sep-24,,,,,
2,23-Aug-24,15-Oct-24,,,,,
2

how about:

awk -v add='Product_Class,Deal_Code,Settlement_Currency,Valuation_Fixing_Date' 'BEGIN {FS=OFS=","} FNR==1 {addN=split(add, t, FS)+1;print $0 OFS add;next} NF+=addN' myInputFile
1 Like
3

@vgersh99 i will try this and let you know

4

@Loganayaki , your awk command is okay.
But your sed command only removes a WinDOS CR (\r)in line 1.
Without the restriction it works on all lines:

sed -i 's/\r$//' CFG_CALYPSO_NONSD_METREC_06212024.csv

You can combine the two operations in sed

sed -i 's/\r$//; 1s/$/,Product_Class,Deal_Code,Settlement_Currency,Valuation_Fixing_Date/; 1!s/$/,,,,/' CFG_CALYPSO_NONSD_METREC_06212024.csv

or in awk

awk 'BEGIN {FS=OFS=","} {sub(/\r$/, "")} NR==1 {print $0 ",Product_Class,Deal_Code,Settlement_Currency,Valuation_Fixing_Date"} NR>1 {print $0 ",,,,"}' CFG_CALYPSO_NONSD_METREC_06212024.csv > temp.csv && mv temp.csv CFG_CALYPSO_NONSD_METREC_06212024.csv

A variable helps to shorten this a bit:

fn="CFG_CALYPSO_NONSD_METREC_06212024.csv"
awk 'BEGIN {FS=","} {sub(/\r$/, "")} NR==1 {print $0 ",Product_Class,Deal_Code,Settlement_Currency,Valuation_Fixing_Date"} NR>1 {print $0 ",,,,"}' "$fn" > "$fn.tmp" &&
mv "$fn.tmp" "$fn"

BTW I count 4 commas to be added.

5

I think I might be missing somethimg here. If you want to get rid of the dos line feed run dos2unix. That will fix the dos nonsince then run the awk and sed to fix the other thimg. BTW there is a unix2dos command if you want to add the crlf at the end of each line.