Solved

Retrieving Powershell data and outputting to csv

Posted on 2016-09-07
7
17 Views
Last Modified: 2016-09-13
Below I have a script that loops through a list of servers, remotes into them, grabs some data and writes it to csv file:

$results = @()
$serverList = Get-Content C:\Test\Servers.txt
foreach ($server in $serverList) {

    if((Test-Connection -Cn $server -BufferSize 16 -Count 1 -ea 0 -quiet))
    {
    
        $myobj = Get-ChildItem ...
	   Write-Host $myobj.Name
 
         $result =  New-object PSObject
         $result | Add-member -type Noteproperty -Name ServerName $server.ToString()
         $result | Add-member -type Noteproperty -Name MyValue -Value $myobj.Name
    
         Write-Host $result

     $results += $result
    }
}
$results | export-csv -Path c:\Test\Output.csv 

Open in new window


The above writes 2 columns to csv file, first column is the server name - displayed correctly, but second column get the value of "System.Object[]" even though above  Write-Host $myobj.Name outputs the correct value. How can i fix my code to display the correct value in a csv file?
0
Comment
Question by:YZlat
  • 3
  • 2
  • 2
7 Comments
 
LVL 39

Expert Comment

by:footech
ID: 41788428
Your $myobj = Get-ChildItem... command must have retrieved more than one object.  There's a lot more flexibility with how things can be displayed on screen vs. the restrictions of a .CSV file.  Namely, in a .CSV, every field has to be a string.  You might try changing line 13 to:
$result | Add-member -type Noteproperty -Name MyValue -Value ($myobj.Name -join ";")

Open in new window

or change line 8 so the results are more limited.

BTW, $server is already a string, so $server.ToString() is redundant.
0
 
LVL 35

Author Comment

by:YZlat
ID: 41788464
But the thing is I want more then one result returned and then I want to add them all to an array and output the array to csv
0
 
LVL 39

Expert Comment

by:footech
ID: 41788611
The sample code I provided will essentially do that, joining all the elements in the array into a single string.  If you want to export to a .CSV, that's your only option.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 68

Expert Comment

by:Qlemo
ID: 41789172
Your choice is to
either combine the Name array result into a single string, as shown by footech,
or inflate results by creating a new CSV line per Name member. This leads to say 5 rows with the same server name but different, single Name entries.
As said by footech, it is not possible to have an array in a CSV directly, you need to have a representation (enumeration with special delimiter like semi-colon, pipe, or similar).
0
 
LVL 35

Accepted Solution

by:
YZlat earned 0 total points
ID: 41789416
Solved the problem by using [string]$myobj.Name


results = @()
$serverList = Get-Content C:\Test\Servers.txt
foreach ($server in $serverList) {

    if((Test-Connection -Cn $server -BufferSize 16 -Count 1 -ea 0 -quiet))
    {
    
        $myobj = Get-ChildItem ...

        #Construct an object  
        $result =  "" | Select "ServerName","Name"
        $result.ServerName=$server.ToString()
        $result.Name= [string]$myobj.Name

        $results += $result
    }
}
$results | export-csv -Path c:\Test\Output.csv 

Open in new window

0
 
LVL 68

Expert Comment

by:Qlemo
ID: 41789668
That just joins with the "default string separator" stored in $OFS, which is a blank by default. It's the same as you see on screen, but should not use in a CSV - footech's recommendation is better. Being nit-picky " add them all to an array and output the array to csv" is not what you have done now.
0
 
LVL 35

Author Closing Comment

by:YZlat
ID: 41795706
Solved myself
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Why would I want to create a function for tracking messages? I am glad you asked. As with most monotonous/routine tasks, human error tends to creep in after doing the same task over and over again. By creating a function, you load the function once…
Active Directory replication delay is the cause to many problems.  Here is a super easy script to force Active Directory replication to all sites with by using an elevated PowerShell command prompt, and a tool to verify your changes.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now