How can i replace the column header and values in a csv file using bash script commands?

Question

Hi I am having a csv file . I have a column named internal and the values under this column are either True or False. I would like to manipulate the csv file using bash script commands. In short I want to rename the column header from 'internal' to 'accounttype' and the column values 'True' should be mapped to 'INTERNAL' and 'False' should be mapped to 'EXTERNAL'.

so following is my csv file named accounts.csv

id,addressprofile,administrator,budget,internal,contactnumber
e15965cf-ffc1-40ae-94c4-b450ab190222,5320a292-4f91-11e8-897d-d50d156ec2f2,Ulisses,150,True,089988737
e59651cf-ffc1-40ae-94c4-b450ab190222,5320a292-4f91-11e8-897d-d50d156ec2f2,petr,100,False,089933737

expected file after changes

id,addressprofile,administrator,budget,accounttype,contactnumber
e15965cf-ffc1-40ae-94c4-b450ab190222,5320a292-4f91-11e8-897d-d50d156ec2f2,Ulisses,150,INTERNAL,089988737
e59651cf-ffc1-40ae-94c4-b450ab190222,5320a292-4f91-11e8-897d-d50d156ec2f2,petr,100,EXTERNAL,089933737

Any idea how can i achieve this . I am happy to use awk or sed command or any other command as well .

this is the original csv file i am trying

