?
Solved

Retrieving Powershell data and outputting to csv

Posted on 2016-09-07
7
Medium Priority
?
23 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
Bringing Advanced Authentication to the SMB Market

WatchGuard announces the acquisition of advanced authentication provider, Datablink, with one mission – to bring secure authentication to SMB, mid-market, and distributed enterprises with a cloud-based solution, ideal for resale via their established channel & MSSP community.

 
LVL 70

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 70

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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

A quick Powershell script I wrote to find old program installations and check versions of a specific file across the network.
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...
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…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

801 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