Wednesday, October 5, 2022

[SOLVED] Copy data from each row in first column into a html hyperlink in the last column using Sed, Awk, etc

Issue

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!


Solution

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


Answered By - RavinderSingh13
Answer Checked By - Katrina (WPSolving Volunteer)
at