id,address1,address2,address3,address4,addressprofile,administrator,averageclickthroughrate,budget,contactnumber,contractid,country,createdby,createdon,currency,customercontactnumber,customerid,defaultlanguage,editmessageprefix,features,internal,inventories,lastupdated,lastupdatedby,logo,messageprefix,name,status,testmessagecontactlist,testmessagelimit,usedefaultclickthroughrate,zipcode
e15965cf-ffc1-40ae-94c4-b450ab190222,,,,,5320a292-4f91-11e8-897d-d50d156ec2f2,ab4e0287-6973-4eec-bd03-cf3669c535d0,40,True,01222222,AB9912345,,ab4e0287-6973-4eec-bd03-cf3669c535d0,2018-05-01 09:11:11.000+0000,f741ac53-b110-46c3-a19d-2bdbdeda8adb,0830456ext123,TestAccount001,b04265e6-c114-470c-8bb0-d10879655ec9,True,"[2976bc5f-70f8-44e8-b738-909616eeb9b2, 443712ee-6b4a-4842-8b20-bf3090748427, 63d04627-1bd9-4652-b48e-dbe1b3e99088]",True,"[7828a316-6212-46e8-8605-e917876abd69, 9352ffc6-0448-4b34-aaad-f19ec5bb5e01, 455822e0-d0ad-46d8-aca1-66c82ac37553, be25104b-90d1-4076-bb4b-44c756d06e55, bdf7fad0-b8cd-4a9a-9c9d-48261fd5e7c7, be25104b-90d1-4076-bb4b-44c756d06d20, 9352ffc6-0448-4b34-aaad-f19ec5bb5e23]",2019-08-22 10:34:35.184+0000,3363a3ad-f52a-4a8b-bc52-7a069bab31d9,,FREETXT,Test Account,ACTIVE,106edc44-bcab-42b6-a75d-c76c32a5174b,10,True,
49eb431c-0b52-43e6-a27c-39374c6df92e,"146a Lower Baggot Street,",Dublin 2,Dublin 2,,5320a292-4f91-11e8-897d-d50d156ec2f2,2770830e-c55c-484a-84d2-cb9d09115a67,40,True,,CTUAUAU,7aae0106-f549-476e-9ec1-de447f4edfde,ab4e0287-6973-4eec-bd03-cf3669c535d0,2019-09-27 09:55:36.088+0000,,0830456811 ext 1552,CX262662,,False,[],False,[9352ffc6-0448-4b34-aaad-f19ec5bb5e23],2019-11-12 15:27:39.535+0000,ab4e0287-6973-4eec-bd03-cf3669c535d0,,FREETXT,Tagon XL Account,ACTIVE,,,True,
e15965cf-ffc1-40ae-94c4-b450ab190233,line 1,line 2,line 3,line 4,5320a292-4f91-11e8-897d-d50d156ec2f2,ab4e0287-6973-4eec-bd03-cf3669c535d0,,False,01222222,,7aae0106-f549-476e-9ec1-de447f4edfde,ab4e0287-6973-4eec-bd03-cf3669c535d0,2018-05-01 08:11:11.000+0000,,,,,False,,False,"[be25104b-90d1-4076-bb4b-44c756d06e55, bdf7fad0-b8cd-4a9a-9c9d-48261fd5e7c7, be25104b-90d1-4076-bb4b-44c756d06d20, 9352ffc6-0448-4b34-aaad-f19ec5bb5e23]",2018-05-01 08:11:11.000+0000,ab4e0287-6973-4eec-bd03-cf3669c535d0,,,Test Account 2,ACTIVE,,10,True,
82abc301-67dd-4bf1-9037-46ac50597f00,,,,,5320a292-4f91-11e8-897d-d50d156ec2f2,621620ad-131f-40e6-ab03-3b0c28a7ec39,,False,,1234567890,,ab4e0287-6973-4eec-bd03-cf3669c535d0,2019-07-17 11:24:23.572+0000,,016330070,47899990,,False,[443712ee-6b4a-4842-8b20-bf3090748427],True,"[bdf7fad0-b8cd-4a9a-9c9d-48261fd5e7c7, be25104b-90d1-4076-bb4b-44c756d06d20]",2019-07-17 11:24:23.792+0000,ab4e0287-6973-4eec-bd03-cf3669c535d0,,,Gail,ACTIVE,,,True,
270d13e6-2f4f-4d51-99d5-92ffba5f0cb6,College Green,Dublin 2,Dublin,Leinster,5320a292-4f91-11e8-897d-d50d156ec2f2,7626bb7e-6722-48f9-a182-c1c2886025a9,40,True,,WXFFGG123,7aae0106-f549-476e-9ec1-de447f4edfde,ab4e0287-6973-4eec-bd03-cf3669c535d0,2019-03-25 18:21:00.432+0000,,0830456811,StarbucksDublin001,b04265e6-c114-470c-8bb0-d10879655ec9,True,"[2976bc5f-70f8-44e8-b738-909616eeb9b2, 443712ee-6b4a-4842-8b20-bf3090748427, 63d04627-1bd9-4652-b48e-dbe1b3e99088]",False,"[bdf7fad0-b8cd-4a9a-9c9d-48261fd5e7c7, be25104b-90d1-4076-bb4b-44c756d06d20, 9352ffc6-0448-4b34-aaad-f19ec5bb5e23]",2019-11-26 17:22:59.919+0000,ab4e0287-6973-4eec-bd03-cf3669c535d0,72fd8d43-0233-4f7e-9066-b7b78c835e71,RM0,Starbucks,ACTIVE,4069d51e-2421-44da-a3cc-9ebbf1e52bc5,10,True,0000 DB
68173861-429e-416e-9576-cd6c37c31765,,,,,5320a292-4f91-11e8-897d-d50d156ec2f2,38c64d45-0eca-44da-8c6e-a5fdf3f540dc,40,True,,,,ab4e0287-6973-4eec-bd03-cf3669c535d0,2019-03-08 12:03:26.760+0000,,,,b04265e6-c114-470c-8bb0-d10879655ec9,False,[],True,"[bdf7fad0-b8cd-4a9a-9c9d-48261fd5e7c7, be25104b-90d1-4076-bb4b-44c756d06d20]",2019-07-30 11:22:51.330+0000,ab4e0287-6973-4eec-bd03-cf3669c535d0,,,Munchies,ACTIVE,f65a472d-b4d4-4050-8711-a93e6376c8f1,10,True,
d2d63c5d-a13c-486d-9ae4-4d211bd86530,,,,,5320a292-4f91-11e8-897d-d50d156ec2f2,92209e3f-31ba-4654-8f07-f7b4d5bb7219,40,True,,,,ab4e0287-6973-4eec-bd03-cf3669c535d0,2019-07-17 08:20:38.737+0000,,,,,False,[63d04627-1bd9-4652-b48e-dbe1b3e99088],True,"[bdf7fad0-b8cd-4a9a-9c9d-48261fd5e7c7, be25104b-90d1-4076-bb4b-44c756d06d20]",2019-12-05 13:31:08.237+0000,ab4e0287-6973-4eec-bd03-cf3669c535d0,,RM0,Derek McElhinney,ACTIVE,,0,True,

