Solved

Retrieving Powershell data and outputting to csv

Posted on 2016-09-07
7
20 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 40

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 40

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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 69

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 69

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A project that enables an administrator to perform actions within a user session context not just at the time of login but any time later on day(s) or week(s) later.
A recent project that involved parsing Tableau Desktop and Server log files to extract reusable user queries for use in other systems. I chose to use PowerShell to gather the data, and SharePoint to present it...
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the adminiā€¦

730 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