How do I convert repeated xml nodes into a comma delimited string using powershell

Question

I have some 13000 log files that are formatted in XML format and I need to convert all of them into a spreadsheet\csv file.

As you will see I'm not programmer but I've tried.
I have written a powershell script to get the first nodes out and create a comma delimited string but I am stuck with getting the last node which can contain anything from no entries to dozens.

example of the xml file:

<?xml version="1.0" encoding="utf-8"?>
<MigrationUserStatus>
  <User>username@domain.com</User>
  <StoreList>
    <EmailMigrationStatus>
      <MigrationStatus value="Success" />
      <FolderList>
        <TotalCount value="6" />
        <SuccessCount value="3" />
        <FailCount value="3" />
        <FailedMessages>
          <ErrorMessage>GDSTATUS_BAD_REQUEST:Permanent failure: BadAttachment</ErrorMessage>
          <SentTime>1601-01-01T00:00:00.000Z</SentTime>
          <ReceiveTime>1601-01-01T00:00:00.000Z</ReceiveTime>
        </FailedMessages>
        <FailedMessages>
          <ErrorMessage>GDSTATUS_BAD_REQUEST:Permanent failure: BadAttachment</ErrorMessage>
          <SentTime>1601-01-01T00:00:00.000Z</SentTime>
          <ReceiveTime>1601-01-01T00:00:00.000Z</ReceiveTime>
        </FailedMessages>
        <FailedMessages>
          <MessageSubject>Hey</MessageSubject>
          <ErrorMessage>GDSTATUS_BAD_REQUEST:Permanent failure: BadAttachment</ErrorMessage>
          <SentTime>2013-01-07T02:51:17.000Z</SentTime>
          <ReceiveTime>2013-01-07T02:51:17.000Z</ReceiveTime>
          <MessageSize value="2881" />
        </FailedMessages>
        <StartTime>2013-01-07T01:52:46.000Z</StartTime>
        <EndTime>2013-01-07T04:41:59.000Z</EndTime>
      </FolderList>
      <StartTime>2013-01-07T01:52:43.000Z</StartTime>
      <EndTime>2013-01-07T04:41:59.000Z</EndTime>
    </EmailMigrationStatus>
    <StartTime>2013-01-07T01:52:43.000Z</StartTime>
    <EndTime>2013-01-07T04:41:59.000Z</EndTime>
  </StoreList>
</MigrationUserStatus>

With this code I can easily get the the first parts of the csv line created:

$folder = "C:\temp"
$outfile = = [IO.File]::OpenWrite("alluserslogs.csv")
$csv = "User,Total Emails, Successful emails,Failed emails,Failures`r`n"

dir Status-*.log | foreach ( $_) {
[xml]$Status = Get-Content $_
$csvpt1 +=$Status.MigrationUserStatus.User + "," + $Status.MigrationUserStatus.StoreList.EmailMigrationStatus.FolderList.TotalCount.value + "," + $Status.MigrationUserStatus.StoreList.EmailMigrationStatus.FolderList.SuccessCount.value + "," + $Status.MigrationUserStatus.StoreList.EmailMigrationStatus.FolderList.FailCount.value

The next bit is where I'm coming unstuck. I want to read each FailedMessages node and build it to another comma delimited string

foreach ($FMessage in $Status.MigrationUserStatus.StoreList.EmailMigrationStatus.FolderList.FailedMessages) {
$csvpt2 +=$FMessage + ","
}

Desired output:

GDSTATUS_BAD_REQUEST:Permanent failu... 1601-01-01T00:00:00.000Z                1601-01-01T00:00:00.000Z,GDSTATUS_BAD_REQUEST:Permanent failu... 1601-01-01T00:00:00.000Z                1601-01-01T00:00:00.000Z,.......

I get either blank in $FMessage or Method invocation failed because of the + "," at the end so I need this fixed.

then I'll concatenate into one final string and write to file

$csv +=$csvpt1 + "," + $csvpt2
$outfile.WriteLine($csv)
}
$outfile.Close()

In an added wish list it would also be great to be able to create the csv file columns header Failures for n number of columns as depicted by the largest number of FailedMessages nodes.

Your assistance is greatly appreciated.