@ed norton after i run the script with FPAT all the data fields shifted to the right. here is the output of your script .

id,address1,address2,address3,address4,addressprofile,administrator,averageclickthroughrate,budget,contactnumber,contractid,country,createdby,createdon,currency,customercontactnumber,customerid,defaultlanguage,editmessageprefix,features,accounttype,inventories,lastupdated,lastupdatedby,logo,messageprefix,name,status,testmessagecontactlist,testmessagelimit,usedefaultclickthroughrate,zipcode
e15965cf-ffc1-40ae-94c4-b450ab190222,,,,,5320a292-4f91-11e8-897d-d50d156ec2f2,ab4e0287-6973-4eec-bd03-cf3669c535d0,40,True,01222222,AB9912345,,ab4e0287-6973-4eec-bd03-cf3669c535d0,2018-05-01 09:11:11.000+0000,f741ac53-b110-46c3-a19d-2bdbdeda8adb,0830456ext123,TestAccount001,b04265e6-c114-470c-8bb0-d10879655ec9,True,"[2976bc5f-70f8-44e8-b738-909616eeb9b2, 443712ee-6b4a-4842-8b20-bf3090748427, 63d04627-1bd9-4652-b48e-dbe1b3e99088]",INTERNAL,,"[7828a316-6212-46e8-8605-e917876abd69, 9352ffc6-0448-4b34-aaad-f19ec5bb5e01, 455822e0-d0ad-46d8-aca1-66c82ac37553, be25104b-90d1-4076-bb4b-44c756d06e55, bdf7fad0-b8cd-4a9a-9c9d-48261fd5e7c7, be25104b-90d1-4076-bb4b-44c756d06d20, 9352ffc6-0448-4b34-aaad-f19ec5bb5e23]",2019-08-22 10:34:35.184+0000,3363a3ad-f52a-4a8b-bc52-7a069bab31d9,,FREETXT,Test Account,ACTIVE,106edc44-bcab-42b6-a75d-c76c32a5174b,10,True,
49eb431c-0b52-43e6-a27c-39374c6df92e,"146a Lower Baggot Street,",Dublin 2,Dublin 2,,5320a292-4f91-11e8-897d-d50d156ec2f2,2770830e-c55c-484a-84d2-cb9d09115a67,40,True,,CTUAUAU,7aae0106-f549-476e-9ec1-de447f4edfde,ab4e0287-6973-4eec-bd03-cf3669c535d0,2019-09-27 09:55:36.088+0000,,0830456811 ext 1552,CX262662,,False,[],EXTERNAL,,[9352ffc6-0448-4b34-aaad-f19ec5bb5e23],2019-11-12 15:27:39.535+0000,ab4e0287-6973-4eec-bd03-cf3669c535d0,,FREETXT,Tagon XL Account,ACTIVE,,,True,
e15965cf-ffc1-40ae-94c4-b450ab190233,line 1,line 2,line 3,line 4,5320a292-4f91-11e8-897d-d50d156ec2f2,ab4e0287-6973-4eec-bd03-cf3669c535d0,,False,01222222,,7aae0106-f549-476e-9ec1-de447f4edfde,ab4e0287-6973-4eec-bd03-cf3669c535d0,2018-05-01 08:11:11.000+0000,,,,,False,,EXTERNAL,,"[be25104b-90d1-4076-bb4b-44c756d06e55, bdf7fad0-b8cd-4a9a-9c9d-48261fd5e7c7, be25104b-90d1-4076-bb4b-44c756d06d20, 9352ffc6-0448-4b34-aaad-f19ec5bb5e23]",2018-05-01 08:11:11.000+0000,ab4e0287-6973-4eec-bd03-cf3669c535d0,,,Test Account 2,ACTIVE,,10,True,
82abc301-67dd-4bf1-9037-46ac50597f00,,,,,5320a292-4f91-11e8-897d-d50d156ec2f2,621620ad-131f-40e6-ab03-3b0c28a7ec39,,False,,1234567890,,ab4e0287-6973-4eec-bd03-cf3669c535d0,2019-07-17 11:24:23.572+0000,,016330070,47899990,,False,[443712ee-6b4a-4842-8b20-bf3090748427],INTERNAL,,"[bdf7fad0-b8cd-4a9a-9c9d-48261fd5e7c7, be25104b-90d1-4076-bb4b-44c756d06d20]",2019-07-17 11:24:23.792+0000,ab4e0287-6973-4eec-bd03-cf3669c535d0,,,Gail,ACTIVE,,,True,
270d13e6-2f4f-4d51-99d5-92ffba5f0cb6,College Green,Dublin 2,Dublin,Leinster,5320a292-4f91-11e8-897d-d50d156ec2f2,7626bb7e-6722-48f9-a182-c1c2886025a9,40,True,,WXFFGG123,7aae0106-f549-476e-9ec1-de447f4edfde,ab4e0287-6973-4eec-bd03-cf3669c535d0,2019-03-25 18:21:00.432+0000,,0830456811,StarbucksDublin001,b04265e6-c114-470c-8bb0-d10879655ec9,True,"[2976bc5f-70f8-44e8-b738-909616eeb9b2, 443712ee-6b4a-4842-8b20-bf3090748427, 63d04627-1bd9-4652-b48e-dbe1b3e99088]",EXTERNAL,,"[bdf7fad0-b8cd-4a9a-9c9d-48261fd5e7c7, be25104b-90d1-4076-bb4b-44c756d06d20, 9352ffc6-0448-4b34-aaad-f19ec5bb5e23]",2019-11-26 17:22:59.919+0000,ab4e0287-6973-4eec-bd03-cf3669c535d0,72fd8d43-0233-4f7e-9066-b7b78c835e71,RM0,Starbucks,ACTIVE,4069d51e-2421-44da-a3cc-9ebbf1e52bc5,10,True,0000 DB
68173861-429e-416e-9576-cd6c37c31765,,,,,5320a292-4f91-11e8-897d-d50d156ec2f2,38c64d45-0eca-44da-8c6e-a5fdf3f540dc,40,True,,,,ab4e0287-6973-4eec-bd03-cf3669c535d0,2019-03-08 12:03:26.760+0000,,,,b04265e6-c114-470c-8bb0-d10879655ec9,False,[],INTERNAL,,"[bdf7fad0-b8cd-4a9a-9c9d-48261fd5e7c7, be25104b-90d1-4076-bb4b-44c756d06d20]",2019-07-30 11:22:51.330+0000,ab4e0287-6973-4eec-bd03-cf3669c535d0,,,Munchies,ACTIVE,f65a472d-b4d4-4050-8711-a93e6376c8f1,10,True,
d2d63c5d-a13c-486d-9ae4-4d211bd86530,,,,,5320a292-4f91-11e8-897d-d50d156ec2f2,92209e3f-31ba-4654-8f07-f7b4d5bb7219,40,True,,,,ab4e0287-6973-4eec-bd03-cf3669c535d0,2019-07-17 08:20:38.737+0000,,,,,False,[63d04627-1bd9-4652-b48e-dbe1b3e99088],INTERNAL,,"[bdf7fad0-b8cd-4a9a-9c9d-48261fd5e7c7, be25104b-90d1-4076-bb4b-44c756d06d20]",2019-12-05 13:31:08.237+0000,ab4e0287-6973-4eec-bd03-cf3669c535d0,,RM0,Derek McElhinney,ACTIVE,,0,True,

