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

Question

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|B24
B65|john|45|male|B65
B74|jane|29|female|B74

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="'$NUMBER'"; 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|
B65|john|45|male|B65
B74|jane|29|female|B74

Appreciate the help!

user_5866580 · Answer

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,""$1""
}
'  Input_file

2nd solution adding 1 more variation of awk code above where making variable named firstHeader which has value of just in case we need to change it in future we need not to change main program then.


awk -v firstHeader=""$1 secheader
}
'  Input_file

OR use ternary operator condition little tweak in above version:
awk -v firstHeader=""$1 secheader)
}
'  Input_file

user_548225 · Answer

This sed should work for you:

sed -E '1s/$/|website/; 1!s~^([^|]+).*~&|\1~' file

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

user_10785975 · Answer

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|%s
", $0, $1, $1}' file.txt

gives output

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

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 () at end, as printf does not furnish it.

(tested in gawk 4.2.1)

user_5424988 · Answer

Based on the line number, you might also append a pipe and either website or the hyperlink at the end of the line.

awk -F"|" 'sub(/$/,(NR==1?"|website":"|"$1""))' input.csv > output.csv

The content of output.csv:

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

user_14672114 · Answer

keep it simple :

{m,g}awk -F'^$' '
NF < NR ? (ORS =(_=sprintf("%.3s",$__))">"(_)"
")^!++NF \
        : $!NF = $__ "|website"' OFS='|


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

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

5 Answers