Brass Contributor

Jan 06, 2023

Solved

formatting output as a .csv file

This is my first attempt at PowerShell, and I have gathered lots of helpful information from existing forum threads and documentation, but I'm still not getting what I need. I need the latest *.pqd file entry in every folder and subfolder output to a file in .csv format. So far, I get the elements I need output to the terminal, but when I redirect the output to a file, I only get the single last result. I also need to add a comma delimiter after each element.

Here's the code so far:

$NoOfDirs=Get-ChildItem $path | Where-Object {$_.PSIsContainer -eq $True}
ForEach($dir in $NoOfDirs )
{

Get-ChildItem "$path\$($dir.name)" -Recurse |
Where-Object {($_.name -Like "trend*.pqd") -and ($_.PSIsContainer -eq $False) } |
Select-Object @{l='Folder';e={$dir.Name}},Name,LastWriteTime |
Sort-Object -pro LastWriteTime -Descending |
Select -First 1 | Format-Table -AutoSize -HideTableHeaders
}

Here's the terminal output that correctly displays the folder name, the file name, the date, and the time:

Folder1 NewestFile.pqd 1/3/2023 2:53:20 PM

Folder2 NewestFile.pqd 1/5/2023 3:16:25 AM

Folder3 NewestFile.pqd 1/5/2023 3:16:28 AM

Folder4 NewestFile.pqd 11/10/2022 8:55:34 AM

The goal is to have the output formatted as follows, and placed in a file:

Folder1,NewestFile.pqd,1/3/2023,2:53:20 PM,

Folder2,NewestFile.pqd,1/5/2023,3:16:25 AM,

Folder3,NewestFile.pqd,1/5/2023,3:16:28 AM,

Folder4,NewestFile.pqd,11/10/2022,8:55:34 AM,

Windows PowerShell

Harm_Veenstra

Jan 09, 2023

Fred_Elmendorf Modified it again, script is like this now:

$path='d:\temp'
$total = ForEach ($dir in Get-ChildItem $path | Where-Object PSIsContainer -eq $True ) {
    $files = Get-ChildItem "$($path)\$($dir.name)" -Recurse | Where-Object { ($_.name -Like "trend*.pqd") -and ($_.PSIsContainer -eq $False) } 
    if ($null -ne $files) {
        foreach ($file in $files) {
            [PSCustomObject]@{
                Folder        = $file.DirectoryName.Split('\')[-3..-1] -join '\'
                Name          = $file.Name
                LastWriteDate = $file.LastWriteTime.ToString().Split(' ')[0]
                LastWriteTime = $file.LastWriteTime.ToString().Split(' ')[1]
            }
        }
    }
}

$total | Export-Csv -Path d:\temp\report.csv -NoTypeInformation -Encoding UTF8 -Delimiter ','

Output is like this:

"Folder","Name","LastWriteDate","LastWriteTime"
"D:\temp\x2","trend123.pqd","8-1-2023","22:39:18"
"D:\temp\x3","trend123456.pqd","8-1-2023","22:48:44"
"x5\x6\red","trendabcdef.pqd","9-1-2023","21:40:32"

The last is inside a deeper path than the rest. Perhaps you should tweak the .Split numbers a bit, in my case the full path of the last item is D:\Temp\x4\x5\x6\red.

There's a link beneath this post, mark as best response 😉

7 Replies

Harm_Veenstra

MVP

Jan 08, 2023

Fred_Elmendorf This should work. Replace the path variable with your own; the output is saved in d:\temp in this example but change it to your own location.

$path='d:\temp'
$total = ForEach ($dir in Get-ChildItem $path | Where-Object PSIsContainer -eq $True ) {
    $files = Get-ChildItem "$($path)\$($dir.name)" -Recurse | Where-Object { ($_.name -Like "trend*.pqd") -and ($_.PSIsContainer -eq $False) } 
    if ($null -ne $files) {
        foreach ($file in $files) {
            [PSCustomObject]@{
                Folder        = $file.DirectoryName
                Name          = $file.Name
                LastWriteDate = $file.LastWriteTime.ToString().Split(' ')[0]
                LastWriteTime = $file.LastWriteTime.ToString().Split(' ')[1]

            }
        }
    }
}

$total | Export-Csv -Path d:\temp\report.csv -NoTypeInformation -Encoding UTF8 -Delimiter ','

Output in the file looks like this:

"Folder","Name","LastWriteDate","LastWriteTime"
"D:\temp\x2","trend123.pqd","8-1-2023","22:39:18"
"D:\temp\x3","trend123456.pqd","8-1-2023","22:48:44"

Fred_Elmendorf

Brass Contributor

Jan 09, 2023

Harm_Veenstra Yes, that worked. Thank you!

The only tweak that would be helpful, is to have only the folder name included, without the path. In your output example, "x2" rather than "D:\temp\x2"

Harm_Veenstra

MVP

Jan 09, 2023

Fred_Elmendorf No problem, changed it for you:

$path='d:\temp'
$total = ForEach ($dir in Get-ChildItem $path | Where-Object PSIsContainer -eq $True ) {
    $files = Get-ChildItem "$($path)\$($dir.name)" -Recurse | Where-Object { ($_.name -Like "trend*.pqd") -and ($_.PSIsContainer -eq $False) } 
    if ($null -ne $files) {
        foreach ($file in $files) {
            [PSCustomObject]@{
                Folder        = $file.Directory.Name
                Name          = $file.Name
                LastWriteDate = $file.LastWriteTime.ToString().Split(' ')[0]
                LastWriteTime = $file.LastWriteTime.ToString().Split(' ')[1]

            }
        }
    }
}

$total | Export-Csv -Path d:\temp\report.csv -NoTypeInformation -Encoding UTF8 -Delimiter ','

Output is like this now:

"Folder","Name","LastWriteDate","LastWriteTime"
"x2","trend123.pqd","8-1-2023","22:39:18"
"x3","trend123456.pqd","8-1-2023","22:48:44"

Please mark my answer as solution to mark this post as solved

Forum Discussion

formatting output as a .csv file

7 Replies

Resources