Powershell - Can't Collect and Compile XML attributes into an Excel File

Question

I'm aiming to accomplish the following with a PowerShell script. Most sub folders in "C:\Example-Path\src" contain a single .csproj file that includes a "Property Include" and "Version" attribute. I want to gather these attributes from every instance of a .csproj file across all sub folder and compile them into a spreadsheet, 1 column for each attribute. I've revised my work quite a bit to where I'm not getting errors anymore, however after the program runs the final excel sheets turns up empty and I can't seem to figure out why. My code is below, any input is greatly appreciated!

$Directory = dir C:\Example-Path\src -Directory
foreach ($d in $Directory) {
    Write-Host "Working on directory $($d.FullName)..."
    Get-ChildItem -Path "$($d.fullname)\*" -File -Recurse -filter  '.csproj' |
        ForEach-Object {
           [xml]$file = get-content .\$_
           $xmlProperties = $file.SelectNodes("/Project/ItemGroup/PackageReference")
           $xmlProperties | Select-Object -Property Include, Version 
      } | Export-Excel -Path C:\Temp\ExamplExcel.xlsx -AutoSize -AutoFilter
}

Here is an example of the .csproj file I'm attempting to read from:

<Project Sdk="Microsoft.NET.Sdk">
  <PropertyGroup>
    <TargetFramework>netstandard2.0</TargetFramework>
    <GeneratePackageOnBuild>True</GeneratePackageOnBuild>
  </PropertyGroup>
  <PropertyGroup>
    <GenerateAssemblyInfo>false</GenerateAssemblyInfo>
  </PropertyGroup>
  <ItemGroup>
    <PackageReference Include="LazyCache.AspNetCore" Version="2.1.3" />
  </ItemGroup>
  <ItemGroup>
    <ProjectReference Include="..\Caching.Core\Caching.Core.csproj" />
 <ProjectReference Include="..\Caching.SharedKernel\ Caching.SharedKernel.csproj" />
  </ItemGroup>
</Project>

Is `[xml]$file` getting populated? I would replace `.\$_` for `$_.FullName` — Santiago Squarzon, Feb 01 '22 at 23:16
When I wasn't recursing through all the folders it would populate from the single .csproj file. But through my attempts to recurse through all the folders, I"m hitting issues. I replaced .\$_ for $_.FullName and the excel file was still empty. — sirgroot, Feb 02 '22 at 00:17

Santiago Squarzon · Answer 1 · 2022-02-02T00:50:25.143

0

Give this a try, hopefully it fixes your issue. One thing to note is that you're using Export-Excel within an inner loop and it's opening, replacing the content and closing the Excel file on each iteration (as you will see, I have moved that line as the step of the script), if you were using -Append it would at least not replace the contents of the .xlsx however it would be quite slow.

$Directory = Get-ChildItem C:\Example-Path\src -Directory
$ErrorFiles = [System.Collections.Generic.List[string]]::new()

$result = foreach ($d in $Directory) {
    Write-Host "Working on directory $($d.FullName)..."
    $files = Get-ChildItem -Path $d.FullName -File -Recurse -Filter *.csproj
    foreach($file in $files) {
        try {
            $xml = [xml](Get-Content $file.FullName -Raw)
            $xml.SelectNodes("/Project/ItemGroup/PackageReference") |
            Select-Object -Property Include, Version
        }
        catch {
            Write-Warning $_.Exception.Message
            $ErrorFiles.Add($file.FullName)
        }
    }
}

$result | Export-Excel -Path C:\Temp\ExamplExcel.xlsx -AutoSize -AutoFilter

edited Feb 02 '22 at 00:50

answered Feb 02 '22 at 00:26

Santiago Squarzon

41,465
5
14
37

Oh! You're so right about the Export-Excel being in the inner loop. Would have noticed that really quick once I had data showing up. I gave your solution a shot and am now troubleshooting this error for each sub folder that is attempted. | Cannot convert value "Example.csproj" to type "System.Xml.XmlDocument". Error: "The specified node cannot be inserted as the valid child of this node, because the specified node is the wrong type.". | I imagine this has to due with the .csproj file type being finicky? – sirgroot Feb 02 '22 at 00:44
@sirgroot correct, if the xml is not a valid xml, `XmlDocument` (`[xml]`) will not be able to parse it. – Santiago Squarzon Feb 02 '22 at 00:47
Hm, interesting that it would parse it when I wasn't recursing through the folders. – sirgroot Feb 02 '22 at 00:50
@sirgroot try using my updated code, at least `$ErrorFiles` will hold the list of files which could not be parsed – Santiago Squarzon Feb 02 '22 at 00:51
I can't seem to follow where or why recursing makes this not succeed. $ErrorFiles doesn't seem to be populated after running the script. Looked into other methods to read attributes from .csproj files, but nothing looks very plausible at the moment. – sirgroot Feb 02 '22 at 01:45
@sirgroot could you update your question showing an example of how your `.csproj` files look like? – Santiago Squarzon Feb 02 '22 at 01:48
Great idea, I updated the topic with an example .csproj file. – sirgroot Feb 02 '22 at 19:16
@sirgroot the xml is perfectly valid, honestly no idea what could be wrong. I don't see anything wrong with my code either – Santiago Squarzon Feb 02 '22 at 21:19
Hm, I added the following at the start as well as various points in the try block and this added Write-Host is never outputted to the console. Write-Host "Acessing nodes from $($file.FullName)..." Since that's not being printed maybe the try block isn't even running. However the first Write-Host that mentions directory names has been outputting and shows that all the correct directories are being looked at. – sirgroot Feb 03 '22 at 00:19
I added Write Host "Hello World" at the beginning of the foreach, before the try block and that does not print out either. – sirgroot Feb 03 '22 at 00:38

score 0 · Answer 2 · answered Feb 03 '22 at 01:53

Santiago essentially solved it! I'm not sure why but changing his $file/$files variable to $folder/$folders was the magic trick! With the main issue out of the way, now I'm unsure how to add an excel column for the name of the .csproj file so I know where the version data is coming from.

$Directory = Get-ChildItem C:\Example-Path\src -Directory
$ErrorFiles = [System.Collections.Generic.List[string]]::new()

$result = foreach ($d in $Directory) {
    Write-Host "Working on directory $($d.FullName)..."
    $folders = Get-ChildItem -Path $d.FullName -File -Recurse -Filter *.csproj
    foreach($folder in $folders) {
        try {
            $xml = [xml](Get-Content $file.FullName -Raw)
            $xml.SelectNodes("/Project/ItemGroup/PackageReference") |
            Select-Object -Property Include, Version
        }
        catch {
            Write-Warning $_.Exception.Message
            $ErrorFiles.Add($file.FullName)
        }
    }
}

$result | Export-Excel -Path C:\Temp\ExamplExcel.xlsx -AutoSize -AutoFilter

This is opening and closing the same file over and over again, `$file` has not been defined before on this code hence it only works because the variable has been defined before. If you restart your PowerShell session you will see what I mean. — Santiago Squarzon, Feb 03 '22 at 03:02

Powershell - Can't Collect and Compile XML attributes into an Excel File

2 Answers2