Add headlines and split text in CSV File to more columns?

This topic contains 0 replies, has 1 voice, and was last updated by  Forums Archives 5 years, 11 months ago.

  • Author
    Posts
  • #6261

    by sabeltiger81 at 2013-01-27 04:46:39

    I got this great script, but one thing I really have a hard time to figure out is, how to add headlines to the csv file and split the text between column A and B. I would like that it in cell A1 has a headline called Groups, and in B1 it says Servers.

    The idea is that this CSV file is to be sent to my boss, so when he opens the file, it is already splitted in column A and B. The real kicker would be of someone new how to switch the data so Server would be in Collumn A and Groups in Column B. that would be great. thanks in advance.

    Import-Module ActiveDirectory
    $searchOU = 'OU=Servers,DC=Boyle,DC=local'
    Get-ADComputer -filter * -SearchBase $searchOU | Foreach-Object {
    $server=$_.Name
    if ( Test-Connection $_.Name -q ) {
    Write-Host "Processing $server" -fore green
    ([ADSI]"WinNT://$server/Administrators").psbase.invoke('Members') | ForEach-Object {
    $member=$_.GetType().InvokeMember('Name', 'GetProperty', $null, $_, $null)
    New-Object PSObject -Property @{Server=$server; Account=$member}
    }
    } else {
    New-Object PSObject -property @{Server=$server; Account="NOT_AVAILABLE"}
    }
    }

    by ps_gregg at 2013-01-27 08:52:41

    Hi sabeltiger81,

    That's a nice little script – the boss should will be proud. Let's add an array at the beginning to capture your results, then you can select the properties in the right order (and rename them), and finally put them into a CSV

    Import-Module ActiveDirectory
    $list =@()
    $searchOU = 'OU=Servers,DC=Boyle,DC=local'
    Get-ADComputer -filter * -SearchBase $searchOU | Foreach-Object {
    $server=$_.Name
    if ( Test-Connection $_.Name -q ) {
    Write-Host "Processing $server" -fore green
    ([ADSI]"WinNT://$server/Administrators").psbase.invoke('Members') | ForEach-Object {
    $member=$_.GetType().InvokeMember('Name', 'GetProperty', $null, $_, $null)
    $list += New-Object PSObject -Property @{Server=$server; Account=$member}
    }
    } else {
    $list += New-Object PSObject -property @{Server=$server; Account="NOT_AVAILABLE"}
    }
    }
    $list | Select-Object -Property @{label="Server";expression={$_.Server}}, @{label="Groups";expression={$_.Account}} | Export-Csv -Path report.csv -NoTypeInformation

    So, we capture your new-objects into an array ($list), then we pipe that array into the Select-Object command and create a couple of hash tables to relabel your data. Finally we pipe the selected objects to the Export-Csv command and use the -NoTypeInformation parameter to get rid of that ugly TypeInfo line that can appear on the first line of a CSV.

    Hope that helps and good work on the script

    Cheers
    -Gregg

    by sabeltiger81 at 2013-01-27 13:19:40

    The report.txt shouldn't that be report.csv? just asking because you do use the Export-csv cmdlet.

    by ps_gregg at 2013-01-27 13:34:42

    Opps.. you are correct. My typo/to fast typing.. Thanks for keeping me honest.

    I corrected it..

    Cheers
    -Gregg

    by sabeltiger81 at 2013-01-28 01:02:34

    You're welcome. I have just tried your solution. The strange thing is that the CSV file doesn't get created. I have tried to replace -path with an actual C:\foldername\report.csv but without luck.

    by sabeltiger81 at 2013-01-28 02:30:00

    I found the answer to, why the script didn't created the csv file. You type to fast 😉

    The last lines should be like this:

    $list += New-Object PSObject -property @{Server=$server; Account="NOT_AVAILABLE"}
    }
    $list | Select-Object -Property @{label="Server";expression={$_.Server}}, @{label="Groups";expression={$_.Account}} | Export-Csv -Path report.csv -NoTypeInformation
    }

You must be logged in to reply to this topic.