How do I delete a row from a CSV in bash using sed or awk if a string exists in two columns?

Question

I'm a beginner-mid level bash scripter and I'm not very familiar with working with csv files through terminal.

Through the hours of research I've wasted on this, I'm guessing sed or awk will be my best bet, I'm just not certain the best way to accomplish this.

The CSV is as follows:

Owner,id,permission.deleted,permission.displayName,permission.domain,permission.emailAddress,permission.id,permission.photoLink,permission.role,permission.type
[email protected],some_file_id,False,Display Name,domain.com,[email protected],permissionidnumber,,writer,user
[email protected],some_file_id,False,Display Name,domain.com,[email protected],permissionidnumber,url,owner,user

My goal is to remove any lines where the owner is granted permissions from the original csv.

Ideally, I'd like something along the lines of "If Column A (Owner) matches Column F (permission.emailAddress), delete the line"

Desired Output - Replace existing CSV with: The CSV is as follows:

Owner,id,permission.deleted,permission.displayName,permission.domain,permission.emailAddress,permission.id,permission.photoLink,permission.role,permission.type
[email protected],some_file_id,False,Display Name,domain.com,[email protected],permissionidnumber,,writer,user

The command I'm running needs to use the CSV to read the permissions appropriately and I'm removing the owner since they retain ownership and if I try to grant it to them again, they receive an email and I'm trying to avoid spamming my users.

If I can't grab match two columns within the CSV and delete it from there, I can probably grab the [email protected] address and set it to a variable to use if that's easier. I just have to run this against ~100 unique users so the more I can automate, the better.

Please add your desired output (no description, no images, no links) for that sample input to your question (no comment). — Cyrus
– Cyrus, Commented Jun 30, 2021 at 19:37
Regarding I'm guessing sed or awk will be my best bet - you are correct about awk. — Ed Morton
– Ed Morton, Commented Jun 30, 2021 at 21:55

Ed Morton · Accepted Answer · 2021-06-30 22:04:35Z

1

Using any awk in any shell on every Unix box, the following will execute orders of magnitude faster than a shell read loop with far simpler and far briefer code:

awk -F, '$1 != $6' file

For example:

$ awk -F, '$1 != $6' file
Owner,id,permission.deleted,permission.displayName,permission.domain,permission.emailAddress,permission.id,permission.photoLink,permission.role,permission.type
[email protected],some_file_id,False,Display Name,domain.com,[email protected],permissionidnumber,,writer,user

To modify the original file with GNU awk use awk -i inplace -F, '$1!=$6' file or with any awk awk -F, '$1!=$6' file > tmp && mv tmp file.

edited Jun 30, 2021 at 22:04

answered Jun 30, 2021 at 21:57

Ed Morton

209k18 gold badges90 silver badges212 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

mb_bash Over a year ago

This makes a lot of sense and it's very flexible, thank you so much!

Paul Hodges · Accepted Answer · 2021-06-30 20:41:59Z

0

Maybe awk.

$: cat x
1 2 3 4 5 6 7
3 4 3 4 5 3 7
5 6 3 4 5 6 7
7 8 3 4 5 6 7
9 0 3 4 5 9 7

awk '$1 == $6 { next } 1' x
1 2 3 4 5 6 7
5 6 3 4 5 6 7
7 8 3 4 5 6 7

answered Jun 30, 2021 at 20:41

Paul Hodges

16k1 gold badge24 silver badges43 bronze badges

Collectives™ on Stack Overflow

How do I delete a row from a CSV in bash using sed or awk if a string exists in two columns?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related