Delete lines in CSV where data in column equals a fixed value

[BastienP]

Dear Daniweb Community,

Like I've previously explained on an other topic, I'm trying to delete lines of a csv file where the value of a column is equal to a fixed value.

In other words I have to check if a specific column value for a line is equal to a fixed value. For instance, I still have this file with user account data of the ActiveDirectory, a user per line. If this user's account has the status disabled, I have to drop the line (because I won't mention him on the phonebook, he's probably gone of the company). So I'm comparing the field named "AccountDisabled" in the header to 0 or 1. If the value is 1, then drop the line.

I'll post my tests scripts (but non-working like I want them to) tomorrow as I don't have them at home, and must wait until tomorrow at work in order to post them.

If you already have some script that does that I would be really really gratefull !!!

Thanks by advance,

Regards,
Bastien

[d5e5]

I made up the following data to test and put it in a file called 'users.csv'.

"Name","ComplicatedColumn","AccountDisabled","OnSite",
"Fred","Age=25,Pet=Fish","0","1",
"Donna","Age=29,Pet=Cat","0","1",
"Tom","Age=35,Pet=Gerbil","1","0",
"Linda","Age=27,Pet=Dog","0","0",

The following works for me (finally) except if the "AccountDisabled" header name doesn't exist in the file it won't catch that error... so it may need better error-checking.

#!/usr/bin/perl
use strict;
use warnings;
use Text::CSV;
my $dir = '/home/david/Programming/Perl';
my $file = $dir . '/' . 'users.csv';

my $csv = Text::CSV->new({always_quote => 1});


open (my $fh, "<", $file) or die $!;
my @file = <$fh>;
close $fh;
my $check_colname= "AccountDisabled";
my $check_col_index;
if ($csv->parse($file[0])) { #Look at the first line to find index of column name
    my @header = $csv->fields();
    ($check_col_index) = grep { $header[$_] eq $check_colname} 0..$#header;
    print "Index is $check_col_index\n";
}else{
    die "Couldn't parse first line of $file";
}

foreach (@file) { #Loop through array and print
    if ($csv->parse($_)) {
        my @columns = $csv->fields();
        next if $columns[$check_col_index] eq "1"; #If AccountDisabled column has a "1" in it
        my $status = $csv->combine(@columns);    # combine columns into a string
        my $line   = $csv->string();             # get the combined string
        print "$line\n";
    } else {
        my $err = $csv->error_input;
        print "Failed to parse line: $err";
    }
}

I'll be away tomorrow (Saturday). Have a good weekend.

[BastienP]

Hello David,

Once again you've helped me a lot ! Thanks buddy !!!

Your algorithm works 100%, and is way more efficient than what I was trying to do (my nickname could be UglyCode...).

Sorry for the late post, I had to work on some other stuff (something that needed to be done for the day before oc course, as usual) and had no time for myself, nor for spending some spare time of the forum.

Thanks once again.

Regards,
Bastien

[d5e5]

Hello Bastien,
You're quite welcome. Thanks for the feedback. No problem about the delay. I was thinking that if my assumption that all fields in your input were in quotes was wrong -- i.e. if the numeric fields were not in quotes, then the script wouldn't work correctly without a minor change. I'm glad it worked.

Regards,
David