Mixing
Bash: join data from two csv files
Clash Royale CLAN TAG#URR8PPP
up vote
3
down vote
favorite
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.
awk csv join
asked 2 hours ago
DavDav
162
New contributor
DavDav is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
 |Â
show 1 more comment
up vote
3
down vote
favorite
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.
awk csv join
asked 2 hours ago
DavDav
162
New contributor
DavDav is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Bash isn’t a text editor.
– Jeff Schaller
1 hour ago
Sorry for the confusion; I just meant that example as a sketchup table, an illustration. Yes, I use semicolon as field delimiter...and yes I am aware that Bash is not a text editor. I have done similar things using PHP, using a function in a foreach loop, calling a function in every iteration that parses file B for the 'username'. I had hoped to do the same in Bash - so far no luck. Anyone, that has a constructive suggestion for me :) ?
– DavDav
1 hour ago
then please provide the correct csv tables, or at least csv tables...
– RoVo
1 hour ago
Alright; done! Those were the correct tables; I fail to see that this format adds considerable readability, since I'm used to doing it in the sketchup form in database fora, I frequent. Anyone got some constructive comment for me now :) ?
– DavDav
1 hour ago
but the solution is way easier.
– RoVo
1 hour ago
 |Â
show 1 more comment
up vote
3
down vote
favorite
up vote
3
down vote
favorite
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.
awk csv join
asked 2 hours ago
DavDav
162
New contributor
DavDav is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
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.
awk csv join
awk csv join
asked 2 hours ago
DavDav
162
New contributor
DavDav is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 2 hours ago
DavDav
162
New contributor
DavDav is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited 1 hour ago
asked 2 hours ago
DavDav
162
New contributor
DavDav is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 2 hours ago
DavDav
162
asked 2 hours ago
DavDav
162
162
New contributor
DavDav is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
DavDav is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
DavDav is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Bash isn’t a text editor.
– Jeff Schaller
1 hour ago
Sorry for the confusion; I just meant that example as a sketchup table, an illustration. Yes, I use semicolon as field delimiter...and yes I am aware that Bash is not a text editor. I have done similar things using PHP, using a function in a foreach loop, calling a function in every iteration that parses file B for the 'username'. I had hoped to do the same in Bash - so far no luck. Anyone, that has a constructive suggestion for me :) ?
– DavDav
1 hour ago
then please provide the correct csv tables, or at least csv tables...
– RoVo
1 hour ago
Alright; done! Those were the correct tables; I fail to see that this format adds considerable readability, since I'm used to doing it in the sketchup form in database fora, I frequent. Anyone got some constructive comment for me now :) ?
– DavDav
1 hour ago
but the solution is way easier.
– RoVo
1 hour ago
 |Â
show 1 more comment
Bash isn’t a text editor.
– Jeff Schaller
1 hour ago
Sorry for the confusion; I just meant that example as a sketchup table, an illustration. Yes, I use semicolon as field delimiter...and yes I am aware that Bash is not a text editor. I have done similar things using PHP, using a function in a foreach loop, calling a function in every iteration that parses file B for the 'username'. I had hoped to do the same in Bash - so far no luck. Anyone, that has a constructive suggestion for me :) ?
– DavDav
1 hour ago
then please provide the correct csv tables, or at least csv tables...
– RoVo
1 hour ago
Alright; done! Those were the correct tables; I fail to see that this format adds considerable readability, since I'm used to doing it in the sketchup form in database fora, I frequent. Anyone got some constructive comment for me now :) ?
– DavDav
1 hour ago
but the solution is way easier.
– RoVo
1 hour ago
Bash isn’t a text editor.
– Jeff Schaller
1 hour ago
Bash isn’t a text editor.
– Jeff Schaller
1 hour ago
Sorry for the confusion; I just meant that example as a sketchup table, an illustration. Yes, I use semicolon as field delimiter...and yes I am aware that Bash is not a text editor. I have done similar things using PHP, using a function in a foreach loop, calling a function in every iteration that parses file B for the 'username'. I had hoped to do the same in Bash - so far no luck. Anyone, that has a constructive suggestion for me :) ?
– DavDav
1 hour ago
Sorry for the confusion; I just meant that example as a sketchup table, an illustration. Yes, I use semicolon as field delimiter...and yes I am aware that Bash is not a text editor. I have done similar things using PHP, using a function in a foreach loop, calling a function in every iteration that parses file B for the 'username'. I had hoped to do the same in Bash - so far no luck. Anyone, that has a constructive suggestion for me :) ?
– DavDav
1 hour ago
then please provide the correct csv tables, or at least csv tables...
– RoVo
1 hour ago
then please provide the correct csv tables, or at least csv tables...
– RoVo
1 hour ago
Alright; done! Those were the correct tables; I fail to see that this format adds considerable readability, since I'm used to doing it in the sketchup form in database fora, I frequent. Anyone got some constructive comment for me now :) ?
– DavDav
1 hour ago
Alright; done! Those were the correct tables; I fail to see that this format adds considerable readability, since I'm used to doing it in the sketchup form in database fora, I frequent. Anyone got some constructive comment for me now :) ?
– DavDav
1 hour ago
but the solution is way easier.
– RoVo
1 hour ago
but the solution is way easier.
– RoVo
1 hour ago
 |Â
