Generate XML From a CSV or TXT File

[aisha]

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]

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]

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]

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.