Csvtoldap.pl

From Kolab Wiki

This will convert a csv file into a kolab-compatible ldif file. It only has the most basic entries, please feel free to edit this and add more!

The original CSV-File looks like this (just one entry):

  Bastian;Preindl;secretpassword;bastian@preindl.net;bastian@preindl.net;kolab.preindl.net;Administration;Bundesschuelerheim;Juchgasse 27;1030;Vienna;Austria

The extendet CSVtoldap.pl (I called it csvtoldif.pl) follows:

 #!/kolab/bin/perl
 # Filename: csvtoldif.pl
 # Description: makes ldif files from csv for bulk user adding.
 # This is just a basic set of ldap data, feel free to add more
 # according to your needs.
 #
 # Usage: csvtoldif csvfile.csv
 #
 # Added password-add support for the CVS->LDIF Script
 # Bastian Preindl, bastian@preindl.net, 2005-07-31
 # You propably need to fetch Digest::SHA1 and MIME::Base64 from CPAN !
 #
 # Source: http://www.openldap.org/faq/data/cache/347.html
 #
 # OpenLDAP supports RFC 2307 passwords, 
 # including the {SHA}, {SSHA} and other schemes. 
 # Such passwords may be used as userPassword values 
 # and/or rootpw value.
 
 use Digest::SHA1;
 use MIME::Base64;
 
 open (LDIF,"+>./fromcsv.ldif");
 $csv = @ARGV[0];
 open (CSV,"$csv");
 while ($line = <CSV>)
 {
      chomp $line;
 
 # Remember to order your CSV the same as this!
 
 ($givenName, $sn, $userPassword, $email, $uid, $kolabHomeServer, $ou, $o, $street,  $postalCode, $l, $c) = split (";", $line);
 
 # Hashing the userPassword using SHA1 and MIME encode_base64
 
      $ctx = Digest::SHA1->new;
      $ctx->add($userPassword);
      $hashedPasswd = '{SHA}' . encode_base64($ctx->digest,);
 # Debug
      print 'userPassword: ' .  $hashedPasswd . "\n";
 # /Debug
 
 # Please edit here to change your base dn (If someone can change it to get this 
 # value from something else, please do! I cant make it work like that!
 
      printf LDIF ("%s","dn: cn=$givenName $sn,dc=mydomain,dc=com\n");
      printf LDIF ("%s","kolabHomeServer: $kolabHomeServer\n");
      printf LDIF ("%s","objectclass: top\n");
      printf LDIF ("%s","objectclass: inetOrgPerson\n");
      printf LDIF ("%s","objectclass: kolabInetOrgPerson\n");
      printf LDIF ("%s","sn: $sn\n");
      printf LDIF ("%s","cn: $givenName $sn\n");
      printf LDIF ("%s","givenName: $givenName\n");
      printf LDIF ("%s","userPassword: $hashedPasswd\n");
      printf LDIF ("%s","mail: $email\n");
      printf LDIF ("%s","uid: $uid\n");
      printf LDIF ("%s","ou: $ou\n");
      printf LDIF ("%s","o: $o\n");
      printf LDIF ("%s","street: $street\n");
      printf LDIF ("%s","postalCode: $postalCode\n");
      printf LDIF ("%s","l: $l\n");
      printf LDIF ("%s","c: $c\n");
      printf LDIF ("%s","kolabInvitationPolicy: ACT_MANUAL\n\n");
  }
 close CSV;
 exit (0);

After you have converted the CSV into a LDIF you will propably want to get it into the LDAP-Directory. Read ldapimport to bring it into the directory (using ldapadd). I used phpldapadmin and it worked well (more on that later).

A converted CSV-entry should look like this:

 dn: cn=Bastian Preindl,dc=preindl,dc=net
 kolabHomeServer: kolab.preindl.net
 objectclass: top
 objectclass: inetOrgPerson
 objectclass: kolabInetOrgPerson
 sn: Preindl
 cn: Bastian Preindl
 givenName: Bastian
 userPassword: {SHA}lipzUZw3uS9y30MXQb+zTDaffsM=
 mail: bastian@preindl.net
 uid: bastian@preindl.net
 ou: Administration
 o: Bundesschuelerheim
 street: Juchgasse 27
 postalCode: 1030
 l: Wien
 c: Austria
 kolabInvitationPolicy: ACT_MANUAL

Be careful with "ö","ü","ä","ß" and all other non-standard ASCII-Characters in the cn,dn,o,ou and so on values. LDAP does NOT support special characters in the key-values and won't insert the user into the directory ! (not important for English-speaking countries but for everyone else !).