none
How to Convert LDIF to CSV Using PowerShell

    Question

  • I have a ldif file exported from a LDAP server, the format is as:

    UID: id1

    Firstname: Lyle1

    Lastname: Thomas1

    Email: test1@test.com

    UID: id2

    Firstname: Lyle2

    Lastname: Thomas2

    Email: test2@test.com

    etc...

    The file contains many records, each record has 4 attributes seperated by a blank line.

    How can I convert the ldif to a csv file using PowerShell? I would like to remove the attribute title from each attribute and only keep the actual value in the csv file.

    I am still learning PowerShell, I think I can read the file to an array, but not sure how to handle the reading and processing after that.

    Thanks very much for your input on this!

    Wednesday, January 23, 2013 6:08 AM

Answers

  • Having done a quick search and not found any specific functions to achieve this, you could do something like this:

    $obj = $null

    $objlist = @() Get-Content "C:\MyLdif.ldif"|Foreach{ $parts = $_.Split(":") switch($parts[0].Trim()) { "UID" { if($obj -ne $null) { $objlist+=$obj } $obj=""|select UID,Firstname,Lastname,Email $obj.UID=$parts[1].Trim() } "" { # Do nothing if it's blank } default { $obj."$($parts[0].Trim())"=$parts[1].Trim() } } }

    if ($obj -ne $null)

    {
    $objlist+=$obj

    }

    $objlist|Export-Csv C:\MyCsv.csv

    Thanks, Chris.
    • Edited by Dwarfsoft Wednesday, January 23, 2013 6:23 AM Completing answer
    • Proposed as answer by Dwarfsoft Wednesday, January 23, 2013 6:24 AM
    • Marked as answer by Flowery Wednesday, January 23, 2013 6:53 AM
    • Unmarked as answer by Flowery Wednesday, January 23, 2013 6:53 AM
    • Marked as answer by Flowery Wednesday, January 23, 2013 6:55 AM
    Wednesday, January 23, 2013 6:21 AM
  • (Get-Content t.ldif -Delimiter UID) -replace "UID" | Foreach {
     $hash = $_ -replace "^:","UID=" -replace ":","=" | ConvertFrom-StringData
     New-Object PSObject -Prop $hash
    } | Export-CSV result.csv -NoType



    • Proposed as answer by Dwarfsoft Wednesday, January 23, 2013 6:39 AM
    • Edited by Kazun Wednesday, January 23, 2013 6:45 AM
    • Marked as answer by Flowery Wednesday, January 23, 2013 6:55 AM
    Wednesday, January 23, 2013 6:37 AM

All replies

  • Having done a quick search and not found any specific functions to achieve this, you could do something like this:

    $obj = $null

    $objlist = @() Get-Content "C:\MyLdif.ldif"|Foreach{ $parts = $_.Split(":") switch($parts[0].Trim()) { "UID" { if($obj -ne $null) { $objlist+=$obj } $obj=""|select UID,Firstname,Lastname,Email $obj.UID=$parts[1].Trim() } "" { # Do nothing if it's blank } default { $obj."$($parts[0].Trim())"=$parts[1].Trim() } } }

    if ($obj -ne $null)

    {
    $objlist+=$obj

    }

    $objlist|Export-Csv C:\MyCsv.csv

    Thanks, Chris.
    • Edited by Dwarfsoft Wednesday, January 23, 2013 6:23 AM Completing answer
    • Proposed as answer by Dwarfsoft Wednesday, January 23, 2013 6:24 AM
    • Marked as answer by Flowery Wednesday, January 23, 2013 6:53 AM
    • Unmarked as answer by Flowery Wednesday, January 23, 2013 6:53 AM
    • Marked as answer by Flowery Wednesday, January 23, 2013 6:55 AM
    Wednesday, January 23, 2013 6:21 AM
  • (Get-Content t.ldif -Delimiter UID) -replace "UID" | Foreach {
     $hash = $_ -replace "^:","UID=" -replace ":","=" | ConvertFrom-StringData
     New-Object PSObject -Prop $hash
    } | Export-CSV result.csv -NoType



    • Proposed as answer by Dwarfsoft Wednesday, January 23, 2013 6:39 AM
    • Edited by Kazun Wednesday, January 23, 2013 6:45 AM
    • Marked as answer by Flowery Wednesday, January 23, 2013 6:55 AM
    Wednesday, January 23, 2013 6:37 AM
  • Thanks so much Chris and Kazun for your quick reply.

    I tried both, get some errors on both commands(may have something to do with my PowerShell version v2.0 or configuration), but also get the csv file in the proper format I need.

    Now I got to understand what each line means and start learning PowerShell.

    All the best!

    Wednesday, January 23, 2013 6:53 AM
  • Alternately, you could extract from the LDAP server directly into .csv format using CSVDE.exe instead of LDIFDE.exe.

    Al Dunbar -- remember to 'mark or propose as answer' or 'vote as helpful' as appropriate.

    Wednesday, January 23, 2013 3:42 PM
  • Thanks Dwarsoft. I could fix your solution for a greater example. 

    Regards!

    Tuesday, November 04, 2014 5:08 PM
  • Works fine for me! Thx!

    But if you have a multi-value attribute like

    UID: id1 Firstname: Lyle1 Lastname: Thomas1 Email: test1@test.com UID: id2 Firstname: Lyle2 Lastname: Thomas2 Email: test2@test.com Email: test3@test.com etc...

    I would like to join those two email and become:

    "UID";"FristName";"LastName";"Email"
    "id2";"Lyle2";Thomas2";"test2@test.com;test3@test.com"

    Thanx for your input!

    • Proposed as answer by xbstoller Wednesday, April 15, 2015 8:50 PM
    • Unproposed as answer by xbstoller Wednesday, April 15, 2015 8:51 PM
    Thursday, March 19, 2015 2:25 PM
  • We also have a need to parse varying length multi-valued fields

    We have a need to determine our [publicDelegatesBL] and [publicDelegates]

    Many thousand's of are users have +5 or more delegates in the fields delimited by a semi-colon

    I tried using csvde and ms-access  to attempt to parse out the multi-valued properties with a query and while it worked
    the ms-access queried field string to <256 characters which only is enough to parse the 1st (2) Delegates<o:p></o:p>

     What we need is a pwrshell script to read in a multi-valued  LDIFDE FILE and create a CSVDE output creating a column for each "new" value

     Id imagine the 1st pass thru the file would determine  the max # of [publicDelegatesBL] and [publicDelegates]in the file to format the CSVDE output file to add the headers and an array type table could be used for the "new" column names. (?)

    thanks for your time ,

    Brian


    Wednesday, April 15, 2015 8:58 PM