Duplicate orders in Excel from XML
Posted: Tue Aug 22, 2023 3:06 pm
Hello,
I need help with a batch file in Switch.
Following, the batch file accesses a folder, reads all the XML files in there and combines them into one XML.
So far everything works and the XML looks fine.
However, this file is then opened in Excel and suddenly every single node row is doubled and the jobs are duplicated in the Excel table.
What could be the reason for this?
I need help with a batch file in Switch.
Following, the batch file accesses a folder, reads all the XML files in there and combines them into one XML.
So far everything works and the XML looks fine.
However, this file is then opened in Excel and suddenly every single node row is doubled and the jobs are duplicated in the Excel table.
What could be the reason for this?
Code: Select all
@echo off
setlocal enabledelayedexpansion
REM Abrufen des aktuellen Datums im Format "MM-JJJJ"
for /f "tokens=2 delims=. " %%a in ('echo %date%') do (
set "currentMonth=%%a"
)
for /f "tokens=3 delims=. " %%b in ('echo %date%') do (
set "currentYear=%%b"
)
set "outputFile=Monatsabschluss_Jobs_%currentMonth%-%currentYear%.xml"
set "inputFolder=C:\Switch\MOVE_TO\Monatsabschluss\Monatsabschluss_Jobs_XML_IN"
set "outputFolder=C:\Switch\MOVE_TO\Monatsabschluss\Monatsabschluss_Jobs_XML_OUT"
REM Erstellen des Unterordnerpfads basierend auf aktuellem Monat und Jahr
set "subfolder=%currentMonth%-%currentYear%"
REM Erzeugen des XML-Headers und öffnenden Tags
(
echo ^<?xml version="1.0" encoding="UTF-8"?^>
echo ^<jobs^>
) > "%outputFolder%\%outputFile%"
REM Verzeichnisprüfung und Schleife zur Verarbeitung von XML-Dateien
for %%f in ("%inputFolder%\%subfolder%\*.xml") do (
for /f "delims=" %%j in ('powershell -Command "$content = Get-Content '%%~f' -Encoding UTF8; $root = [regex]::Matches($content, '<order>.*?</order>', [System.Text.RegularExpressions.RegexOptions]::Singleline); foreach ($order in $root) { Write-Output (' <job>' + $order.Value + '</job>') }"') do (
set "line=%%j"
echo !line! >> "%outputFolder%\%outputFile%"
)
)
REM Schließen des XML-Tags
echo ^</jobs^> >> "%outputFolder%\%outputFile%"
endlocal