show 1 more comment
4 Answers
4
active
oldest
votes
up vote
4
down vote
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.
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. 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 it that needed 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
- Join the two files on the usernames (whitespaces are significant).
- Extract the data for the users that are locked out (the
0will have been changed toFalseat this point in the pipeline). - Extract the email addresses.
- Remove the CSV header (using the last
sedcommand).
answered 45 mins ago
Kusalananda
108k14209332
add a comment |Â
up vote
0
down vote
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
Hope this helps
answered 50 mins ago
Lewis M
3893
add a comment |Â
up vote
0
down vote
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>1print $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
answered 1 hour ago
RoVo
1,832213
Not interested in sqlite solutions but thanks anyway :)
– DavDav
1 hour ago
All I need is a cronjob to run my bash script, to loop over my users and to inform those with locked accounts of the fact. I know my cron and I know some Bash but then again not enough, just need that comment that makes it happen ;)
– DavDav
56 mins ago
strange application that keeps account information incsvfiles ... but okay, I added anawksolution. But still, what you want to do is easily possible with the sqlite solution.
– RoVo
39 mins ago
Thanks :) as for 'strange application', well ultimately that is what is used for account info in Linux itself, in /etc/passwd and /etc/shadow (those are no more than simple csv files with ":" as delimiter ;) ) As for convinience using a db: agree and If I were to decide, I might even throw in a MariaDB just for the fun of it but this is not my design.
– DavDav
11 mins ago
haha, yes you're right ... I need to reconsider my statement ;-)
– RoVo
5 mins ago
add a comment |Â
up vote
0
down vote
#!/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
answered 37 mins ago
marco
263
New contributor
marco is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
uuoc and in general quite prone to errors.
– RoVo
28 mins ago
add a comment |Â
4 Answers
4
active
oldest
votes
4 Answers
4
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
4
down vote
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.
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. 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 it that needed 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
- Join the two files on the usernames (whitespaces are significant).
- Extract the data for the users that are locked out (the
0will have been changed toFalseat this point in the pipeline). - Extract the email addresses.
- Remove the CSV header (using the last
sedcommand).
answered 45 mins ago
Kusalananda
108k14209332
add a comment |Â
up vote
4
down vote
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.
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. 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 it that needed 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
- Join the two files on the usernames (whitespaces are significant).
- Extract the data for the users that are locked out (the
0will have been changed toFalseat this point in the pipeline). - Extract the email addresses.
- Remove the CSV header (using the last
sedcommand).
answered 45 mins ago
Kusalananda
108k14209332
add a comment |Â
up vote
4
down vote
up vote
4
down vote
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.
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. 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 it that needed 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
- Join the two files on the usernames (whitespaces are significant).
- Extract the data for the users that are locked out (the
0will have been changed toFalseat this point in the pipeline). - Extract the email addresses.
- Remove the CSV header (using the last
sedcommand).
answered 45 mins ago
Kusalananda
108k14209332
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.
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. 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 it that needed 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
- Join the two files on the usernames (whitespaces are significant).
- Extract the data for the users that are locked out (the
0will have been changed toFalseat this point in the pipeline). - Extract the email addresses.
- Remove the CSV header (using the last
sedcommand).
answered 45 mins ago
Kusalananda
108k14209332
edited 2 mins ago
answered 45 mins ago
Kusalananda
108k14209332
answered 45 mins ago
Kusalananda
108k14209332
answered 45 mins ago
Kusalananda
108k14209332
108k14209332
add a comment |Â
add a comment |Â
up vote
0
down vote
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
Hope this helps
answered 50 mins ago
Lewis M
3893
add a comment |Â
up vote
0
down vote
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
Hope this helps
answered 50 mins ago
Lewis M
3893
add a comment |Â
up vote
0
down vote
up vote
0
down vote
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
Hope this helps
answered 50 mins ago
Lewis M
3893
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
Hope this helps
answered 50 mins ago
Lewis M
3893
answered 50 mins ago
Lewis M
3893
answered 50 mins ago
Lewis M
3893
answered 50 mins ago
Lewis M
3893
3893
add a comment |Â
add a comment |Â
up vote
0
down vote
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>1print $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
answered 1 hour ago
RoVo
1,832213
Not interested in sqlite solutions but thanks anyway :)
– DavDav
1 hour ago
All I need is a cronjob to run my bash script, to loop over my users and to inform those with locked accounts of the fact. I know my cron and I know some Bash but then again not enough, just need that comment that makes it happen ;)
– DavDav
56 mins ago
strange application that keeps account information incsvfiles ... but okay, I added anawksolution. But still, what you want to do is easily possible with the sqlite solution.
– RoVo
39 mins ago
Thanks :) as for 'strange application', well ultimately that is what is used for account info in Linux itself, in /etc/passwd and /etc/shadow (those are no more than simple csv files with ":" as delimiter ;) ) As for convinience using a db: agree and If I were to decide, I might even throw in a MariaDB just for the fun of it but this is not my design.
– DavDav
11 mins ago
haha, yes you're right ... I need to reconsider my statement ;-)
– RoVo
5 mins ago
add a comment |Â
up vote
0
down vote
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>1print $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
answered 1 hour ago
RoVo
1,832213
Not interested in sqlite solutions but thanks anyway :)
– DavDav
1 hour ago
All I need is a cronjob to run my bash script, to loop over my users and to inform those with locked accounts of the fact. I know my cron and I know some Bash but then again not enough, just need that comment that makes it happen ;)
– DavDav
56 mins ago
strange application that keeps account information incsvfiles ... but okay, I added anawksolution. But still, what you want to do is easily possible with the sqlite solution.
– RoVo
39 mins ago
Thanks :) as for 'strange application', well ultimately that is what is used for account info in Linux itself, in /etc/passwd and /etc/shadow (those are no more than simple csv files with ":" as delimiter ;) ) As for convinience using a db: agree and If I were to decide, I might even throw in a MariaDB just for the fun of it but this is not my design.
– DavDav
11 mins ago
haha, yes you're right ... I need to reconsider my statement ;-)
– RoVo
5 mins ago
add a comment |Â
up vote
0
down vote
up vote
0
down vote
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>1print $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
answered 1 hour ago
RoVo
1,832213
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>1print $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
answered 1 hour ago
RoVo
1,832213
edited 41 mins ago
answered 1 hour ago
RoVo
1,832213
answered 1 hour ago
RoVo
1,832213
answered 1 hour ago
RoVo
1,832213
1,832213
Not interested in sqlite solutions but thanks anyway :)
– DavDav
1 hour ago
All I need is a cronjob to run my bash script, to loop over my users and to inform those with locked accounts of the fact. I know my cron and I know some Bash but then again not enough, just need that comment that makes it happen ;)
– DavDav
56 mins ago
strange application that keeps account information incsvfiles ... but okay, I added anawksolution. But still, what you want to do is easily possible with the sqlite solution.
– RoVo
39 mins ago
Thanks :) as for 'strange application', well ultimately that is what is used for account info in Linux itself, in /etc/passwd and /etc/shadow (those are no more than simple csv files with ":" as delimiter ;) ) As for convinience using a db: agree and If I were to decide, I might even throw in a MariaDB just for the fun of it but this is not my design.
– DavDav
11 mins ago
haha, yes you're right ... I need to reconsider my statement ;-)
– RoVo
5 mins ago
add a comment |Â
Not interested in sqlite solutions but thanks anyway :)
– DavDav
1 hour ago
All I need is a cronjob to run my bash script, to loop over my users and to inform those with locked accounts of the fact. I know my cron and I know some Bash but then again not enough, just need that comment that makes it happen ;)
– DavDav
56 mins ago
strange application that keeps account information incsvfiles ... but okay, I added anawksolution. But still, what you want to do is easily possible with the sqlite solution.
– RoVo
39 mins ago
Thanks :) as for 'strange application', well ultimately that is what is used for account info in Linux itself, in /etc/passwd and /etc/shadow (those are no more than simple csv files with ":" as delimiter ;) ) As for convinience using a db: agree and If I were to decide, I might even throw in a MariaDB just for the fun of it but this is not my design.
– DavDav
11 mins ago
haha, yes you're right ... I need to reconsider my statement ;-)
– RoVo
5 mins ago
Not interested in sqlite solutions but thanks anyway :)
– DavDav
1 hour ago
Not interested in sqlite solutions but thanks anyway :)
– DavDav
1 hour ago
All I need is a cronjob to run my bash script, to loop over my users and to inform those with locked accounts of the fact. I know my cron and I know some Bash but then again not enough, just need that comment that makes it happen ;)
– DavDav
56 mins ago
All I need is a cronjob to run my bash script, to loop over my users and to inform those with locked accounts of the fact. I know my cron and I know some Bash but then again not enough, just need that comment that makes it happen ;)
– DavDav
56 mins ago
strange application that keeps account information in
csv files ... but okay, I added an awk solution. But still, what you want to do is easily possible with the sqlite solution.– RoVo
39 mins ago
strange application that keeps account information in
csv files ... but okay, I added an awk solution. But still, what you want to do is easily possible with the sqlite solution.– RoVo
39 mins ago
Thanks :) as for 'strange application', well ultimately that is what is used for account info in Linux itself, in /etc/passwd and /etc/shadow (those are no more than simple csv files with ":" as delimiter ;) ) As for convinience using a db: agree and If I were to decide, I might even throw in a MariaDB just for the fun of it but this is not my design.
– DavDav
11 mins ago
Thanks :) as for 'strange application', well ultimately that is what is used for account info in Linux itself, in /etc/passwd and /etc/shadow (those are no more than simple csv files with ":" as delimiter ;) ) As for convinience using a db: agree and If I were to decide, I might even throw in a MariaDB just for the fun of it but this is not my design.
– DavDav
11 mins ago
haha, yes you're right ... I need to reconsider my statement ;-)
– RoVo
5 mins ago
haha, yes you're right ... I need to reconsider my statement ;-)
– RoVo
5 mins ago
add a comment |Â
up vote
0
down vote
#!/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
answered 37 mins ago
marco
263
New contributor
marco is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
uuoc and in general quite prone to errors.
– RoVo
28 mins ago
add a comment |Â
up vote
0
down vote
#!/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
answered 37 mins ago
marco
263
New contributor
marco is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
uuoc and in general quite prone to errors.
– RoVo
28 mins ago
add a comment |Â
up vote
0
down vote
up vote
0
down vote
#!/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
answered 37 mins ago
marco
263
New contributor
marco is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
#!/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
answered 37 mins ago
marco
263
New contributor
marco is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
answered 37 mins ago
marco
263
New contributor
marco is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
answered 37 mins ago
marco
263
answered 37 mins ago
marco
263
263
New contributor
marco is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
marco is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
marco is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
uuoc and in general quite prone to errors.
– RoVo
28 mins ago
add a comment |Â
uuoc and in general quite prone to errors.
– RoVo
28 mins ago
uuoc and in general quite prone to errors.
– RoVo
28 mins ago
uuoc and in general quite prone to errors.
– RoVo
28 mins ago
add a comment |Â
DavDav is a new contributor. Be nice, and check out our Code of Conduct.
DavDav is a new contributor. Be nice, and check out our Code of Conduct.
DavDav is a new contributor. Be nice, and check out our Code of Conduct.
DavDav is a new contributor. Be nice, and check out our Code of Conduct.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2funix.stackexchange.com%2fquestions%2f472729%2fbash-join-data-from-two-csv-files%23new-answer', 'question_page');
);
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Bash isn’t a text editor.
– Jeff Schaller
1 hour ago
Sorry for the confusion; I just meant that example as a sketchup table, an illustration. Yes, I use semicolon as field delimiter...and yes I am aware that Bash is not a text editor. I have done similar things using PHP, using a function in a foreach loop, calling a function in every iteration that parses file B for the 'username'. I had hoped to do the same in Bash - so far no luck. Anyone, that has a constructive suggestion for me :) ?
– DavDav
1 hour ago
then please provide the correct csv tables, or at least csv tables...
– RoVo
1 hour ago
Alright; done! Those were the correct tables; I fail to see that this format adds considerable readability, since I'm used to doing it in the sketchup form in database fora, I frequent. Anyone got some constructive comment for me now :) ?
– DavDav
1 hour ago
but the solution is way easier.
– RoVo
1 hour ago