使用 awk 對齊 csv 文件中正確行中的值

這是來自這裡的一個問題Aligning values in csv file

問題是我想根據它們與第一列的匹配方式來對齊第 5 列的值。

輸入：

"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,

所需的輸出將是：

"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,

Ed Morton 之前在上一個問題中使用 awk 腳本回答了這個問題

BEGIN { FS=OFS="," }
NR == FNR {
   id = $2
   sub(/-[^-]+$/,"",id)
   vals[id] = $2 OFS $3
   next
}
{ print (FNR>1 ? $1 OFS vals[$1] : $0) }

但是，當我嘗試理解和修改腳本中的值以匹配我正在處理的 csv 文件中的值時，我得到一堆,或得到錯誤的值，因為最初上面的 awk 腳本比較第 1 列和2. 但這次我想比較第 1 列和第 5 列。然後將整行（第 5-9 列）與第 1 列對齊。我該怎麼做？我還在學習awk。

編輯：更改輸入數據的值以防止在我使用逗號分隔值時出現混淆。我認為當我在 excel 中打開文件並從那裡粘貼而不是使用程式碼編輯器進行粘貼時，我粘貼不正確。對不起，混亂！

您現在使用的文件使用“，”作為欄位分隔符，並且許多欄位都用雙引號括起來。你可以像這樣：

$ awk '  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] 
        }
     ' infile infile

對於給定的輸入，將列印以下內容：

"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,

引用自：https://unix.stackexchange.com/questions/716268