I have a csv file I'm trying to use an awk script with, basically what I want is to format the output and align the values in columns 5 to 9 in respect to the value in column 1. Here is the sample:
Input:
"DB Instance Identifier","Engine","Instance Type","MultiAZ","Reservation ID","State","Start Time","Reserved Engine","Reserved DB Instance Class"
"alpha-db-dev","postgres","db.t4g.micro",false,"alpha-db-dev-26aug2022","active","2022-08-26","postgresql","db.t4g.micro"
"alpha-db-prod","postgres","db.t4g.small",true,"alpha-db-prod-26aug2022","active","2022-08-26","postgresql","db.t4g.small"
"alpha-db-staging","postgres","db.t4g.micro",false,"alpha-db-staging-26aug2022","active","2022-08-26","postgresql","db.t4g.micro"
"beta-db-dev-primary","aurora-postgresql","db.t3.medium",false,"charlie-db-dev-8dec2021","active","2021-12-08","postgresql","db.t3.micro"
"beta-db-prod-primary","aurora-postgresql","db.r5.xlarge",false,
"beta-db-prod-replica","aurora-postgresql","db.r5.xlarge",false,
"beta-db-staging-primary","aurora-postgresql","db.t3.medium",false,
"charlie-db-dev","postgres","db.t3.micro",false,
"charlie-db-prod-dms","postgres","db.m4.4xlarge",true,
"charlie-db-prod-dms-replica","postgres","db.m4.4xlarge",false,
"charlie-db-staging","postgres","db.t2.large",false,
"charlie-db-staging-loadtest","postgres","db.m4.4xlarge",false,
"charlie-kong-db-dev","postgres","db.t3.micro",false,
"charlie-kong-db-prod","postgres","db.m4.large",true,
"charlie-kong-db-staging","postgres","db.t2.small",false,
"delta-db-prod","mysql","db.t3.small",true,
"delta-db-recon-prod","mysql","db.t3.micro",false,
"delta-db-staging","mysql","db.t3.micro",false,
Desired output:
"DB Instance Identifier","Engine","Instance Type","MultiAZ","Reservation ID","State","Start Time","Reserved Engine","Reserved DB Instance Class"
"alpha-db-dev","postgres","db.t4g.micro",false,"alpha-db-dev-26aug2022","active","2022-08-26","postgresql","db.t4g.micro"
"alpha-db-prod","postgres","db.t4g.small",true,"alpha-db-prod-26aug2022","active","2022-08-26","postgresql","db.t4g.small"
"alpha-db-staging","postgres","db.t4g.micro",false,"alpha-db-staging-26aug2022","active","2022-08-26","postgresql","db.t4g.micro"
"beta-db-dev-primary","aurora-postgresql","db.t3.medium",false,
"beta-db-prod-primary","aurora-postgresql","db.r5.xlarge",false,
"beta-db-prod-replica","aurora-postgresql","db.r5.xlarge",false,
"beta-db-staging-primary","aurora-postgresql","db.t3.medium",false,
"charlie-db-dev","postgres","db.t3.micro",false,"charlie-db-dev-8dec2021","active","2021-12-08","postgresql","db.t3.micro"
"charlie-db-prod-dms","postgres","db.m4.4xlarge",true,
"charlie-db-prod-dms-replica","postgres","db.m4.4xlarge",false,
"charlie-db-staging","postgres","db.t2.large",false,
"charlie-db-staging-loadtest","postgres","db.m4.4xlarge",false,
"charlie-kong-db-dev","postgres","db.t3.micro",false,
"charlie-kong-db-prod","postgres","db.m4.large",true,
"charlie-kong-db-staging","postgres","db.t2.small",false,
"delta-db-prod","mysql","db.t3.small",true,
"delta-db-recon-prod","mysql","db.t3.micro",false,
"delta-db-staging","mysql","db.t3.micro",false,
In my attempt, I had asked help using an awk script which was given as
BEGIN{ FS=OFS="," }
NR==FNR{
if (NR==1) {next}
id=$5;
sub(/-[^-]+$/,"",id);
a[id]=$5 OFS $6 OFS $7 OFS $8 OFS $9; next
}
{
if (FNR==1) {print; next}
print $1,$2,$3,$4,a[$1]
}
However, when I run it I get erroneous results. Wrong output:
"DB Instance Identifier","Engine","Instance Type","MultiAZ","Reservation ID","State","Start Time","Reserved Engine","Reserved DB Instance Class"
"alpha-db-dev","postgres","db.t4g.micro",false,
"alpha-db-prod","postgres","db.t4g.small",true,
"alpha-db-staging","postgres","db.t4g.micro",false,
"beta-db-dev-primary","aurora-postgresql","db.t3.medium",false,
"beta-db-prod-primary","aurora-postgresql","db.r5.xlarge",false,
"beta-db-prod-replica","aurora-postgresql","db.r5.xlarge",false,
"beta-db-staging-primary","aurora-postgresql","db.t3.medium",false,
"charlie-db-dev","postgres","db.t3.micro",false,
"charlie-db-prod-dms","postgres","db.m4.4xlarge",true,
"charlie-db-prod-dms-replica","postgres","db.m4.4xlarge",false,
"charlie-db-staging","postgres","db.t2.large",false,
"charlie-db-staging-loadtest","postgres","db.m4.4xlarge",false,
"charlie-kong-db-dev","postgres","db.t3.micro",false,
"charlie-kong-db-prod","postgres","db.m4.large",true,
"charlie-kong-db-staging","postgres","db.t2.small",false,
"delta-db-prod","mysql","db.t3.small",true,
"delta-db-recon-prod","mysql","db.t3.micro",false,
"delta-db-staging","mysql","db.t3.micro",false,
Wherein the values from the 5th column up to the 9th is cut off. (Except for the header) and I'm guessing this is in relation to how boolean values are being present in the csv file on the 4th column (false/true) where the " is not present. I can make it work by pre-formatting the data and using sed to remove all " in the csv file however it seems that this is not something I would want to do to conform with the data format I'm working with.
My question is, how do I edit the awk script to make it work with the input that I have?
