Replace string after semicolon

user_14301772 0
Viewed 142

I have a file filtered_content with the following content:

ip-172-31-42324-162.sa-east-1.compute.internal;2021-06-26T05:07:30Z
ip-172-31-42324-162.sa-east-1.compute.internal;2021-10-12T05:07:30Z
ip-172-31-4234-163.sa-east-1.compute.internal;2021-03-02T05:07:30Z
ip-172-31-4234-163.sa-east-1.compute.internal;2021-05-26T05:07:30Z

and another file converted_data with content:

1624684050
1634015250
1614661650
1622005650

I want to replace strings after semicolon in filtered_content with content of converted_data like this:

ip-172-31-42324-162.sa-east-1.compute.internal;1624684050
ip-172-31-42324-162.sa-east-1.compute.internal;1634015250
ip-172-31-4234-163.sa-east-1.compute.internal;1614661650
ip-172-31-4234-163.sa-east-1.compute.internal;1622005650

I tried something like this, but doesn't work.

data=$(cat /tmp/converted_data)
cat /tmp/filtered_content | sed 's/;.*//' | sed 's/.${data};//'
sed awk bash
6 Answers

A quick way using paste and cut and Process Substitution.

paste -d';' <(cut -d';' -f1 filtered_content.txt) converted_content.txt

As per @oguzismail's comment, a Process Substitution is not needed.

cut -d ';' -f1 filtered_content.txt | paste -d';' - converted_content.txt
user_44522650

You may consider this awk solution:

awk 'BEGIN{FS=OFS=";"} NR==FNR {arr[FNR]=$1; next} {$2 = arr[FNR]} 1' converted_data filtered_content

ip-172-31-42324-162.sa-east-1.compute.internal;1624684050
ip-172-31-42324-162.sa-east-1.compute.internal;1634015250
ip-172-31-4234-163.sa-east-1.compute.internal;1614661650
ip-172-31-4234-163.sa-east-1.compute.internal;1622005650

A more readable form:

awk '
BEGIN { FS=OFS=";" }
NR == FNR {
   arr[FNR] = $1
   next
}
{
   $2 = arr[FNR]
} 1' converted_data filtered_content
user_5482250

You don't need converted_data at all, just use GNU awk (for mktime() and gensub()) on filtered_content alone:

$ awk 'BEGIN{FS=OFS=";"} {$NF=mktime(gensub(/[-T:]/," ","g",$NF),1)} 1' filtered_content
ip-172-31-42324-162.sa-east-1.compute.internal;1624684050
ip-172-31-42324-162.sa-east-1.compute.internal;1634015250
ip-172-31-4234-163.sa-east-1.compute.internal;1614661650
ip-172-31-4234-163.sa-east-1.compute.internal;1622005650
user_17450010

Another awk, using getline to read from converted_data:

$ awk 'BEGIN{FS=OFS=";"} getline $NF <"converted_data"' filtered_content

Output:

ip-172-31-42324-162.sa-east-1.compute.internal;1624684050
ip-172-31-42324-162.sa-east-1.compute.internal;1634015250
ip-172-31-4234-163.sa-east-1.compute.internal;1614661650
ip-172-31-4234-163.sa-east-1.compute.internal;1622005650

If converted_data has less lines than filtered_content, getline returns 0 and the line from filtered_content won't get printed. If converted_data does not exist, -1 is returned and the line filtered_content is output as-is.

user_41623560

Taking inspiration from Ed sir's answer, using only gsub function of awk along with mktime function, pleas try following.

awk 'BEGIN{FS=OFS=";"} {gsub(/-|T|:|Z$/," ",$NF);$NF=mktime($NF)} 1' Input_file

Explanation: Adding detailed explanation for above.

awk '                        ##Starting awk program from here.
BEGIN{                       ##Starting BEGIN section of this program from here.
  FS=OFS=";"                 ##Setting FS and OFS as semi-colon here.
}
{
  gsub(/-|T|:|Z$/," ",$NF)   ##Globally substituting - T : Z(at last) with space in  last field.
  $NF=mktime($NF)            ##Using mktime function to save values into last field itself, changing it to epoch time basically.
}
1                            ##Printing current line here.
' Input_file                 ##Mentioning Input_file name here.
user_58665800

Like Ed said, there is no need for a temporary file. Here is one way to do it in one go with date, paste and cut:

paste -d ';' <(cut -d';' -f1 infile) <(cut -d';' -f2 infile | date -f - +%s)

Output:

ip-172-31-42324-162.sa-east-1.compute.internal;1624684050
ip-172-31-42324-162.sa-east-1.compute.internal;1634015250
ip-172-31-4234-163.sa-east-1.compute.internal;1614661650
ip-172-31-4234-163.sa-east-1.compute.internal;1622005650
user_13313990
Related