appreciate any help

score 1 · Answer 1 · answered Dec 06 '19 at 14:14

This Python 3 script will do as well :

#!/usr/local/bin/python3

import csv
csvr = csv.reader(open('rename_columns.csv'), delimiter = ",")

for row in csvr:
    row[4] = 'accounttype' if row[4] =='internal' else row[4]
    row[4] = 'INTERNAL' if row[4] =='True' else row[4]
    row[4] = 'EXTERNAL' if row[4] =='False' else row[4]
    print("{},{},{},{},{},{}".format(row[0],row[1],row[2],row[3],row[4],row[5]))

Regards!

Ed Morton · Accepted Answer · 2019-12-06T15:28:16.543

The general approach to manipulate any/all fields in a simple CSV (i.e. no embedded commas, quotes, or newlines in fields) by the column header names as opposed to relying on the order of the columns:

$ cat tst.awk
BEGIN { FS=OFS="," }
{
    if ( NR==1 ) {
        for (i=1; i<=NF; i++) {
            if ($i == "internal") {
                $i = "accounttype"
            }
            f[$i] = i
        }
    }
    else {
        $(f["accounttype"]) = ( $(f["accounttype"]) == "True" ? "INTERNAL" : "EXTERNAL" )
    }
    print
}

$ awk -f tst.awk accounts.csv
id,addressprofile,administrator,budget,accounttype,contactnumber
e15965cf-ffc1-40ae-94c4-b450ab190222,5320a292-4f91-11e8-897d-d50d156ec2f2,Ulisses,150,INTERNAL,089988737
e59651cf-ffc1-40ae-94c4-b450ab190222,5320a292-4f91-11e8-897d-d50d156ec2f2,petr,100,EXTERNAL,089933737

