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
Generate XML From a CSV or TXT File
Moderator: DosItHelp
Re: Generate XML From a CSV or TXT File (almost got it)
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.
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.
Re: Generate XML From a CSV or TXT File
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.
source.csv:
output.xml:
Antonio
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
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
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>
Re: Generate XML From a CSV or TXT File
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.
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.