Null .csv files when extracting node data from .xml files
Trying this again. Ok so I have a powershell script that processes .xml files in a directory. For each .xml file the script processes it is looking for every instance of <AuditRecord> and extracts all data within each node.
The challenge I am having when I try to write results to a .csv file it comes up empty. No headers, no data, absolutely nothing. I've been racking my brain over why it won't simply write the data into the .csv file
Update 07.31.2024: Incorporated recent suggestions. Code sample updated - no errors: but it still generates a .csv file with no headers or data in it (blank)
Update 07.31.2024: Refactored code. This time it doesn't generate any .csv file. This is very discouraging, don't know what to do at this point. I can fix the params to read the elements correctly as suggested, but I can't even generate a simple .csv with content. Any help would be sorely appreciated.
CODE
cls
$inPath = "C:\deathstar\test\in\"
$outPath = "C:\deathstar\test\in\"
# Create the 'out' directory if it doesn't exist
if (-not (Test-Path $outPath)) {
New-Item -ItemType Directory -Path $outPath | Out-Null
}
# Get all XML files in the 'in' directory
$xmlFiles = Get-ChildItem $inPath -Filter *.xml
foreach ($file in $xmlFiles) {
[xml]$xmlContent = Get-Content $file.FullName
$auditRecord = $xmlContent.AuditRecord
if ($auditRecord -ne $null) {
$outputData = [ordered]@{
AuditRecordID = $auditRecord.AuditRecordID
UserID = $auditRecord.PIUser.Name
UTCSeconds = $auditRecord.PITime.UTCSeconds
LocalDate = $auditRecord.PITime.LocalDate
PIModuleAction = $auditRecord.PIConfigurationDB.PIModules.Action
PIModuleUID = ($auditRecord.PIConfigurationDB.PIModules.PIModule.UniqueID -join ", ")
PIModuleName = $auditRecord.PIConfigurationDB.PIModules.PIModule.Name
PIModuleAttribName = ($auditRecord.PIConfigurationDB.PIModules.PIModule.PIModuleAttributes.PIModuleAttribute.Name -join ", ")
PIModValueBefore = $auditRecord.PIConfigurationDB.PIModules.PIModule.PIModuleAttributes.PIModuleAttribute.Value.Before.Type[0]
PIModValueAfter = $auditRecord.PIConfigurationDB.PIModules.PIModule.PIModuleAttributes.PIModuleAttribute.Value.After.Type[0]
PIModAttribName = $auditRecord.PIConfigurationDB.PIModules.PIModule.PIModuleAttributes.PIModuleAttribute.Name[1]
PIModAttribValBefore = $auditRecord.PIConfigurationDB.PIModules.PIModule.PIModuleAttributes.PIModuleAttribute.Value.Before.Type[1]
PIModAttribValAfter = $auditRecord.PIConfigurationDB.PIModules.PIModule.PIModuleAttributes.PIModuleAttribute.Value.After.Type[1]
PIPropertyName = $auditRecord.PIConfigurationDB.PIModules.PIModule.PIProperties.PIProperty.Name
PIPropertyAction = $auditRecord.PIConfigurationDB.PIModules.PIModule.PIProperties.PIProperty.Action
PIPropertyUNCName = $auditRecord.PIConfigurationDB.PIModules.PIModule.PIProperties.PIProperty.ParentUNC_Name
PIPropValBefore = $auditRecord.PIConfigurationDB.PIModules.PIModule.PIProperties.PIProperty.Value.Before.Type
PIPropertyValAfter = $auditRecord.PIConfigurationDB.PIModules.PIModule.PIProperties.PIProperty.Value.After.Type
}
$csvFileName = "$outPath\$($file.BaseName).csv"
$outputData | Export-Csv -Path $csvFileName -NoTypeInformation
}
}
.XML FILE SNIPPET
<?xml version="1.0" encoding="UTF-8" ?>
<PIAudit
xmlns="xml.osisoft.com-schemas-piaudit"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="xml.osisoft.com-schemas-piaudit http://xml.osisoft.com/Schemas/PIAudit">
<PIServer IPHost="DEATH-STAR" IPAddress="192.168.1.1"/>
<ExportDate UTCSeconds="1722272370" LocalDate="2024-07-29T12:59:30-04:00"/>
<OSUser Domain="RX" Name="dvadar" />
<AuditRecords ExportFileName="C:\Batch\1\input\pibasessAudit.dat3_Apr_24_17_24_10_16502" RecordCount="420" AuditFileName="D:\PI\log\pibasessAudit.dat" CreationDate="2024-03-28T17:47:42-04:00" Mask="ffffffffh">
<AuditRecord AuditRecordID="b066447a-20de-4659-8e5e-41ecb27d9d39">
<PIUser UserID="1" Name="piadmin"/>
<PITime UTCSeconds="1711662501" LocalDate="2024-03-28T17:48:21-04:00" />
<PIConfigurationDB>
<PIModules Action="Edit">
<PIModule UniqueID="feea9e80-3d3f-4f45-b58d-275e4845bcde, 31-Dec-69 16:00:01" Name="Database">
<PIModuleAttributes>
<PIModuleAttribute Name="ModifyDate">
<Value>
<Before Type="xs:dateTime">2024-03-28T17:47:42-04:00</Before>
<After Type="xs:dateTime">2024-03-28T17:48:21-04:00</After>
</Value>
</PIModuleAttribute>
<PIModuleAttribute Name="Revision">
<Value>
<Before Type="xs:long">35738</Before>
<After Type="xs:long">35739</After>
</Value>
</PIModuleAttribute>
</PIModuleAttributes>
<PIProperties>
<PIProperty Name="TemplateDataSet" Action="Edit" ParentUNC_Name="\\PIProperties">
<Value>
<Before Type="xs:hexBinary"></Before>
<After Type="xs:hexBinary"></After>
</Value>
</PIProperty>
</PIProperties>
</PIModule>
</PIModules>
</PIConfigurationDB>
</AuditRecord>
<AuditRecord AuditRecordID="d6588207-7ab2-4a4f-bfba-f62621a2cae2">
<PIUser UserID="1" Name="piadmin"/>
<PITime UTCSeconds="1711662705" LocalDate="2024-03-28T17:51:45-04:00" />
<PIConfigurationDB>
<PIModules Action="Edit">
<PIModule UniqueID="feea9e80-3d3f-4f45-b58d-275e4845bcde, 31-Dec-69 16:00:01" Name="Database">
<PIModuleAttributes>
<PIModuleAttribute Name="ModifyDate">
<Value>
<Before Type="xs:dateTime">2024-03-28T17:48:21-04:00</Before>
<After Type="xs:dateTime">2024-03-28T17:51:45-04:00</After>
</Value>
</PIModuleAttribute>
<PIModuleAttribute Name="Revision">
<Value>
<Before Type="xs:long">35739</Before>
<After Type="xs:long">35740</After>
</Value>
</PIModuleAttribute>
</PIModuleAttributes>
<PIProperties>
<PIProperty Name="TemplateDataSet" Action="Edit" ParentUNC_Name="\\PIProperties">
<Value>
<Before Type="xs:hexBinary"></Before>
<After Type="xs:hexBinary"></After>
</Value>
</PIProperty>
</PIProperties>
</PIModule>
</PIModules>
</PIConfigurationDB>
</AuditRecord>
<AuditRecord AuditRecordID="ff041b3b-ca18-4909-a16a-18444d8b17b9">
<PIUser UserID="1" Name="piadmin"/>
<PITime UTCSeconds="1711669167" LocalDate="2024-03-28T19:39:27-04:00" />
<PIConfigurationDB>
<PIModules Action="Edit">
<PIModule UniqueID="feea9e80-3d3f-4f45-b58d-275e4845bcde, 31-Dec-69 16:00:01" Name="Database">
<PIModuleAttributes>
<PIModuleAttribute Name="ModifyDate">
<Value>
<Before Type="xs:dateTime">2024-03-28T17:51:45-04:00</Before>
<After Type="xs:dateTime">2024-03-28T19:39:27-04:00</After>
</Value>
</PIModuleAttribute>
<PIModuleAttribute Name="Revision">
<Value>
<Before Type="xs:long">35740</Before>
<After Type="xs:long">35741</After>
</Value>
</PIModuleAttribute>
</PIModuleAttributes>
<PIProperties>
<PIProperty Name="TemplateDataSet" Action="Edit" ParentUNC_Name="\\PIProperties">
<Value>
<Before Type="xs:hexBinary"></Before>
<After Type="xs:hexBinary"></After>
</Value>
</PIProperty>
</PIProperties>
</PIModule>
</PIModules>
</PIConfigurationDB>
</AuditRecord>
</AuditRecords>
</PIAudit>
DESIRED OUTPUT
Solution
Consider XSLT, the special-purpose, industry-wide language designed to transform XML files into other XML, HTML, and even text files like CSVs! PowerShell can run XSLT with built-in System.Xml.Xsl or other, third-party XSLT processors (Saxon, Xalan, etc.) which can run higher versions 2.0 and 3.0.
Specifically, below XSLT 1.0 will walk through every descendant element and attribute of root (assumed to be <AuditRecord>) and extracts underlying text values in comma separation. To create unique headers, ancestor elements three levels up are concatenated together with underscore separators. For elements that do not have such ancestor, underscores are stretched out with no values between. Also, some elements do not have any text and hence return empty cells. Consider cleaning this up later on!
XSLT (save as .xsl file, a special .xml file)
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="text" indent="yes"/>
<xsl:strip-space elements="*"/>
<xsl:template match="/AuditRecord">
<!-- HEADERS -->
<xsl:for-each select="descendant::*/@* | descendant::*">
<xsl:value-of select="concat(name(../../..), '_', name(../..), '_', ../@Name, '_', name(..), '_', name())"/>
<xsl:if test="position() != last()">
<xsl:text>,</xsl:text>
</xsl:if>
<xsl:if test="position() = last()">
<xsl:text>
</xsl:text>
</xsl:if>
</xsl:for-each>
<!-- ROWS -->
<xsl:apply-templates select="descendant::*"/>
</xsl:template>
<!-- PARSE ELEMENTS -->
<xsl:template match="*">
<xsl:apply-templates select="@*"/>
<xsl:value-of select="text()"/>
<xsl:if test="position() != last()">
<xsl:text>,</xsl:text>
</xsl:if>
<xsl:if test="position() = last()">
<xsl:text>
</xsl:text>
</xsl:if>
</xsl:template>
<!-- PARSE ATTRIBUTES -->
<xsl:template match="@*">
<xsl:value-of select="."/>
<xsl:if test="position() != last()">
<xsl:text>,</xsl:text>
</xsl:if>
</xsl:template>
</xsl:stylesheet>
Output (CSV shown in markdown table version)
| ___AuditRecord_PIUser | _AuditRecord_piadmin_PIUser_UserID | _AuditRecord_piadmin_PIUser_Name | ___AuditRecord_PITime | _AuditRecord__PITime_UTCSeconds | _AuditRecord__PITime_LocalDate | ___AuditRecord_PIConfigurationDB | _AuditRecord__PIConfigurationDB_PIModules | AuditRecord_PIConfigurationDB__PIModules_Action | AuditRecord_PIConfigurationDB__PIModules_PIModule | PIConfigurationDB_PIModules_Database_PIModule_UniqueID | PIConfigurationDB_PIModules_Database_PIModule_Name | PIConfigurationDB_PIModules_Database_PIModule_PIModuleAttributes | PIModules_PIModule__PIModuleAttributes_PIModuleAttribute | PIModule_PIModuleAttributes_ModifyDate_PIModuleAttribute_Name | PIModule_PIModuleAttributes_ModifyDate_PIModuleAttribute_Value | PIModuleAttributes_PIModuleAttribute__Value_Before | PIModuleAttribute_Value__Before_Type | PIModuleAttributes_PIModuleAttribute__Value_After | PIModuleAttribute_Value__After_Type | PIModules_PIModule__PIModuleAttributes_PIModuleAttribute | PIModule_PIModuleAttributes_Revision_PIModuleAttribute_Name | PIModule_PIModuleAttributes_Revision_PIModuleAttribute_Value | PIModuleAttributes_PIModuleAttribute__Value_Before | PIModuleAttribute_Value__Before_Type | PIModuleAttributes_PIModuleAttribute__Value_After | PIModuleAttribute_Value__After_Type | PIConfigurationDB_PIModules_Database_PIModule_PIProperties | PIModules_PIModule__PIProperties_PIProperty | PIModule_PIProperties_TemplateDataSet_PIProperty_Name | PIModule_PIProperties_TemplateDataSet_PIProperty_Action | PIModule_PIProperties_TemplateDataSet_PIProperty_ParentUNC_Name | PIModule_PIProperties_TemplateDataSet_PIProperty_Value | PIProperties_PIProperty__Value_Before | PIProperty_Value__Before_Type | PIProperties_PIProperty__Value_After | PIProperty_Value__After_Type |
|-----------------------|------------------------------------|----------------------------------|---------------------------|---------------------------------|--------------------------------|--------------------------------------|-------------------------------------------|-------------------------------------------------|---------------------------------------------------|--------------------------------------------------------|----------------------------------------------------|------------------------------------------------------------------|----------------------------------------------------------|---------------------------------------------------------------|----------------------------------------------------------------|----------------------------------------------------|--------------------------------------|---------------------------------------------------|-------------------------------------|----------------------------------------------------------|-------------------------------------------------------------|--------------------------------------------------------------|----------------------------------------------------|--------------------------------------|---------------------------------------------------|-------------------------------------|------------------------------------------------------------|---------------------------------------------|-------------------------------------------------------|---------------------------------------------------------|-----------------------------------------------------------------|--------------------------------------------------------|---------------------------------------|-------------------------------|--------------------------------------|------------------------------|
| 1 | piadmin | 1711662501 | 2024-03-28T17:48:21-04:00 | | Edit | feea9e80-3d3f-4f45-b58d-275e4845bcde | 31-Dec-69 16:00:01 | Database | | ModifyDate | | xs:dateTime2024-03-28T17:47:42-04:00 | xs:dateTime2024-03-28T17:48:21-04:00 | Revision | | xs:long35738 | xs:long35739 | | TemplateDataSet | Edit | \\PIProperties | | xs:hexBinary | xs:hexBinary | | | | | | | | | | | | |
PowerShell
# Define the directory containing the XML files
$directory = "C:\test\"
# Get all XML files in the directory
$xmlFiles = Get-ChildItem -Path $directory -Filter *.xml
# Load XSLT
$xslt = New-Object System.Xml.Xsl.XslCompiledTransform;
$xslt.Load("C:\path\to\style.xsl");
# Loop through each XML file
foreach ($file in $xmlFiles) {
# Define the output CSV file path
$csvFilePath = Join-Path -Path $directory -ChildPath ($file.BaseName + ".csv")
# Run transformation (XML -> CSV)
$xslt.Transform($file.FullName, $csvFilePath);
}
- How to call native XPath functions from Select-Xml?
- xml.LoadData - Data at the root level is invalid. Line 1, position 1
- Remove few nodes in the xml with a match text in the sibling nodes using xslt
- Getting the weird hexadecimal code on the top of the page of the sitemap
- Removing Xml nodes with a match in the sibling nodes
- error: style attribute 'android:attr/windowOptOutEdgeToEdgeEnforcement' not found
- how to pass JSON data though a foreach loop in php
- SAXParseException: cvc-elt.1: Cannot find the declaration of element 'Document'
- XSL sorting nodes at different levels of one hierarchy
- Regex for quoting unquoted XML attributes
- Best way to add dynamic features into an html website without a CMS or php?
- Converting XML to JSON using Python?
- inserting newlines in xml file generated via xml.etree.ElementTree in python
- How to add widgets (e.g. a button) to a window in a C++ application using gtkmm and builder XML file
- XSLT - What does "/." do?
- dotnet test is unable to find a datacollector with friendly name 'Code Coverage'
- Space Before Closing Slash?
- "Expression must evaluate to a node-set."
- Python xml.ElementTree - function to return parsed xml in variable to be used later
- semi-space or half-space in Android Studio
- Generate Google C++ Unit Test XML Report
- SVG Scaling Text to fit container
- Google search console says my sitemap.xml has errors, but I can’t see them
- Android sliders with fixed numbers
- ONVIF GetDeviceInformation SOAP Message
- Navigation icon Too Large - How to resize it
- Can git be made to mostly auto-merge XML order-insensitive files?
- Does XHTML5 require a DOCTYPE/DTD?
- Android menu background black with Theme.AppCompat?
- custom namespace "localFunction" in Schematron file causes transformation crash