Generate XML From a CSV or TXT File

Discussion forum for all Windows batch related topics.

Moderator: DosItHelp

Post Reply
Message
Author
aisha
Posts: 26
Joined: 28 Sep 2016 06:40

Generate XML From a CSV or TXT File

#1 Post by aisha » 19 Dec 2017 16:07

Hello DOSTips list
I have been looking into how to create an XML file from a CSV or TXT (source.csv), and while I can now successfully echo the required syntax to create an XML (output.xml) manually.

However, to take it to the next level, we need to be able to read from a CSV or TXT (source.csv) file with six comma-separated values per line and read from the CSV or TXT (output.xml) file as source material for how to populate the generated XML (output.xml) file.

The source CSV or TXT (source.csv) file has many lines, each with the following types of data:
date,source,title,type,value,description

So an example from a CSV or TXT (source.csv) file:
12-19-2017, New York Times, Salad Spinners Observed Chasing Unicorns, open sources, lorem ipsum, flotsam and jetsam
12-15-2017, Washington Post, Salad Spinners Observed Chasing Unicorns, interview, eleven past five, The sister of an only child baked a marmite pizza

And we hope to have it create an XML (output.xml) file:
<stix:Timestamp:>12-19-2017</stix:Timestamp>
<indicator:Source:>New York Times</indicator:Source>
<indicator:Title:>Salad Spinners Observed Chasing Unicorns</indicator:Title>
<indicator:Type:>open sources</indicator:Type>
<indicator:Value:>lorem ipsum</indicator:Value>
<indicator:Description:>flotsam and jetsam</indicator:Description>
<stix:Timestamp:>12-15-2017</stix:Timestamp>
<indicator:Source:>Washington Post</indicator:Source>
<indicator:Title:>Salad Spinners Observed Chasing Unicorns</indicator:Title>
<indicator:Type:>interview</indicator:Type>
<indicator:Value:>eleven past five</indicator:Value>
<indicator:Description:>The sister of an only child baked a marmite pizza</indicator:Description>

thank you in advance - I hope I have described this clearly

Squashman
Expert
Posts: 4484
Joined: 23 Dec 2011 13:59

Re: Generate XML From a CSV or TXT File (almost got it)

#2 Post by Squashman » 19 Dec 2017 16:24

Personally I would look at doing this type of conversion with Vbscript, Jscript or Powershell as they all have native capability to read and write XML.

Also forgot about Microsoft's LogParser utility. It can read and write csv and xml, as well as many other types of file formats. I was playing around with it a few years ago to output xml to csv and csv to xml.

I am a bit confused by your XML example. There is no defined beginning and end of a record.

Aacini
Expert
Posts: 1910
Joined: 06 Dec 2011 22:15
Location: México City, México
Contact:

Re: Generate XML From a CSV or TXT File

#3 Post by Aacini » 19 Dec 2017 23:52

This code is almost complete; you just need to add an IF command in order to change "indicator" by "stix" when !i! EQU 1. The code assume that the first line in the SOURCE.CSV input file contain the names of the fields of OUTPUT.XML file. If you want to hardcode these names in the program, just define "HEADER=names,of,fields..." and move the "THEN" part of the IF before the FOR loop, and eliminate the "ELSE" part of the nested IF.

Code: Select all

@echo off
setlocal EnableDelayedExpansion

(for /F "delims=" %%a in (source.csv) do (
   if not defined header (
      set "header=%%a"
      set "n=0"
      for %%b in (%%a) do (
         set /A n+=1
         set "col[!n!]=%%b"
      )
   ) else (
      set "row=%%a"
      set "i=0"
      for %%b in ("!row:,=" "!") do (
         set /A i+=1
         for %%i in (!i!) do echo ^<indicator:!col[%%i]!:^>%%~b^</indicator:!col[%%i]!^>
      )
   )
)) > output.xml
source.csv:

Code: Select all

Timestamp,Source,Title,Type,Value,Description
12-19-2017,New York Times,Salad Spinners Observed Chasing Unicorns,open sources,lorem ipsum,flotsam and jetsam
12-15-2017,Washington Post,Salad Spinners Observed Chasing Unicorns,interview,eleven past five,The sister of an only child baked a marmite pizza
output.xml:

Code: Select all

<indicator:Timestamp:>12-19-2017</indicator:Timestamp>
<indicator:Source:>New York Times</indicator:Source>
<indicator:Title:>Salad Spinners Observed Chasing Unicorns</indicator:Title>
<indicator:Type:>open sources</indicator:Type>
<indicator:Value:>lorem ipsum</indicator:Value>
<indicator:Description:>flotsam and jetsam</indicator:Description>
<indicator:Timestamp:>12-15-2017</indicator:Timestamp>
<indicator:Source:>Washington Post</indicator:Source>
<indicator:Title:>Salad Spinners Observed Chasing Unicorns</indicator:Title>
<indicator:Type:>interview</indicator:Type>
<indicator:Value:>eleven past five</indicator:Value>
<indicator:Description:>The sister of an only child baked a marmite pizza</indicator:Description>
Antonio

aisha
Posts: 26
Joined: 28 Sep 2016 06:40

Re: Generate XML From a CSV or TXT File

#4 Post by aisha » 20 Dec 2017 08:18

Wow! thank you so much - that is perfect

I is true that the full output does not produce a fully functional XML file; I am going to be adding a starting part and an ending part to these files and that portion is already functional, so this wonderful assistance brings the requirement to completion now.

Post Reply