0

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

test.txt

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:

FinalOutput.txt

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.

  • You should export your .xlsy file as a .csv file to allow processing by a script. Please do this and add the resulting file and your sample input and output to the question as text, formatted as code blocks using the braces tool {} of the editor field. We want to be able to copy and paste your sample data. – Bodo Mar 4 at 16:39
  • Are there , in the Names or Organisations in test.xlsx? – katosh Mar 4 at 16:51
  • It does not allow me to copy and paste the output of my csv file for some reason. There are , , . , and - in the company names. – Ibrahim A Mar 4 at 17:22
  • You can redesign your important data and show us a few lines of your csv file. This way, we can understand what you look for. – ctac_ Mar 4 at 18:22
  • I edited my original post to make it easier because the entire csv process throws me off. – Ibrahim A Mar 4 at 19:21
1

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.

  • $ in2csv ActiveUsers.xlsx | tail -n+2 | while read line; do name=$(printf "$line" | csvcut -c 1 | tr -d \"); company=$(printf "$line" | csvcut -c 2 | tr -d \"); printf "s/$name/$name $company/g;" ; done | xargs -I{} sed "{}" LoginExcInternal.txt -bash: in2csv: command not found – Ibrahim A Mar 4 at 17:29
  • Is there a way to do it without this? This will be a part of a much larger script. If I have to convert this to a text file instead of using csv, I am willing to do that. – Ibrahim A Mar 4 at 17:34
  • I converted it into a text file. As of now, the way I did it is that there is the Name, there is a Tab Spacing and then there is the Company Name. It then goes on to the next line for the next User. – Ibrahim A Mar 4 at 17:39
  • I edited my original post to make it easier because the entire csv process throws me off. – Ibrahim A Mar 4 at 19:20
  • We are on to something with this: prefix="loginName:\s*" tail -n+2 UserList.txt | while read line; do name=$(printf "$line" | cut -f1) company=$(printf "$line" | cut -f2) sed -i "s/^$prefix$name/$prefix$name $company/g" Weekly.txt done This is pulling the correct information. However, it is overwriting the existing file. I want this to be outputted into a new file while leaving this one alone. Secondly, before the name, it is removing the tab and replacing it with s* on every field. Also, after the company name, it should just be a tab, instead it starts a new line. – Ibrahim A Mar 4 at 20:06
2

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+(.*)$/\1\t\2\t\3/' 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+(.*)$/\1\t\2\t\3/' 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.

  • I edited my original post to make it easier because the entire csv process throws me off. – Ibrahim A Mar 4 at 19:20
  • You can even get rid of xlsx2csv by just using tail -n+2 UserList.txt. – katosh Mar 4 at 19:45
  • @katosh right. fixed. – sborsky Mar 4 at 19:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.