For handling more general/complicated CSVs, see What's the most robust way to efficiently parse CSV using awk?.

If you don't mind using a hard-coded field number ($5) then one way to do this would be:

$ cat tst.awk
BEGIN {
    FS=OFS=","
    map[1,"internal"] = "accounttype"
    map[0,"True"]     = "INTERNAL"
    map[0,"False"]    = "EXTERNAL"
}
{
    $5 = map[NR==1,$5]
    print
}

$ awk -f tst.awk accounts.csv
id,addressprofile,administrator,budget,accounttype,contactnumber
e15965cf-ffc1-40ae-94c4-b450ab190222,5320a292-4f91-11e8-897d-d50d156ec2f2,Ulisses,150,INTERNAL,089988737
e59651cf-ffc1-40ae-94c4-b450ab190222,5320a292-4f91-11e8-897d-d50d156ec2f2,petr,100,EXTERNAL,089933737

Given your updated sample input has commas embedded in quoted fields then using GNU awk for FPAT instead of FS:

$ cat tst.awk
BEGIN {
    OFS = ","
    FPAT = "([^" OFS "]*)|(\"[^\"]+\")"
}
{
    if ( NR==1 ) {
        for (i=1; i<=NF; i++) {
            if ($i == "internal") {
                $i = "accounttype"
            }
            f[$i] = i
        }
    }
    else {
        $(f["accounttype"]) = ( $(f["accounttype"]) == "True" ? "INTERNAL" : "EXTERNAL" )
    }
    print
}

