Bash: join data from two csv files

Question

I have two csv files that contain various user data; they share one common field (username).

file A:
username ; Fullname ; mail
Bob      ; Bob Hope ; bob@hope.org

file B:
username ; LastLogonTime  ; AccountStatus (locked=0 or unlocked=1)
Bob      ; 2018-10-01 etc.; 0

For audit purposes, I want to use Bash to loop over A, cross checking with B whether the account is locked in which case I can mail the user to the mail address in A.

awk -F";"

allows me to go over A; that's easy - but I'm at a loss when trying to do the cross-check loop over B.

Answer 1

Using awk, first read the usernames of users whose account are locked from the second file, then extract the email addresses of these from the first file (then hope that they don't need to log in to read their emails):

awk -F ';' 'NR == FNR && $NF == 0    { names[$1] }
            NR != FNR && $1 in names { print $NF }' B.csv A.csv

This assumes that each username has an equal amount of whitespace around them in both files. If that is not so, you may use -F ' *; *' to include any space characters in the delimiter that awk is using. It also assumes that there are no embedded ; characters in the data.

NR is the record (line) number of the current record overall, and FNR is the same number but within the current file. If NR == FNR, then we are reading from the first file given on the command line (B.csv). NF is the number of fields (columns) in the current record and $NF is the data in the last field (and $1 is the data in the first field).

The code above uses an associative array/hash, names, keyed on the usernames of locked-out users read from the first file (B.csv). The $1 in names will be true if $1 is a key in that array.

Putting this into a loop:

awk -F ';' 'NR == FNR && $NF == 0    { names[$1] }
            NR != FNR && $1 in names { print $NF }' B.csv A.csv |
while read addr; do
    printf 'Would send an email to "%s"\n' "$addr"
    #mail -s 'Account locked' "$addr" <template-email.txt
done

Or something along those lines. Reading the email addresses in this way in the loop would delete any whitespace around them. The loop above does not send emails but prints the addresses that needs sending to. Remove the # before mail (and write some form email in template-email.txt) to actually send an email (but you may want to do it differently).

---

Using csvkit:

csvjoin -d ';' -c 1 A.csv B.csv |
csvgrep -c 5 -m False |
csvcut -S -c 3 | sed 1d

CSVkit provides CSV parsing tools for working with CSV files. This would be needed if your CSV data is not "simple", i.e. if it uses CSV rules for quoting embedded ; characters etc. The pipeline above will

1. Join the two files on the usernames (whitespaces are significant).
2. Extract the data for the users that are locked out (the 0 will have been changed to False at this point in the pipeline).
3. Extract the email addresses.
4. Remove the CSV header (using the last sed command).

Answer 2

Use a specialized tool for doing tasks like this (a.k.a. a database):

# Remove spaces around the field separator
sed -i.fixed 's/ *\; */\;/g' a
sed -i.fixed 's/ *\; */\;/g' b

# Add to sqlite database
echo -e '.separator ";"\n.import a.fixed a' | sqlite3 db.sqlite
echo -e '.separator ";"\n.import b.fixed b' | sqlite3 db.sqlite

# Select whatever you need
echo -e 'select a.username,a.mail,b."AccountStatus (locked=0 or unlocked=1)" from a join b on a.username = b.username;' | sqlite3 db.sqlite

---

awk solution:

users=( $(awk -F";" 'NR>1{print $1";"$3}' a) )
for u in "${users[@]}"; do
    username=$(echo "$u" | cut -d';' -f1)
    mail=$(echo "$u" | cut -d';' -f2)
    awk -v "u=$username" -v "m=$mail" -F';' 'NR>1 { if ($3 == 0) print "User "u" ("m") is locked"; }' b
done

Answer 3

#!/bin/bash 

cat fileA.txt | sed 1d | while IFS=';' read -r line; do #read fileA.txt starting with line #2
name=$(echo $line | awk '{print $1}') #find names in each line/column 1 of the table 
lock_status=$(grep $name fileB.txt | awk '{print $5}') # find lock/unlock status in fileB.txt

    if [[ "$lock_status" -eq 0 ]];then 

    echo "Locked: To mail the user : replace echo by the command mail";

        else

    echo "unlocked";
     fi
done

Answer 4

First, if you do have spaces around your delimiter, you need to remove them in your script, like @RoVo said. The sed commands will do that for you.

Second, you basically want to have a while loop reading in each line from the fixed fileA, and getting the user name and email address, and optionally the user's full name. You then want to check for the status of that user in the fixed fileB.

Something like the following little loop should get you started:

#!/bin/bash

# Remove spaces around delimiter
sed -i.fixed 's/[       ]*\;[   ]*/\;/g' fileA
sed -i.fixed 's/[       ]*\;[   ]*/\;/g' fileB

# Read in each line from the fixed fileA
while read l; do

  # Skip the header line
  [[ ${l} =~ ^username ]] && continue

  # Get the user from the line that was read in.
  u=$(echo ${l} | awk -F\; '{print $1}')

  # Get the lock status for that user from the fixed fileB
  l=$(awk -F\; -v u=${u} '{if ($1 == u) {print $3}}' fileB.fixed)

  # Echo out the 2 fields.
  echo ${u}=${l}

  # Other stuff can go here.
done <fileA.fixed

exit 0