Perl Issue

Hi,

I got this script from the web, this generates an LDAP report in CSV format.

#!/usr/bin/perl
#
# Copyright (c) 2004
# Ali Onur Cinar &060;cinar&064;zdo.com&062;
#
# License:
#
# Permission to use, copy, modify, and distribute this software and its
# documentation for non-commercial purposes and without fee is hereby
# granted, provided that the above copyright notice appear in all copies
# and that both the copyright notice and this permission notice and
# warranty disclaimer appear in supporting documentation, and that the name
# of Ali Onur Cinar not be used in advertising or publicity pertaining to
# distribution of the software without specific, written prior permission.
#
# @author A. Onur Cinar <cinar(a)zdo.com># @version $Id: ldap2csv.pl,v 1.1 2004/12/07 15:12:38 cinar Exp $#
use Net::LDAP;
@fields = # fields to export
(  # Label Field Name Index 
  ['First Name', 'givenName' , 0], ['Last Name' , 'sn' , 0], ['Title' , 'title' , 0],
  ['Email' , 'mail' , 0], ['Email 2' , 'mail' , 1], ['Phone' , 'telephoneNumber', 0], 
  ['Phone' , 'telephoneNumber', 1], ['Phone' , 'telephoneNumber', 2], 
  ['Phone' , 'telephoneNumber', 3], ['Phone' , 'telephoneNumber', 4], 
  ['Mobile' , 'mobile' , 0], 
  ['Birthday' , 'birthday' , 0], ['Address' , 'street' , 0], ['Address' , 'street' , 1], 
  ['Address' , 'street' , 2],
);
 
$ldap = Net::LDAP->new('localhost') or die "$@"; # open connection
$mesg = $ldap->bind( # login 
            "uid=test", password => "password");
$mesg = $ldap->search( # search 
            base => "dc=abc", 
            filter => "(objectClass=inetOrgPerson)"
);
 
$DATETIME=`date`;
 
sub get # get field value
{ return $_[0]->exists($_[1]) ? $_[0]->get($_[1])->[$_[2] ? $_[2] : 0] : '';}
 