$ awk -f tst.awk original.csv
id,address1,address2,address3,address4,addressprofile,administrator,averageclickthroughrate,budget,contactnumber,contractid,country,createdby,createdon,currency,customercontactnumber,customerid,defaultlanguage,editmessageprefix,features,accounttype,inventories,lastupdated,lastupdatedby,logo,messageprefix,name,status,testmessagecontactlist,testmessagelimit,usedefaultclickthroughrate,zipcode
e15965cf-ffc1-40ae-94c4-b450ab190222,,,,,5320a292-4f91-11e8-897d-d50d156ec2f2,ab4e0287-6973-4eec-bd03-cf3669c535d0,40,True,01222222,AB9912345,,ab4e0287-6973-4eec-bd03-cf3669c535d0,2018-05-01 09:11:11.000+0000,f741ac53-b110-46c3-a19d-2bdbdeda8adb,0830456ext123,TestAccount001,b04265e6-c114-470c-8bb0-d10879655ec9,True,"[2976bc5f-70f8-44e8-b738-909616eeb9b2, 443712ee-6b4a-4842-8b20-bf3090748427, 63d04627-1bd9-4652-b48e-dbe1b3e99088]",INTERNAL,"[7828a316-6212-46e8-8605-e917876abd69, 9352ffc6-0448-4b34-aaad-f19ec5bb5e01, 455822e0-d0ad-46d8-aca1-66c82ac37553, be25104b-90d1-4076-bb4b-44c756d06e55, bdf7fad0-b8cd-4a9a-9c9d-48261fd5e7c7, be25104b-90d1-4076-bb4b-44c756d06d20, 9352ffc6-0448-4b34-aaad-f19ec5bb5e23]",2019-08-22 10:34:35.184+0000,3363a3ad-f52a-4a8b-bc52-7a069bab31d9,,FREETXT,Test Account,ACTIVE,106edc44-bcab-42b6-a75d-c76c32a5174b,10,True,
49eb431c-0b52-43e6-a27c-39374c6df92e,"146a Lower Baggot Street,",Dublin 2,Dublin 2,,5320a292-4f91-11e8-897d-d50d156ec2f2,2770830e-c55c-484a-84d2-cb9d09115a67,40,True,,CTUAUAU,7aae0106-f549-476e-9ec1-de447f4edfde,ab4e0287-6973-4eec-bd03-cf3669c535d0,2019-09-27 09:55:36.088+0000,,0830456811 ext 1552,CX262662,,False,[],EXTERNAL,[9352ffc6-0448-4b34-aaad-f19ec5bb5e23],2019-11-12 15:27:39.535+0000,ab4e0287-6973-4eec-bd03-cf3669c535d0,,FREETXT,Tagon XL Account,ACTIVE,,,True,
e15965cf-ffc1-40ae-94c4-b450ab190233,line 1,line 2,line 3,line 4,5320a292-4f91-11e8-897d-d50d156ec2f2,ab4e0287-6973-4eec-bd03-cf3669c535d0,,False,01222222,,7aae0106-f549-476e-9ec1-de447f4edfde,ab4e0287-6973-4eec-bd03-cf3669c535d0,2018-05-01 08:11:11.000+0000,,,,,False,,EXTERNAL,"[be25104b-90d1-4076-bb4b-44c756d06e55, bdf7fad0-b8cd-4a9a-9c9d-48261fd5e7c7, be25104b-90d1-4076-bb4b-44c756d06d20, 9352ffc6-0448-4b34-aaad-f19ec5bb5e23]",2018-05-01 08:11:11.000+0000,ab4e0287-6973-4eec-bd03-cf3669c535d0,,,Test Account 2,ACTIVE,,10,True,
82abc301-67dd-4bf1-9037-46ac50597f00,,,,,5320a292-4f91-11e8-897d-d50d156ec2f2,621620ad-131f-40e6-ab03-3b0c28a7ec39,,False,,1234567890,,ab4e0287-6973-4eec-bd03-cf3669c535d0,2019-07-17 11:24:23.572+0000,,016330070,47899990,,False,[443712ee-6b4a-4842-8b20-bf3090748427],INTERNAL,"[bdf7fad0-b8cd-4a9a-9c9d-48261fd5e7c7, be25104b-90d1-4076-bb4b-44c756d06d20]",2019-07-17 11:24:23.792+0000,ab4e0287-6973-4eec-bd03-cf3669c535d0,,,Gail,ACTIVE,,,True,
270d13e6-2f4f-4d51-99d5-92ffba5f0cb6,College Green,Dublin 2,Dublin,Leinster,5320a292-4f91-11e8-897d-d50d156ec2f2,7626bb7e-6722-48f9-a182-c1c2886025a9,40,True,,WXFFGG123,7aae0106-f549-476e-9ec1-de447f4edfde,ab4e0287-6973-4eec-bd03-cf3669c535d0,2019-03-25 18:21:00.432+0000,,0830456811,StarbucksDublin001,b04265e6-c114-470c-8bb0-d10879655ec9,True,"[2976bc5f-70f8-44e8-b738-909616eeb9b2, 443712ee-6b4a-4842-8b20-bf3090748427, 63d04627-1bd9-4652-b48e-dbe1b3e99088]",EXTERNAL,"[bdf7fad0-b8cd-4a9a-9c9d-48261fd5e7c7, be25104b-90d1-4076-bb4b-44c756d06d20, 9352ffc6-0448-4b34-aaad-f19ec5bb5e23]",2019-11-26 17:22:59.919+0000,ab4e0287-6973-4eec-bd03-cf3669c535d0,72fd8d43-0233-4f7e-9066-b7b78c835e71,RM0,Starbucks,ACTIVE,4069d51e-2421-44da-a3cc-9ebbf1e52bc5,10,True,0000 DB
68173861-429e-416e-9576-cd6c37c31765,,,,,5320a292-4f91-11e8-897d-d50d156ec2f2,38c64d45-0eca-44da-8c6e-a5fdf3f540dc,40,True,,,,ab4e0287-6973-4eec-bd03-cf3669c535d0,2019-03-08 12:03:26.760+0000,,,,b04265e6-c114-470c-8bb0-d10879655ec9,False,[],INTERNAL,"[bdf7fad0-b8cd-4a9a-9c9d-48261fd5e7c7, be25104b-90d1-4076-bb4b-44c756d06d20]",2019-07-30 11:22:51.330+0000,ab4e0287-6973-4eec-bd03-cf3669c535d0,,,Munchies,ACTIVE,f65a472d-b4d4-4050-8711-a93e6376c8f1,10,True,
d2d63c5d-a13c-486d-9ae4-4d211bd86530,,,,,5320a292-4f91-11e8-897d-d50d156ec2f2,92209e3f-31ba-4654-8f07-f7b4d5bb7219,40,True,,,,ab4e0287-6973-4eec-bd03-cf3669c535d0,2019-07-17 08:20:38.737+0000,,,,,False,[63d04627-1bd9-4652-b48e-dbe1b3e99088],INTERNAL,"[bdf7fad0-b8cd-4a9a-9c9d-48261fd5e7c7, be25104b-90d1-4076-bb4b-44c756d06d20]",2019-12-05 13:31:08.237+0000,ab4e0287-6973-4eec-bd03-cf3669c535d0,,RM0,Derek McElhinney,ACTIVE,,0,True,

