Drop columns containing string

[BastienP]

Hello everyone,

I'm currently working on an automated process which extract user info from AD with csvde, then do some treatments with perl scripts, convert the csv file into xls in order to do some modifications by a specific user in Excel, then convert the xls to csv, format it in a specific type in order to be imported to the company's phonebook (an intranet app with SQL Database), and, finally, convert it to ldf in order to import the modifications for every user into the ActiveDirectory again (so that we won't have to do the modifications every time an extraction is done).

Great isn't it ? Well the thing is I'm far from being a Perl Script Expert so I have some problems in some of my scripts...

And I'm currently stuck with a stupid thing: I'm trying to drop a column in my csv when encountering a specific String (as an example let's say I want to drop the "Distinguishedname" column from my AD Extract). I have found and modificated a Script that drops a column if every data contained in the column (ie for every line) is equal to my string, but not if the string is encountered once.

Do you have a solution please ?

If you have also the solution for dropping the line IF a String is encountered in the column named "example" -in the header line-, I'm fully interested !!!!

Here is the code I'm trying to adapt:

--------------------------

#!/usr/bin/perl
use warnings;
use strict;


my $file = 'input-light.csv';

open my $FH, '<', $file or die "Cannot open '$file' $!";

my @headers = map 1, split /,/, <$FH>, -1;
my @keep = ( undef ) x @headers;

while ( <$FH> ) {
     chomp;
     my @fields = split /,/, $_, -1;
     die "Error: expected " . @headers . " fields but reading " . 
@fields . " fields instead.\n"
         if @headers < @fields;
     for my $i ( 0 .. $#fields ) {
         $keep[ $i ] = $i if $fields[ $i ] ne '"Distinguishedname"';
         }
     exit 0 if @headers == grep defined, @keep;
     }

close $FH;

@keep = grep defined, @keep;

( $^I, @ARGV ) = ( '.back2', $file );

while ( <> ) {
     chomp;
     print join( ',', ( split /,/, $_, -1 )[ @keep ] ), "\n";
     }

__END__

------------------

Thanks by advance,
Bastien

[BastienP]

And here is my input-light.csv example:

"Distinguishedname","EmployeeID","Lastname","Firstname","Phone","Email","Country","CountryShort","AccountIsDisabled","AccountExpirationStatus","objectguid",
"CN=Alberto TUVILI,OU=Users,DC=mydomain,DC=com","[e-mail]","TUVILI","Alberto"," ","[e-mail]","Czek Republic","CZ","False","Never","EB45EBD232A3F34EB9A4A3CDR400139D",

[BastienP]

Yet another reply for a precision: The header line can contain quotes or not, I've got a script for the replacement without the quotes (ie Distinguishedname,EmployeeID,Lastname,Firstname,....)

Bastien

[d5e5]

I haven't figured this out yet but I think the first step for something with somewhat complex CSV parsing requirements would be to choose and install a good CSV parsing module for Perl. There are some simple examples of Parsing CSV at this link.

[BastienP]

Hello d5e5

I've already tried that but with no success... And the mentionned script is working when Distinguishedname is encountered in the whole column so I thought it would be pretty easy to adapt it, but I think I was mistaking, 'cause I'm not finding out how to do that !

Any idea ?

If you've gotta working script with a parsing module I'd be glad to try it though ! :o)

Thanks by advance,
Bastien

[BastienP]

Here's a little script for the lines that I must delete when finding a specific String (in my example the string is TESTTEST).

Inputfile = input-light2.csv
OutputFile= input-light2-processed.csv

#!/usr/bin/perl -w                       
                                                                     
open(FILE, "< input-light2.csv" )
    or die ("Error while opening file" );
@save = <FILE>;
close(FILE);
foreach (@save)
{
    $_ = "" if ($_ =~ m/TESTTEST/);
    chomp $_;
}
open(FILE, ">input-light2-processed.csv" )
    or die ("Error while opening file" );
foreach (@save)
{
    print FILE $_."\n" if ($_);
}
close(FILE);

[BastienP]

I've found even easier for the line-dropping thing:

in command line (in my case in a command file, batch type):

perl -n -i.bak -e "print unless /TESTTEST/" input-light2.csv

But I still don't have the correct script for the Column deletion in my CSV file. Niarg. If someone has the solution please tell me, 'cause it's now becoming more and more urgent...

Thanks by advance !
Bastien

[d5e5]

Hello d5e5

I've already tried that but with no success... And the mentionned script is working when Distinguishedname is encountered in the whole column so I thought it would be pretty easy to adapt it, but I think I was mistaking, 'cause I'm not finding out how to do that !

Any idea ?

If you've gotta working script with a parsing module I'd be glad to try it though ! :o)

Thanks by advance,
Bastien

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

my $csv = Text::CSV->new();

open (my $fh, "<", $file) or die $!;
my @file = <$fh>;
close $fh;

my $str2find = "Distinguishedname";
my $dropcol;
foreach (@file) { #Loop through array 1st time to find index of column to drop
    if ($csv->parse($_)) {
        my @columns = $csv->fields();
        my $rv = idx (\@columns, $str2find);
        $dropcol = $rv if defined($rv);
    } else {
        my $err = $csv->error_input;
        print "Failed to parse line: $err";
    }
}

foreach (@file) { #Loop through array 2nd time to print data without dropped column
    if ($csv->parse($_)) {
        my @columns = $csv->fields();
        splice (@columns, $dropcol, 1); #Remove column starting at $dropcol for length of 1
        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";
    }
}

sub idx {#Loop through array, return index of string if found
    my @array = @{$_[0]};
    my $str2find = $_[1];
    foreach (0..scalar(@array)-1) { #check columns from 0 to size of array
        return $_ if $array[$_] eq $str2find;
    }
    return undef #If string not found in array
}

[BastienP]

Thanks a lot David, the script is exactly doing what I needed !

I'll just make some slight adjustments because of the quotes. When there's a space in the original field quotes stays, but when there is no space the quotes are deleted.

You rule ! :-)
Bastien

[BastienP]

Here I am, trying to drop a line where a specific field is equal to a specific value, and stuck again...

I'm replying to this thread because it's the same kind of stuff but feel free to tell me if I'd rather split the topic.

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).

Regards,
Bastien

[BastienP]

I even thought about another little stuff... Not only deleting lines where users are disabled but writing them into a new csv file...

I must stop working, I've got a new idea of script every 2 minutes, it's awfull !!! ;-)

Bastien
The head into perl at 200%

[d5e5]

As a rule it's better to start a new topic, even if the problem is similar to the solved one. We'll still be able to refer to the solved one if necessary. There is an option where you can attach a text file to your post if you want. That way the record format of the data is preserved.

Here I am, trying to drop a line where a specific field is equal to a specific value, and stuck again...

I'm replying to this thread because it's the same kind of stuff but feel free to tell me if I'd rather split the topic.

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).

Regards,
Bastien

[BastienP]

Ok then a new topic it will be ! :-)