How to export powershell output to excel

Question

I have found the custom lists in a particular site and I want to print the names to Excel. How do I do it?

Answer 1

Use the following Command:

$spWeb = Get-SPWeb SiteUrl
$spWeb.Lists | select Title | Export-CSV c:\Lists.csv

Answer 2

You can pipe the output from your command to an .csv file:

FileToExecute.ps1 | Out-File C:\output.csv

Make sure you in your script file uses Write-Outputand not Write-Host when printing your output.

Answer 3

You can export any output of the powershell to a CSV. This is a built in function.

Export-CSV

Answer 4

Try if this works:

Install-Module importexcel

If it works, then after that you can use export-excel command.

Answer 5

You wanna use the Export-Csv parameter. I also found this easy example.

SharePoint list to .csv file

Export-Csv

$MyWeb = WebUrl 
$MyList = MyWeb.Lists["ListName"] 
$MyItems = $MyList.Items
$MyItems | Select Fields.. | Export-Csv -path C:\example\excel.csv
$MyWeb.Dispose()

Answer 6

Using the Export-CSV as usual way we can export the SharePoint list items to a csv file, and using the below code we can convert the particular csv file to excel file:

### Set input and output path
$inputCSV = "C:\somefolder\input.csv"
$outputXLSX = "C:\somefolder\output.xlsx"

### Create a new Excel Workbook with one empty sheet
$excel = New-Object -ComObject excel.application 
$workbook = $excel.Workbooks.Add(1)
$worksheet = $workbook.worksheets.Item(1)

### Build the QueryTables.Add command
### QueryTables does the same as when clicking "Data » From Text" in Excel
$TxtConnector = ("TEXT;" + $inputCSV)
$Connector = $worksheet.QueryTables.add($TxtConnector,$worksheet.Range("A1"))
$query = $worksheet.QueryTables.item($Connector.name)

### Set the delimiter (, or ;) according to your regional settings
$query.TextFileOtherDelimiter = $Excel.Application.International(5)

### Set the format to delimited and text for every column
### A trick to create an array of 2s is used with the preceding comma
$query.TextFileParseType  = 1
$query.TextFileColumnDataTypes = ,2 * $worksheet.Cells.Columns.Count
$query.AdjustColumnWidth = 1

### Execute & delete the import query
$query.Refresh()
$query.Delete()

### Save & close the Workbook as XLSX. Change the output extension for Excel 2003
$Workbook.SaveAs($outputXLSX,51)
$excel.Quit()

Reference URL:

How to export a CSV to Excel using Powershell

Note:

• There is no direct inbuilt function to export in excel using PowerShell as like Export-CSV, so first we need to export to csv file, then we have to convert that file to excel.