does the generic solution works ? because i tried with a csv file with one header and some 7 rows. header field was changed successfully from internal to account type how ever , only some of the True or False were only changed to INTERNAL or EXTERNAL. I have uploaded the file here https://gofile.io/?c=9AjJtn — KnowledgeSeeker001, Dec 06 '19 at 14:51
i have posted the content of my original csv. thank you ed morton — KnowledgeSeeker001, Dec 06 '19 at 14:58
the first csv i created manually, thinking it should work . but later i did an import from the cassandra and it is slightly different. I think i have properly copy pasted your script. actually yours is the generic solution where you dont have to hard code the postion — KnowledgeSeeker001, Dec 06 '19 at 15:03
oh ok ... so is there any way to modify your script to accomodate commas ,quots or new lines — KnowledgeSeeker001, Dec 06 '19 at 15:08
I updated my answer to show the first script I posted using FPAT instead of FS and running against your new input. — Ed Morton, Dec 06 '19 at 15:22
so after running your script , under inventory column every this is blank , basiically all rows after the accounttype got shifted to the right .I have attached a couple of images . :( — KnowledgeSeeker001, Dec 06 '19 at 15:49
Again, don't post images (or links) as we can't test against an image, post text. And come up with a [mcve] that demonstrates the problem (e.g. 5 or 6 lines of 5 or 6 fields each), don't just throw up long lines of text you just happened to have lying around. — Ed Morton, Dec 06 '19 at 15:50
wrt `all the data fields shifted to the right` - no, they didn't, the data field `id` is still the first column for example. Again - come up with a **minimal** example that demonstrates the problem and post **that** in your question. Chances are by doing that exercise you'll figure out what the issue is for yourself. — Ed Morton, Dec 06 '19 at 15:55
actually all the columns coming after accounttype were shifted to the right .. if you open the out put i posted with a csv editor you can observice it ... that is why i posted the image — KnowledgeSeeker001, Dec 06 '19 at 16:01
If you create a [mcve] then I don't need to do that, I'll be able to see it easily in the text. In any case, note that the output you get and the output I get are different (copy both to files and run `diff`) so either you didn't copy/paste my script correctly or you didn't run the script on the same input file as I did, or there's a bug in your version of gawk. There have been a couple of FPAT-related bugs in gawk over the years so its not impossible - if thats it they can all be worked around by adding the line `oFPAT=FPAT; FPAT=""; FPAT=oFPAT` immediately above `if ( NR==1 ) {` in my script — Ed Morton, Dec 06 '19 at 16:01

Aaron · Answer 3 · 2019-12-06T16:30:36.440

0

Using awk :

awk -F, 'BEGIN { OFS="," }
         {
            if (NR == 1) {
                $5 = "accounttype"
            } else {
                $5 = ($5 == "True") ? "INTERNAL" : "EXTERNAL"
            }
            print
         }'

-F, sets the Input Field Separator to ,, so that we parse comma-separated fields
BEGIN { OFS="," } set the Output Field Separator to ,, so that we keep a CSV-formatted output
NR represents the current line number, so the then block only applies to the header while the else block will apply to the data
$5 represents the fifth field which is your "internal" column
We modify that field to the desired value. For the data, it is computed using a ternary that yields "INTERNAL" if the original value is "True", and "EXTERNAL" otherwise

edited Dec 06 '19 at 16:30

answered Dec 06 '19 at 14:12

Aaron

24,009
2
33
57

@KnowledgeSeeker001 I've added an explanation – Aaron Dec 06 '19 at 14:21
1

@EdMorton thanks, I've added an improved solution where I factorize this, I can't be bothered to update the explanation right now but I probably will replace my solution completely by this improved solution when I'll find time – Aaron Dec 06 '19 at 14:26
1

Honestly it's so clear and simple that IMHO a reader who doesn't understand it immediately would get far more out of a quick glance at the man pages to figure it out for themselves than if you added an explanation anyway. – Ed Morton Dec 06 '19 at 14:28

score 0 · Answer 4 · answered Dec 06 '19 at 14:17

With this bash command

cat csvfile |  sed "s/,internal,/,accounttype,/g" | sed "s/True/,INTERNAL,/" | sed "s/False/,EXTERNAL,/"

you'll achieve what you need. The only limitation is that none of your users should have "True" or "False" or "internal" as username

How can i replace the column header and values in a csv file using bash script commands?

4 Answers4