Adding a value next to the last field of a csv file to each record using sed command in unix

Question

I have a trouble in writing a sed command to add a value to each record as new field , next to the last column (sometimes values being null), in a csv file using sed command.

For Example, below is my sample csv data (taking last few fields and few records only). I need to add a new value under the header after 'ShipFromStore' field to every record of the file.

Sample Input (There are fields ahead but copied only few of them ending with :

Allocation%  ValidityFrom     ValidityTo    CODsurcharge    ShipFromStore 
22           1/10/2017        9/30/2019       4.5   
22           1/10/2017        9/30/2019       4.5   
22           1/10/2017        9/30/2019       4.5

Desire Output :

Allocation% ValidityFrom ValidityTo CODsurcharge ShipFromStore NewField
22          1/10/2017    9/30/2019        4.5                    160
22          1/10/2017    9/30/2019        4.5                    160 
22          1/10/2017    9/30/2019        4.5                    160

ShipFromStore values are null for now but in the future we might be receiving data

Tried below commands but unable to achieve it.

sed 'N;s/","/",'160',"/77' Filename
#77 being the position after which the value is to be added
sed 's/$/\,'160'/' Filename
sed '1{s/$/,"Batch_Id"/;b};s/$/,'"$c"'/' Filename

To be honest, we receive an .xlsx file as source and we convert it to .csv using a jar function. Post that, we use the file to modify. Below is the sample data in converted csv file. "Allocation %","Validity From","Validity To","COD surcharge","Ship From Store"$ 22,1/10/17,"09/30/2019",4.5$ Ideally, there should be one more comma after the value 4.5 (which is for COD surcharge) since ShipFromStore is a null value. Not sure why its not being shown. Now i need to append a value such that my output would be 4.5,,160 in the file for every record excluding the header — Abhishek, May 02 '19 at 05:05
Even tried to append the value from the second record as below : sed '2,$s/,160/' Filename but isnt working — Abhishek, May 02 '19 at 05:09
Yes, but for now there is no issue with it as I can see one of the field value as below. It has a comma inside it but has been considered as a single value while being converted from .xlsx to .csv "As a global logistics provider, UPS is already on the ground pioneering innovative delivery methods and developing plans for more intelligent transportation systems. For more information, please kindly refer to Sustainability Chapter in UPS Response" — Abhishek, May 02 '19 at 05:15
@oguzismail Edited the same. Please check once. There are 70 attributes in total and i wont be able to add all of them here. I added the last five attributes that are ending with, in the file — Abhishek, May 02 '19 at 06:10
are you having `,` commas separated entries in your file? If it is an excel file then I would say better to use Python, perl for its parsing rather than using `awk`, `sed` etc. Kindly confirm the same and be clear in your questions. — RavinderSingh13, May 02 '19 at 06:21
@RavinderSingh13 Yes, I do have comma separated entries but as i mentioned earlier, we receive a '.xlsx' file which is converted to '.csv' . I downloaded the converted file and could see no issues with fields having commas inside it being separated. The below comman is working , but the only thing causing issue is the value is being added under the last field 'ShipFromStore' which is holding null values as of now whereas It should be added under a newfield viz., 'batch_id' sed '1{s/$/,"Batch_Id"/;b};s/$/,160/' — Abhishek, May 02 '19 at 06:28
Good that you have shown your efforts being a new member on SO, try to be clear in your samples too, so that everyone is there on same page with regards to yuor question. — RavinderSingh13, May 02 '19 at 06:40
@Abhishek I think the program converting that XLSX to CSV is broken, fix it first — oguz ismail, May 02 '19 at 07:30
If your actual data is an excel file... why not just add the extra column in excel with a VBA macro? — Shawn, May 02 '19 at 13:05
See https://stackoverflow.com/a/38805230/1745001 for how to generate CSVs from XLSX using cygwin and https://stackoverflow.com/a/45420607/1745001 for how to then work with those CSVs. If you still need help after that then post truly representative sample input and expected output formatted exactly as it appears in your CSV with commas, quotes, etc. exactly as-is in your text editor (i.e. don't convert it to some tabular format in your question to try to make it more readable). — Ed Morton, May 02 '19 at 17:20

RavinderSingh13 · Answer 1 · 2019-05-02T07:27:24.720

0

Your Input_file is not same whatever you mentioned in your comments, so I am considering that you have comma separated Input_file and you have , at last of your each line, if this is the case then try following.

awk 'BEGIN{s1="\"";FS=OFS=","} FNR==1{print $0 "NewField";next} {$0=$0 OFS s1 " "s1 OFS "160"} 1' Input_file

In case you want to save output into Input_file itself try(Do it only when you are happy with above command since it is doing changes in Input_file itself):

awk 'BEGIN{s1="\"";FS=OFS=","} FNR==1{print $0 "NewField";next} {$0=$0 OFS s1 " "s1 OFS "160"} 1'Input_file > temp && mv temp  Input_file

edited May 02 '19 at 07:27

answered May 02 '19 at 06:39

RavinderSingh13

130,504
14
57
93

This is not working as the csv file is ending with 4.5 as the last value (for CODSurcharge) "1/10/2017","9/30/2019 ",4.5$ But ideally , the last field is 'ShipFromStore' (which is a null value field) and this is not being considered while the .xlsx is converted to .csv . Might be this is causing the issue to be allowing the value to be added to 'ShipFromStore' instead of a field next to it. – Abhishek May 02 '19 at 06:55
@Abhishek, please tell us how it should look like then? As it is not clear. – RavinderSingh13 May 02 '19 at 07:08
I have mentioned the sample input and desired output in my question. Can you please check that once – Abhishek May 02 '19 at 07:15
1

@Abhishek, so it means `4.5,` should be changed to `4.5,160` right? Honestly you should mention your samples with commas in your post. – RavinderSingh13 May 02 '19 at 07:16
Ok, here it how it should be. Sample Input: 22,"1/10/17","09/30/2019",4.5$ (Ideally there is another field after 4.5 and should not be ending with that). So consider it as 4.5," "$ Now my output should be : 22,"1/10/17","09/30/2019",4.5," ",160 For above few example commands, the value 160 is getting appended inside the field holding null value which should not be the case. it should be added as a new field irrespective of the last field holding null values or data – Abhishek May 02 '19 at 07:21
Values are coming as expected but the header (fieldnames) has last two fields concatenated For Example : 'ShipFromStore' (last fied for input file) got concatenated with 'NewField' i.e., 'Batch_Id' . Please suggest for this. Other than this , the values are coming fine. Hope this works even when there is data for 'ShipFromStore' field. – Abhishek May 02 '19 at 07:44

score 0 · Answer 2 · answered May 02 '19 at 14:18

try this code, you change the separator to ';' if your csv is not using ','

awk -v Separator=',' '
   BEGIN{FS=OFS=Separator}
   {
   if(NR>1) $0 = $0 ( (NF == 3 ) ? OFS OFS : OFS ) OFS "160"
     $0 = $0 OFS "\042ShipFromStore\"" OFS "\042NewField\042"
   print
   }
   ' FileName

score 0 · Answer 3 · answered May 02 '19 at 19:04

another awk

$ awk -F, 'BEGIN {OFS=FS} 
           NR==1 {n=NF; print $0, "NewField"} 
           NR>1  {NF=n; print $0, 160}' file.csv 

Allocation%,ValidityFrom,ValidityTo,CODsurcharge,ShipFromStore,NewField
22,1/10/2017,9/30/2019,4.5,,160
22,1/10/2017,9/30/2019,4.5,,160
22,1/10/2017,9/30/2019,4.5,,160

can be re-written shorter

$ awk -F, 'BEGIN{OFS=FS} !n{n=NF} {NF=n; print $0, (NR==1?"NewField":160)}' file.csv

Adding a value next to the last field of a csv file to each record using sed command in unix

3 Answers3