Forum Discussion

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 f...

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 😉

Fred_Elmendorf

Brass Contributor

Jan 09, 2023

I sincerely appreciate your help. These replies work, and produce the output I described, but as I run them in my environment, I realize that I haven't quite described the problem accurately. Here is a more comprehensive example of our folder structure.

Fruit\Trees\Apples\Macintosh\Red\somefile.pqd
Fruit\Trees\Apples\Winesap\Red\anotherfile.pqd
Fruit\Trees\Apples\Fuji\Red\yetanother.pqd

The parent folder for each file is always the same, Red in this example. But the next folder up is unique; Macintosh, Winesap, Fuji in this example. The unique folder name could be identified either by position, always the second level above the filename, or by string comparison, where it is always the level above Red. Using the position is probably the best approach since it is possible to have Red appear somewhere else in the folder structure, even though it does not presently occur in our scenario.

Using this example, the output I'm looking for would be:

Macintosh,somefile.pqd,date,time
Winesap,anotherfile.pqd,date,time
Fuji,yetanother.pqd,date,time

Apologies for these iterations because of my poor description of the problem, but if you are willing to give it one more round, I think I'll be good to go. I'm looking forward to learning from your code so that I can become more proficient in PS.

Also, I see where I can select "Mark as best response", but I can't find a way to mark it as a solution?

Harm_Veenstra

MVP

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:

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 😉

Fred_Elmendorf
Brass Contributor
Jan 10, 2023
Thank you so much! You've given me a lot to work with and I think I can get what I need now.
- Harm_Veenstra
  MVP
  Jan 10, 2023
  No problem