2

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

2
  • You can try using steps in below URL: community.spiceworks.com/how_to/…
    – SKumar
    Commented Jan 14, 2020 at 17:00
  • Script Saves numbers as text, would be nice to see one that doesn't convert integers and floats to text
    – user100847
    Commented Oct 27, 2021 at 18:27

6 Answers 6

5

Use the following Command:

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

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.

0
1

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

Export-CSV

1

Try if this works:

Install-Module importexcel

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

0

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()
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.