Share via

how to Write-Host command to write into a file (notepad or excel) rather than in the screen

Question

Tuesday, April 26, 2016 9:28 PM

Hi,

I was running below command and I want the results to be displayed in excel or text file rather than the screen. I am new to power shell.

[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint")

$farm=[Microsoft.SharePoint.Administration.SPFarm]::Local
$farmWebServices=$farm.Services | where -FilterScript {$_.GetType() -eq[Microsoft.SharePoint.Administration.SPWebService]}
foreach ($farmWebServicein$farmWebServices) {
  foreach ($webApplicationin$farmWebService.WebApplications) {
    foreach ($sitein$webApplication.Sites)
    {
      $web=$site.OpenWeb()
      $scas=""foreach ($siteAdminin$web.SiteAdministrators)
      {
        $scas=$siteAdmin.LoginName +"; "+$scas
      }
      Write-Host "ID:"$site.ID " - URL: "$site.Url " - Owners: "$scas$site.Dispose()
    }
  }
}Thanks in advance for your helpRithu

Rithu

All replies (10)

Thursday, April 28, 2016 5:25 PM âś…Answered

You might be in PowerShell 2.0, introduce a object collection and export the collection

[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint")
$farm = [Microsoft.SharePoint.Administration.SPFarm]::Local
$farmWebServices = $farm.Services | where -FilterScript {$_.GetType() -eq [Microsoft.SharePoint.Administration.SPWebService]}
$objectCollection=@()  
foreach ($farmWebService in $farmWebServices) {
  foreach ($webApplication in $farmWebService.WebApplications) {
    foreach ($site in $webApplication.Sites)
    {
      $web = $site.OpenWeb()
      $scas = ""
      foreach ($siteAdmin in $web.SiteAdministrators)
      {
        $scas = $siteAdmin.LoginName + "; " + $scas
      }
      $object = New-Object PSObject 
        $object | Add-Member -type NoteProperty -Name ID -Value $site.ID
        $object | Add-Member -type NoteProperty -Name URL -Value $site.Url  
        $object | Add-Member -type NoteProperty -Name OWNERS -Value $scas 
        $objectCollection += $object
        #$object | Export-Csv c:\test.csv -NoTypeInformation -append  
      #Write-output "ID:" $site.ID " - URL: " $site.Url " - Owners: " $scas| Out-File "c:\test.csv" -Append
      $site.Dispose()
    }
  }
}
$objectCollection | Export-Csv c:\test.csv -NoTypeInformation

Rajesh
rjesh.com| @rjesh
You don't need to buy me a beer, if helpful just smile, vote, and mark it as answer.

Tuesday, April 26, 2016 9:50 PM

Use Write-Output piped with Out-File instead of Write-Host

Write-Output "My file contents goes here" | Out-File "c:\test.txt" -Append

Rajesh
rjesh.com| @rjesh
You don't need to buy me a beer, if helpful just smile, vote, and mark it as answer.

Wednesday, April 27, 2016 6:13 AM

add

>c:\text\1.txt

at the end of your power-shell command... where text is the folder name c:\ is local drive letter

the output will store in 1.txt file or you can generate to excel file also 1.xlsx

eg:

*Ps c:\users\admin>*powershell.ps1 >c:\text\1.txt

Karim... Please remember to mark your question as answered, if this solves your problem.

Thursday, April 28, 2016 4:20 PM

Hi Rajesh,

Thankyou very much for your help, when I change the write host to write-output it worked, But the format I was getting in excel is like below

ID:

Guid                                                                           
                                                                           
483632-xxxx-4719-8cd0-0b440b97ebb0                                           
 - URL:
http://real.test.com

- Owners:
abc\test1; abc\test2;

ID:

Guid                                                                           
                                                                           
a1k32160-2e12-4644-a683-xxxxxxxx                                          
 - URL:
http://real.test.com\sites\test1
 - Owners:
abc\test3;

how can I make the results in the excel format, likes rows and columns

ex:

GUID                                                                         URL                                          Owner

483632-xxxx-4719-8cd0-0b440b97ebb0            http://real.test.com                 abc\test1; abc\test2;

Below is the script I am using

[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint")
$farm = [Microsoft.SharePoint.Administration.SPFarm]::Local
$farmWebServices = $farm.Services | where -FilterScript {$_.GetType() -eq [Microsoft.SharePoint.Administration.SPWebService]}
foreach ($farmWebService in $farmWebServices) {
  foreach ($webApplication in $farmWebService.WebApplications) {
    foreach ($site in $webApplication.Sites)
    {
      $web = $site.OpenWeb()
      $scas = ""
      foreach ($siteAdmin in $web.SiteAdministrators)
      {
        $scas = $siteAdmin.LoginName + "; " + $scas
      }
      Write-output "ID:" $site.ID " - URL: " $site.Url " - Owners: " $scas| Out-File "c:\test.csv" -Append
      $site.Dispose()
    }
  }
}

Thankyou

Rithu

Thursday, April 28, 2016 4:54 PM | 1 vote

Try Export-Csv cmdlet if you want to write to csv. 

[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint")
$farm = [Microsoft.SharePoint.Administration.SPFarm]::Local
$farmWebServices = $farm.Services | where -FilterScript {$_.GetType() -eq [Microsoft.SharePoint.Administration.SPWebService]}
foreach ($farmWebService in $farmWebServices) {
  foreach ($webApplication in $farmWebService.WebApplications) {
    foreach ($site in $webApplication.Sites)
    {
      $web = $site.OpenWeb()
      $scas = ""
      foreach ($siteAdmin in $web.SiteAdministrators)
      {
        $scas = $siteAdmin.LoginName + "; " + $scas
      }
      $object = New-Object PSObject 
        $object | Add-Member -type NoteProperty -Name ID -Value $site.ID
        $object | Add-Member -type NoteProperty -Name URL -Value $site.Url  
        $object | Add-Member -type NoteProperty -Name OWNERS -Value $scas 
        $object | Export-Csv c:\test.csv -NoTypeInformation -append  
      #Write-output "ID:" $site.ID " - URL: " $site.Url " - Owners: " $scas| Out-File "c:\test.csv" -Append
      $site.Dispose()
    }
  }
}

Rajesh
rjesh.com| @rjesh
You don't need to buy me a beer, if helpful just smile, vote, and mark it as answer.

Thursday, April 28, 2016 5:12 PM

I get bellow error when I use your command, If i remove -append only one item is recorded in the report

Export-Csv : A parameter cannot be found that matches parameter name 'append'.
At line:15 char:68
+         $object | Export-Csv c:\test.csv -NoTypeInformation -append <<<<
    + CategoryInfo          : InvalidArgument: (:) [Export-Csv], ParameterBind
   ingException
    + FullyQualifiedErrorId : NamedParameterNotFound,Microsoft.PowerShell.Comm
   ands.ExportCsvCommand

Export-Csv : A parameter cannot be found that matches parameter name 'append'.
At line:15 char:68
+         $object | Export-Csv c:\test.csv -NoTypeInformation -append <<<<
    + CategoryInfo          : InvalidArgument: (:) [Export-Csv], ParameterBind
   ingException
    + FullyQualifiedErrorId : NamedParameterNotFound,Microsoft.PowerShell.Comm
   ands.ExportCsvCommand

Rithu

Thursday, April 28, 2016 5:32 PM

Thankyou very much Rajesh for your time.....I didn't get any error by running the script but I don't see the test.csv file in C drive, I tried twice still same result.

Rithu

Thursday, April 28, 2016 5:36 PM

I typed it in notepad, if you are using Poershell ISE put some breakpoints and debug.

Rajesh
rjesh.com| @rjesh
You don't need to buy me a beer, if helpful just smile, vote, and mark it as answer.

Thursday, April 28, 2016 5:37 PM

Sorry Rajesh, for some reason file showed after 2 minutes. Thank you Very much for your help.

Rithu

Thursday, April 28, 2016 5:44 PM

Glad to know that.

Your web services operations are consuming time, so add some Write-Host with "Working on it..." and after export csv one more as "Export completed" for visual clue.

Rajesh
rjesh.com| @rjesh
You don't need to buy me a beer, if helpful just smile, vote, and mark it as answer.