Copy data from each row in first column into a html hyperlink in the last column using Sed, Awk, etc-CodePudding

What i'm trying to do is copy the first data from each row in the first column and put it into the hyperlink in the last column created

This is an example of a file i get each day. Let's call this file input.csv and i want to transform it into output.csv

P.S. The number of rows varies each day.

INPUT.CSV

number|name|age|gender
B24|mike|22|male
B65|john|45|male
B74|jane|29|female

This is how i want it to look like:

OUTPUT.CSV

number|name|age|gender|website
B24|mike|22|male|<a href=https://www.abcdef.com/query=B24>B24</a>
B65|john|45|male|<a href=https://www.abcdef.com/query=B65>B65</a>
B74|jane|29|female|<a href=https://www.abcdef.com/query=B74>B74</a>

To make it neater, i put some variables in place

test.sh

#!/bin/bash

NUMBER=(data from the "number" column of each corresponding row in the input.csv file)
URL=https://www.abcdef.com/search?query=$NUMBER

awk -F"|" 'BEGIN { OFS = "|" } {$5="<a href='$URL'>'$NUMBER'</a>"; print}' input.csv > output.csv

So far, i've been able to do this which just creates a new column and repeats the hyperlink all through the column and the result of my failed output is below:

FAILED RESULT

number|name|age|gender|<a href=https://www.abcdef.com/search?query=></a>
B65|john|45|male|<a href=https://www.abcdef.com/query=B65>B65</a>
B74|jane|29|female|<a href=https://www.abcdef.com/query=B74>B74</a>

Appreciate the help!

CodePudding user response：

1st solution: With your shown samples, please try following awk code. Written and tested in GNU awk.

awk '
BEGIN { FS=OFS="|" }
FNR==1{
  print $0,"website"
  next
}
{
  print $0,"<a href=https://www.abcdef.com/query="$1">"$1"</a>"
}
'  Input_file

2nd solution adding 1 more variation of awk code above where making variable named firstHeader which has value of <a href=https://www.abcdef.com/query= just in case we need to change it in future we need not to change main program then.

awk -v firstHeader="<a href=https://www.abcdef.com/query=" -v secheader="</a>" '
BEGIN { FS=OFS="|" }
FNR==1{
  print $0,"website"
  next
}
{
  print $0,firstHeader $1">"$1 secheader
}
'  Input_file

OR use ternary operator condition little tweak in above version:

awk -v firstHeader="<a href=https://www.abcdef.com/query=" -v secheader="</a>" '
BEGIN { FS=OFS="|" }
{
  print $0, (FNR==1 ? "website" : firstHeader $1">"$1 secheader)
}
'  Input_file

CodePudding user response：

This sed should work for you:

sed -E '1s/$/|website/; 2,$s~^([^|] ).*~&|<a href=https://www.abcdef.com/query=\1>\1</a>~' file

number|name|age|gender|website
B24|mike|22|male|<a href=https://www.abcdef.com/query=B24>B24</a>
B65|john|45|male|<a href=https://www.abcdef.com/query=B65>B65</a>
B74|jane|29|female|<a href=https://www.abcdef.com/query=B74>B74</a>

CodePudding user response：

I would harness GNU AWK for this task following way, let file.txt content be

number|name|age|gender
B24|mike|22|male
B65|john|45|male
B74|jane|29|female

then

awk 'BEGIN{FS=OFS="|"}NR==1{print $0,"website";next}{printf "%s|<a href=https://www.abcdef.com/query=%s>%s</a>\n", $0, $1, $1}' file.txt

gives output

number|name|age|gender|website
B24|mike|22|male|<a href=https://www.abcdef.com/query=B24>B24</a>
B65|john|45|male|<a href=https://www.abcdef.com/query=B65>B65</a>
B74|jane|29|female|<a href=https://www.abcdef.com/query=B74>B74</a>

Explanation: I inform GNU AWK that pipe character (|) is both field separator (FS) and output field separator (OFS). For first line I print line as is followed by website, for all other I use printf with string with 3 places to fill denoted by %s: 1st for already existing fields, 2nd for query value, 3rd for a tag text, which I fill using whole using line as is ($0) and first field ($1) and first field ($1). Note that I newline character (\n) at end, as printf does not furnish it.

(tested in gawk 4.2.1)

CodePudding user response：

keep it simple :

{m,g}awk -F'^$' '
NF < NR ? (ORS =(_=sprintf("%.3s",$__))">"(_)"</a>\n")^!  NF \
        : $!NF = $__ "|website"' OFS='|<a href=https://www.abcdef.com/query='

number|name|age|gender|website
B24|mike|22|male|<a href=https://www.abcdef.com/query=B24>B24</a>
B65|john|45|male|<a href=https://www.abcdef.com/query=B65>B65</a>
B74|jane|29|female|<a href=https://www.abcdef.com/query=B74>B74</a>