One scenario you may encounter is wanting to create a report containing information about our Sitecore instance and its content. In this blog, we will see a few methods of how we can create reports using Sitecore Powershell.
Exporting Data as Plain Text File Using Out-Download Command
The Out-Download command is used to prompt the Browser client to download the generated output stored in the file named using –Name parameter. We need to specify input data using –InputObject.
Following is the script that will export information about the items that refer to item with ID – {13AAD693-D140-4054-840D-4F9DABF681D3} and are created from Event Details template.
#Exporting information in plain text
[String[]] $data = Get-ItemReferrer -ID "{13AAD693-D140-4054-840D-4F9DABF681D3}" | Where-Object { $_.TemplateName -eq "Event Details" } | Select-Object -Property Name,Id, TemplateName
Out-Download -Name ReferrerInfo.txt -InputObject $data
See that $data is of type String[], and the –Name parameter of Out-Download command has a file name with an extension .txt. After running the script, I was prompted to download a file named ReferrerInfo.txt and inside it, I found Item object information as Powershell Hash tables in plain text.
Following are the downloaded file’s content:
Exporting Data as Excel File Using Out-Download Command
The exported file had raw objects in plain text which is readable only for a developer. We can instead use Out-Download command to export data as an Excel sheet in proper Excel format.
We will be using the ConvertTo-Xlsx function, which will apply the proper Excel native format. For using this function, we need to first Import it using the Import-Function command. After that, we just need to add ConvertTo-Xlsx command after a (|). Also, as this will not be a plain text, we will change the data type of $data to byte[].
#Export data as Excel sheet
Import-Function -Name ConvertTo-Xlsx
[byte[]] $data = Get-ItemReferrer -ID "{13AAD693-D140-4054-840D-4F9DABF681D3}" | Where-Object { $_.TemplateName -eq "Event Details" } | Select-Object -Property Name,Id, TemplateName | ConvertTo-Xlsx
Out-Download -Name ReferrerInfo.xlsx -InputObject $data
After running the script and downloading ReferrerInfo.xlsx file, it had the following content:
You can see how properly structured it is with respect to a plain text files.
Exporting Data as CSV File Using Export-CSV Command
This command will create a CSV Format file based on the input provided and dump the CSV information in the file specified by –Path location.
#Export data to CSV in file disk
$DataLoc = "C:\testCSV\ComponentsInUse.csv"
Get-ItemReferrer -ID "{13AAD693-D140-4054-840D-4F9DABF681D3}" | Select-Object -Property Name,Id, TemplateName | Export-Csv -Path $DataLoc
This script will create a CSV file ComponentsInUse.csv inside C:\testCSV folder.
Following are the content of this file:
When opened in Excel:
So far, we have seen how to export item information in a simple text file, Excel file using Out-Download command, and how to use Export-Csv command to export information in CSV. In the next post of this series, we will see how to import CSV and create items from it.
