Ytdyklly

I have 2 text files. One is the list of user names along with the company they work on the same line but separated by a tab space. The second is a output from a weekly generated report that includes more information.

The goal is to take the name from the weekly report and replace it with the name and company from the list of user names.

List of user names and companies look like this on a UserList.txt file where it is separated by a tab space.

Name1      Company 1

Name2      Company 2

Name3      Company 3

Name4      Company 4

Name5      Company 5

Weekly output file comes in a Weekly.txt file

I do not know how I can look into the first text file to get the name and company name and replace only that portion on the second text file.

I am looking for the final output to look something like:

So far, I found a way to manually do this using the sed command. Here is what I have so far:

sed -r 's/Name1/Name1 Company 1/g;s/Name2/Name2 Company 2/g;s/Name3/Name3 Company 3/g;s/Name4/Name4 Company 4/g;s/Name5/Name5 Company 5/g'

This works, but it is not reading into another file and pulling the most up to date information. Please keep in mind I am using cygwin and this is all a part of a bigger script.

One solution would be to do the replacement per line in the of the user file. The following script will edit Weekly.txt in-place:

prefix="loginName:s*"

tail -n+2 UserList.txt | while read line; do

    name=$(printf "$line" | cut -f1)

    company=$(printf "$line" | cut -f2 | tr -d 'n')

    sed -i "s/^($prefix)$name/1$name $company/g" Weekly.txt

done

To make it more efficent we can collect the replacements and execute sed once:

prefix="loginName:s*"

sedCommand=$(tail -n+2 UserList.txt | while read line; do

    name=$(printf "$line" | cut -f1)

    company=$(printf "$line" | cut -f2 | tr -d 'n')

    printf "s/^($prefix)$name/1$name $company/g;"

done)

sed "$sedCommand" Weekly.txt > new_Weekly.txt

This version leaves Weekly.txt untouched and writes the result to new_Weekly.txt.

Use the join command:

join -t $'t' -1 1 -2 2 -o 2.1,1.1,1.2,2.3 

    user_list.txt 

    <(sed -E 's/^(loginName:)s+(S+)s+(.*)$/1t2t3/' test.txt)

join will join the two tables using column 1 in the first file (-1 1) and column 2 in the second file (-2 2). It will print the columns in the order specified with -o.

In the original version of the question, the user list was in XLSX format. In that case:

join -t $'t' -1 1 -2 2 -o 2.1,1.1,1.2,2.3 

    <(xlsx2csv -d tab test.xlsx | tail -n +2) 

    <(sed -E 's/^(loginName:)s+(S+)s+(.*)$/1t2t3/' test.txt)

On Debian, xlsx2csv is available as a package (apt install xlsx2csv).
join is part of the coreutils package.

Edit: Adjustments to use tab instead of comma as delimiter, and user list in plain text instead of XLSX.