print "$DATETIME"; # first line with file created date/time
print '"'.(join '","', "Report", # header map {$_->[0]} @fields)."\"\n";
foreach $entry ($mesg->all_entries) # for each entry
{ 
   @dn = map {s/[a-z]+=//gi; $_ = ucfirst} # organization 
          reverse split /,/, $entry->dn; 
   shift @dn; shift @dn; 
   shift @dn; pop @dn; 
 
   print '"'
       . (join '","', "@dn",         # row map   
            {get $entry, $_->[1], $_->[2]} @fields)
       . "\"\n";
}
$mesg = $ldap->unbind; # close connection

This script prints value in the following format :

 
"abc","1234","HCM","John D","JOHN D","rocks" 12","seigo"
"abc2","12345","TDM","Mr Say","Mr Say T","hupt","margo"

If you see there is a quote in the field (rocks" 12), I want to remove the quote from the field value only.Is there any way to modify the script and remove the quotes occuring in any of the field?

Thanks in Advance.

Change
_______________________
shift @dn; pop @dn;

print '"'.(join '","', "@dn", # row map
_______________________

To

_______________________
shift @dn; pop @dn;
map {$_ =~ s/"//} @dn;
print '"'.(join '","', "@dn", # row map
_______________________

Sorry,

The perl script got distorted while posting, here is snapshot of the exact line:

foreach $entry ($mesg->all_entries) # for each entry
{
@dn = map {s/[a-z]+=//gi; $_ = ucfirst} reverse split /,/, $entry->dn; # organization
shift @dn;
pop @dn;
print '"'.(join '","', "@dn", map {get $entry, $->[1], $->[2]} @fields)."\"\n"; # row

}

Please be cautious that "get" is actually a subroutine as mention is script:

sub get # get field value
{
return $_[0]->exists($[1])
? $_[0]->get($_[1])->[$[2] ? $_[2] : 0]
: '';
}

If still it is not clear, here is the link to access the script, from where I have downloaded the script:

ZDO.COM - Articles - Export Data From LDAP As CSV

Thanks,
Raj

@dn = map {s/[a-z]+=//gi; tr/"//d; $_ = ucfirst} # organization

I really appreciate your effort Kevin.

The following commad actually prints header, cant we use the same thing on the below line which prints data field values;

print '"'.(join '","', "@dn",map {get $entry, $->[1], $->[2]} @fields)."\"\n";

maybe:

tr/"//d for @fields;
print '"'.(join '","', "@dn",map {get $entry, $_->[1], $_->[2]} @fields)."\"\n";

This is not working, but may be we are coming closer.

actually the fields are printed out from "$entry" variable, so can't we put something around the variable or into the subroutine "get".

print '"'.(join '","', "@dn", map {get $entry, $->[1], $->[2]} @fields)."\"\n"; # row

# subroutine

sub get
{
return $_[0]->exists($[1])
? $_[0]->get($_[1])->[$[2] ? $_[2] : 0]
: '';
}

maybe:

sub get 
{
return $_[0]->exists($_[1]) ?
       $_[0]->get($_[1])->[$_[2] ?
       $_[2] =~ tr/"//d : 0] : '';
}

remove this line:

tr/"//d for @fields;

Thanks for reply.

However, it is not working. Can't you put the same thing on this variable "$_[0]"?

I believe this is value which gets printed.

Sorry raj001,

I have never used that module and I have no idea what the underlying data structure is so I can't really figure it out.

Kevin-

I really thank you for all your efforts.

Might be any other Perl experts can help me out on this forum.

Raj,

Kevin's scriptlet should work just fine. Printing the headers is an entirely different matter. You only want the data to be processed. Again, what happens when you do this?

@dn = map {s/[a-z]+=//gi; tr/"//d; $_ = ucfirst} # organization

Let me explain this further, the output of my file looks as following :

"Domain","address","department","displayname","sn","roomno","site","location"
"Internal","1234","HCM","John D","JOHN D","rocks" 12","seigo"
"Internal","12345","TDM","Mr Say","Mr Say T","hupt","margo"

There are three ways in which this csv file is written out:

First the header, then the first field value (Internal) which remains same across all the rows,and last all the fields values for each user in a row.

As pointed by you, the following line prints only the first field value:

@dn = map {s/[a-z]+=//gi; tr/"//d; $_ = ucfirst} # organization

this line prints the header:

print '"'.(join '","', "DuPont_ECD_Report",map {$_->[0]} @fields)."\"\n"; # header

and last line which prints out fields values:

print '"'.(join '","', "@dn",map {get $entry, $->[1], $->[2]} @fields)."\"\n";

the above line uses a subroutine "get", which I am unable to comprehend as how it process each entry.

sub get
{
return $_[0]->exists($[1])
? $_[0]->get($_[1])->[$[2] ? $_[2] : 0]
: '';
}

The issue is to remove the quotes occuring within the field values as mentioned in the first row.

Hope this makes things clear.

Thanks.

Ah, @dn is only the first field? That's weird. Why use an array?? Oh well. So for the second join, just do this:

print '"'.(join '","', "@dn",grep {tr/"//d,$_} map {get $entry, $_->[1], $_->[2]} @fields)."\"\n";

The grep routine evaluates the expression and returns the last result for each line in the input. Its input is the output of the map expression. The reason you wouldn't use map here is because map returns every result for each line in the input and because tr returns a number.

Don't worry about this get routine so much.

Hi,

I am amazed !!! It works. You people are really expert. Thanks.

Using the above command, I was able to remove quotes within the field. But I can see that there are some newline/breakline characters within the field, which makes a single line breaks into multiple line.

Is is possible to remove the these character as well?

Once again Thanks You.

Use

tr/"\n\r//d

Thanks it is working.

But I am seeing that it is removing the blank field completely, so field values are not matching up with headers. This is happening in both the cases:

(tr/"//d,$) and (tr/"\n\r//d,$ )

Previous:
"INTERNAL","Elsey Marilyn R","","CH "," ENTERPRISE"
After:
"INTERNAL","Elsey Marilyn R","CH "," ENTERPRISE"

Is there any way not to remove blank field completely?

I really appreciate your efforts.

I was confused about something. Use 1 instead of $_ inside grep.

grep { tr/"\n\r//d,1 }

You are Genius !!! It is working. Thanks.

I hope this is the last question:

What if I dont want to remove the quotes, but add another quotes to quotes, I mean escaping the quotes with quotes.. Something like this:

Previous:
"Internal","1234","HCM","John D","JOHN D","rocks" 12","seigo"

Expected:

"Internal","1234","HCM","John D","JOHN D","rocks"" 12","seigo"

Note: Quotes can occur multiple times within a field.

Then use s/"/""/g. To still handle the newlines, use both:

tr/\n\r//d,s/"/""/g,1

inside the grep {}