Skip to main content

How to Convert XML to CSV in Batch Script

Extensible Markup Language (XML) is highly structured and great for complex nested data, but it is notoriously difficult for legacy systems or simple spreadsheet tools (like Excel) to read. Converting an XML payload into a flat Comma-Separated Values (CSV) file makes the data instantly sortable, filterable, and readable by almost any application.

Because native Batch scripts lack an XML parser, attempting to use FOR /F loops to find <Tag> strings is fragile and breaks if the XML formatting changes. In this guide, we will demonstrate how to reliably convert XML to CSV using a PowerShell bridge.

The Strategy: The PowerShell Bridge​

  1. Use PowerShell's [xml] type accelerator to cast the file content into an XML object natively.
  2. Navigate to the repeating node within the XML structure.
  3. Select the specific properties (tags) you want to export.
  4. Use Export-Csv to format the data and write it to disk.

Setup: A Sample XML File​

To test the script, create a sample XML file named users.xml with the following content:

<?xml version="1.0" encoding="utf-8"?>
<Company>
  <Employees>
    <Employee>
      <ID>101</ID>
      <Name>Jane Austen</Name>
      <Department>Sales</Department>
    </Employee>
    <Employee>
      <ID>102</ID>
      <Name>Mark Twain</Name>
      <Department>Engineering</Department>
    </Employee>
  </Employees>
</Company>

Implementation Script​

@echo off
setlocal enabledelayedexpansion

:: Define file paths
set "xmlFile=users.xml"
set "csvFile=users.csv"

echo Checking for XML file...
if not exist "%xmlFile%" (
    echo [ERROR] "%xmlFile%" not found.
    pause
    exit /b 1
)

echo Converting XML to CSV...

:: Execute the conversion via PowerShell
:: 1. Read the file into an [xml] object.
:: 2. Target the specific repeating node ($xmlData.Company.Employees.Employee).
:: 3. Select the properties to export.
:: 4. Export as CSV (NoTypeInformation removes the .NET type header).
powershell -NoProfile -Command ^
    "[xml]$xmlData = Get-Content -Path '%xmlFile%'; " ^
    "$xmlData.Company.Employees.Employee | " ^
    "Select-Object ID, Name, Department | " ^
    "Export-Csv -Path '%csvFile%' -NoTypeInformation -Encoding UTF8"

:: Capture the exit code immediately
set "psResult=!errorlevel!"

if !psResult! neq 0 (
    echo [ERROR] PowerShell conversion failed with exit code !psResult!.
    pause
    exit /b 1
)

:: Verify the output file was created
if not exist "%csvFile%" (
    echo [ERROR] Output file "%csvFile%" was not created.
    pause
    exit /b 1
)

echo.
echo ==========================================
echo CONVERSION SUCCESSFUL
echo Output generated at: "%csvFile%"
echo ==========================================

endlocal
pause
exit /b 0

Advanced: Choosing Specific Attributes​

Sometimes data is stored in XML attributes (<Employee ID="101">) rather than child nodes. PowerShell handles this seamlessly. If your XML looks like this:

<Employees>
  <Employee ID="101" Name="Jane Austen" />
</Employees>

The PowerShell pipeline logic remains almost exactly the same, as PowerShell treats both child nodes and attributes as accessible properties on the object.

Why Convert XML to CSV?​

  1. Reporting Pipelines: Taking nightly XML health reports generated by VMware or Hyper-V and flattening them into CSV files so they can be ingested by Power BI or Excel dashboards.
  2. Legacy Ingestion: Many modern web APIs return data strictly in XML or JSON, but legacy AS/400 mainframes or old database import routines strictly require flat CSV files.
  3. Human Readability: Non-technical accounting staff cannot easily read an XML invoice dump, but a CSV file opens directly in Excel.

Important Considerations​

  1. Nested Lists: CSV is a "flat" format. If your XML contains a list within a list (e.g., an Employee node with an <Emails> node that contains five different <Email> addresses), the CSV converter will output System.Object[] for that column. You must write custom PowerShell logic to perfectly flatten nested lists (e.g., joining them with a semicolon) before exporting.
  2. File Encoding: Native Batch assumes ANSI encoding. Using -Encoding UTF8 in the PowerShell Export-Csv cmdlet guarantees that international characters (like accents or symbols) are preserved when opened in Excel.
  3. Dynamic Headers: The Select-Object cmdlet defines the CSV headers precisely. If you omit Select-Object and just pipe to Export-Csv, PowerShell will attempt to guess the columns based on the first <Employee> node it encounters.

Conclusion​

Converting inherently nested XML data into a flat CSV format is a formidable requirement in data migration and reporting. Attempting to parse XML as raw text in Batch is dangerous. However, by wrapping a succinct PowerShell object-casting command inside your Batch script, you bridge the gap instantly, providing clean, tabular datasets without sacrificing automation simplicity.

Table of Contents