Solved Convert CSV to XML

May 13, 2020 at 08:40:44
Specs: Windows 7
I have this CSV

Department Emp_Name Emp_No City
Development Priya 23 Pune
Development Neel 21 Mumbai
HR Sneha 45 Satara
HR Riya 76 Sangali
Testing Swapna 54 Kolhapur
Testing Aakash 34 Pune

I want to convert this CSV file to XML file using windows batch script.

Expected output -

<Department = Development>
<Emp_Name=Priya Emp_No=23 City=Pune/>
<Emp_Name=Neel Emp_No=21 City=Mumbai/>
</Department>

<Department = HR>
<Emp_Name=Sneha Emp_No=45 City=Satara/>
<Emp_Name=Riya Emp_No=76 City=Sangali/>
</Department>

<Department = Testing>
<Emp_Name=Swapna Emp_No=54 City=Kolhapur/>
<Emp_Name=Aakash Emp_No=34 City=Pune/>
</Department>


Any Help???


See More: Convert CSV to XML

Reply ↓  Report •

✔ Best Answer
May 13, 2020 at 23:02:46
Rough draft and I consider to be crappy code. But it gives a basis for improvement (or reject). Worked on my local sims. Commas assumed (being CSV). Quotes are assumed in sourcefile but it may work without them.

@echo off & setlocal
set source=test.csv
set xml=test.xml
del %xml% 2>nul
set pre=""
rem ----- get labels
set /p h=<%source%
set c=1
call :aa %h%
goto :eof

:aa
set k%c%=%~1
set /a c+=1
shift
if "%1" neq "" goto :aa
for /f "skip=1 tokens=1* delims=," %%a in (%source%) do call :xx %%a %%b
goto :eof

:xx
if %1 neq %pre% (
if %pre% neq "" >> %xml% echo ^</Department^>
>> %xml% echo ^<department = %~1^>
set pre=%1
)
>>%xml% echo ^<%k2%=%~2 %k3%=%~3 %k4%=%~4/^>
goto :eof

message edited by nbrane



#1
May 13, 2020 at 09:18:21
Does it need to be batch?
Here is a solution using powershell:

Import-Csv -Path "C:\folder\file.csv" | Export-Clixml -Path "C:\folder\file.xml"

Edit:

"It is important to remember that CliXml is XML-based only and not interchangeable with a standard XML document."

Might not be what you were looking for, but worth checking out if you can use CliXml instead of Xml.

message edited by Kilavila


Reply ↓  Report •

#2
May 13, 2020 at 11:19:09
Thanks for your suggestion.

But i want to compare row value in 1st column if that are same then it will go in same tag

for ex:if Department name is same then other values like emp_name,emp_id,city will go in that tag.

Department Emp_Name Emp_No City
Development Priya 23 Pune
Development Neel 21 Mumbai

Expected output:
<Department = Development>
<Emp_Name=Priya Emp_No=23 City=Pune/>
<Emp_Name=Neel Emp_No=21 City=Mumbai/>
</Department>

Please can you suggest any batch script for comparing two rows.

message edited by Manjiri


Reply ↓  Report •

#3
May 13, 2020 at 13:26:23
Not sure i follow.

Do you want all the people that are in the same department to end up in the same tag?

So it would be like this:

Department1
Employee1
Employee2

Department2
Employee1
Employee2

And so on?

Could use an if statement to compare 2 strings tho, "if string1 equ string2".


Reply ↓  Report •

Related Solutions

#4
May 13, 2020 at 23:02:46
✔ Best Answer
Rough draft and I consider to be crappy code. But it gives a basis for improvement (or reject). Worked on my local sims. Commas assumed (being CSV). Quotes are assumed in sourcefile but it may work without them.

@echo off & setlocal
set source=test.csv
set xml=test.xml
del %xml% 2>nul
set pre=""
rem ----- get labels
set /p h=<%source%
set c=1
call :aa %h%
goto :eof

:aa
set k%c%=%~1
set /a c+=1
shift
if "%1" neq "" goto :aa
for /f "skip=1 tokens=1* delims=," %%a in (%source%) do call :xx %%a %%b
goto :eof

:xx
if %1 neq %pre% (
if %pre% neq "" >> %xml% echo ^</Department^>
>> %xml% echo ^<department = %~1^>
set pre=%1
)
>>%xml% echo ^<%k2%=%~2 %k3%=%~3 %k4%=%~4/^>
goto :eof

message edited by nbrane


Reply ↓  Report •

#5
May 14, 2020 at 02:57:06
Thank you for your suggestion.

It is properly working now.

Just a small worry though.
The department tag is not getting closed.

Could you help me with it?

Sorry to bother, I am new to Batch Script.


Reply ↓  Report •

#6
May 14, 2020 at 17:47:06
Fixed in #4. Also added the fwd-slash to the other lines as per your sample.

Reply ↓  Report •

#7
May 16, 2020 at 11:19:19
Thank you so much for your help.

Thank you for your suggestion.

It is properly working now.

You made my day.


Reply ↓  Report •

Ask Question