Answer 1

Powershell has native support for XML, maybe this will help get you started?

It also has a native CSV Exporter with Export-Csv :)

[xml]$XMLfile = gc C:\Temp\migration.xml

$MasterArray = @()
$MasterArray = "" | Select User, Result, TotalEmails, SuccessfulEmails, FailedEmails, Failures

$MasterArray.User = $XMLfile.MigrationUserStatus.user
$MasterArray.Result = $XMLfile.MigrationUserStatus.StoreList.EmailMigrationStatus.MigrationStatus.value
$MasterArray.TotalEmails = $XMLfile.MigrationUserStatus.StoreList.EmailMigrationStatus.FolderList.TotalCount.value
$MasterArray.SuccessfulEmails = $XMLfile.MigrationUserStatus.StoreList.EmailMigrationStatus.FolderList.SuccessCount.value
$MasterArray.FailedEmails = $XMLfile.MigrationUserStatus.StoreList.EmailMigrationStatus.FolderList.FailCount.value

$Failures = $XMLfile.MigrationUserStatus.StoreList.EmailMigrationStatus.FolderList.FailedMessages
$ConcatFailures = @()
foreach ($Failure in $Failures)
{
    $ConcatFailures += $Failure.ErrorMessage + "," + $Failure.SentTime + "," + $Failure.ReceivedTime
}

$MasterArray.Failures = $ConcatFailures -Join "|"
$MasterArray
$MasterArray | Export-Csv -NoType "C:\Temp\export.csv"

For the other fields, you can check if they exist and add them if they do pretty easily, this should work:

foreach ($Failure in $Failures)
{
    if ($Failure.ErrorMessage) { $ConcatFailures += $Failure.ErrorMessage }
    if ($Failure.SentTime) { $ConcatFailures += $Failure.ErrorMessage }
    if ($Failure.ReceivedTime) { $ConcatFailures += $Failure.ReceivedTime }
    if ($Failure.MessageSubject) { $ConcatFailures += $Failure.MessageSubject }
    if ($Failure.MessageSize) { $ConcatFailures += $Failure.MessageSize }
}

To handle the xml files you want to add an outer loop to go through all of the xml files, and then append the data into an array that you build up as you go. This should do what you want, with some adjustments to the paths used:

$XMLFiles = gci "C:\Temp\" -Filter "*.xml"
$MasterArray = @()

foreach ($XMLFile in $XMLFiles)
{
    [xml]$XMLfile = gc $XMLFile.FullName

    $TempArray = @()
    $TempArray = "" | Select User, Result, TotalEmails, SuccessfulEmails, FailedEmails, Failures

    $TempArray.User = $XMLfile.MigrationUserStatus.user
    $TempArray.Result = $XMLfile.MigrationUserStatus.StoreList.EmailMigrationStatus.MigrationStatus.value
    $TempArray.TotalEmails = $XMLfile.MigrationUserStatus.StoreList.EmailMigrationStatus.FolderList.TotalCount.value
    $TempArray.SuccessfulEmails = $XMLfile.MigrationUserStatus.StoreList.EmailMigrationStatus.FolderList.SuccessCount.value
    $TempArray.FailedEmails = $XMLfile.MigrationUserStatus.StoreList.EmailMigrationStatus.FolderList.FailCount.value

    $Failures = $XMLfile.MigrationUserStatus.StoreList.EmailMigrationStatus.FolderList.FailedMessages
    $ConcatFailures = @()

    foreach ($Failure in $Failures)
    {
        if ($Failure.ErrorMessage) { $ConcatFailures += $Failure.ErrorMessage }
        if ($Failure.SentTime) { $ConcatFailures += $Failure.ErrorMessage }
        if ($Failure.ReceivedTime) { $ConcatFailures += $Failure.ReceivedTime }
        if ($Failure.MessageSubject) { $ConcatFailures += $Failure.MessageSubject }
        if ($Failure.MessageSize) { $ConcatFailures += $Failure.MessageSize }
    }
    $TempArray.Failures = $ConcatFailures -Join "|"

    $MasterArray += $TempArray
}

$MasterArray
$MasterArray | Export-Csv -NoType "C:\Temp\export.csv"