How to get a CSV-file from a query on Sharepoint lists with PnPPowershell

Question

I have 46 lists on my online sharepoint environment. I want a list in powershell of the last date of all the sharepoint lists. Here is a line of code which I have for every list:

write-host "MYLIST: " (get-pnplistitem -list "MYLIST" | select -Last 1)["Datum"].ToLocalTime().ToString("dd-MM-yyyy")

Executing the code for 46 list generates a table in PowerShell. Now I need that table exported to a CSV file. How can I do that?

I know the code is:

$results | export-csv -Path c:\temp\so.csv -NoTypeInformation

But I don't know how to get the table in powershell exported to csv.

Gr, P

Answer 1

Sample demo to export list of data to CSV.

$username = "lee@tenant.onmicrosoft.com"
$password = "pw"
$secstr = New-Object -TypeName System.Security.SecureString
$password.ToCharArray() | ForEach-Object {$secstr.AppendChar($_)}
$cred = new-object -typename System.Management.Automation.PSCredential -argumentlist $username, $secstr

$siteurl = "https://tenant.sharepoint.com/sites/tst" 
$ctx=Connect-PnPOnline -Url $siteurl -Credentials $cred 
$web=Get-PnPWeb
Get-PnPProperty -Clientobject $web -Property Id,Lists
$lists=$web.Lists
#Array to Hold Result - PSObjects
$ListsCollection = @()
foreach($list in $lists){ 
if($list.Hidden -eq $false){
$item=(get-pnplistitem -list $list.Title | select -Last 1)
     #Get All List items where Status is "In Progress"

     $ExportItem = New-Object PSObject
     $ExportItem | Add-Member -MemberType NoteProperty -name "ID" -value $item.FieldValues["ID"]
     $ExportItem | Add-Member -MemberType NoteProperty -name "Created" -value $item.FieldValues["Created"]   
     #Add the object with property to an Array
     $ListsCollection += $ExportItem
}        
}
#Export the result Array to CSV file
$ListsCollection | Export-CSV "C:\Lee\Lists.csv" -NoTypeInformation