1

I have a CSV file and I need to filter out some rows that do not contain certain values. Because of this I don't care about those rows and want to remove them or put the results of the command in a new csv file.

This is the format of my CSV file:

employeeid,time,homephone,workphone,ssn,insurance,address,state,salary,position,rank,boss,hiredate

Now there are some rows that have no information some of these fields. How would I perform an awk or sec command to read all the lines in the csv file and only put the lines which no fields are null into another file? Or would it be possible to replace every ,, with a word like notthere? I have some word replacing going on here but this is not 100% working.

So far I have something like this:

sed -e 's/^,/notthere,/' old.csv > new.csv

This pretty much does nothing that I am looking for. I would greatly appreciate it if someone could help me out. I am not that experienced with using linux commands at all.

Thank you!

Improve this question

4 Answers 4

Reset to default
2

Seems like you could also grep the file for connected commas:

grep -v ',,' somefile.csv > newfile.csv

EDIT: Just realized you have fields at the beginning and end you want to check for as well. We can include those with regex, like so:

grep -vE ',,|^,|,$' somefile.csv > newfile.csv

grep -v means 'inverse', in other words: print all lines that don't match these patterns: two commas together, a comma at the start of the line, a comma at the end of the line. The | here means "or".

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

2 Comments

Thank you so much! The inverse way of doing things is interesting and works at the same time. Now I can carry on with my other code I need to do. Thank you!
No problem. I actually use grep -v and grep -vE all the time.
0

This should work:

sed -e 's/,,/,notthere,/' old.csv > new.csv
Improve this answer

1 Comment

should add -e 's/^,/notthere,/' -e 's/,$/,notthere/' to check first and last field for emptiness
0

Some sample data would have helped, but try this to skip lines with empty fields:

awk -F , '{n=0; for (i=1;i<=NF;i++) if ($i=="") n++} n==0' filename

More readably

awk -F , '{
    empty=0
    for (i=1; i<=NF; i++) {
        if ($i == "") {
            empty++
        }
    }
    if (empty == 0) {
        print
    }
}' filename
Improve this answer

Comments

0

It's worth noting that the above examples are "grepping" across the entire row. Another approach is to search specific columns for non-existence using awk as shown below. Given a comma delimited file, the below script only prints lines that have empty values in column 2 signified by $2. The print $0 portion means print the entire line.

Print all lines where column 2 is empty, redirect to new.csv

awk -F "," '$2 !~ /./ {print $0}' old.csv > new.csv

Another related example, print column 3 when only it matches regular expression [0-9]

awk -F "," '$3 ~ /[0-9]/ {print $3}' old.csv > new.csv
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.