1

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

Improve this question

4 Answers 4

Reset to default
1

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,
Improve this answer
Sign up to request clarification or add additional context in comments.

10 Comments

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 gofile.io/?c=9AjJtn
i have posted the content of my original csv. thank you ed morton
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
oh ok ... so is there any way to modify your script to accomodate commas ,quots or new lines
I updated my answer to show the first script I posted using FPAT instead of FS and running against your new input.
|
1

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!

Improve this answer

Comments

0

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

Improve this answer

Comments

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
Improve this answer

3 Comments

@KnowledgeSeeker001 I've added an explanation
@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
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.