XML to CSV

grob115 · 04-12-2010, 04:38 AM

Just wondering if there are any tools available for doing this. I found CSV to XML but not the other way around. Basically I have the following structure:
<Level Grade=1>
<Content Step=A>Romero</Content>
<Content Step=B>Julie</Content>
<Content Step=C>Stephen</Content>
</Level>
<Level Grade=2>
<Content Step=A>Thomas</Content>
</Level>
<Level Grade=3>
<Content Step=A>Mary</Content>
<Content Step=B>Flora</Content>
<Content Step=C>Michael</Content>
<Content Step=D>Jerry</Content>
</Level>

And I want it to appear into the following structure:
Level, Content A, Content B, Content C, Content D
1, Romero, Julie, Stephen
2, Thomas, ,
3, Mary, Flora, Michael, Jerry

Any ideas?

Sergei Steshenko · 04-12-2010, 05:48 AM

Quote:

Originally Posted by grob115

Just wondering if there are any tools available for doing this. I found CSV to XML but not the other way around. Basically I have the following structure:
<Level Grade=1>
<Content Step=A>Romero</Content>
<Content Step=B>Julie</Content>
<Content Step=C>Stephen</Content>
</Level>
<Level Grade=2>
<Content Step=A>Thomas</Content>
</Level>
<Level Grade=3>
<Content Step=A>Mary</Content>
<Content Step=B>Flora</Content>
<Content Step=C>Michael</Content>
<Content Step=D>Jerry</Content>
</Level>

And I want it to appear into the following structure:
Level, Content A, Content B, Content C, Content D
1, Romero, Julie, Stephen
2, Thomas, ,
3, Mary, Flora, Michael, Jerry

Any ideas?

Yes, grab a Perl module parsing XML and grab a Perl module generating CSV and glue them together.

Tinkster · 04-12-2010, 05:53 AM

A prototype that needs LOTS of more work ;}

Code:

awk 'BEGIN{RS=ORS="<Level Grade";FS=OFS="\n"}{printf "%s,", gensub(/=([0-9]+).*/, "\\1","1",$1);for(i=1;i<=NF;i++){if($i ~ /Content Step/){printf "%s,",gensub(/^[^>]+>([^<]+)<.*/,"\\1","1", $i)}}printf "\n"}' file 
,
1,Romero,Julie,Stephen,
2,Thomas,
3,Mary,Flora,Michael,Jerry,

You get no headers, and the comma count is not the same on
all lines ....

Sergei Steshenko · 04-12-2010, 05:57 AM

Quote:

Originally Posted by Tinkster

A prototype that needs LOTS of more work ;}

Code:

awk 'BEGIN{RS=ORS="<Level Grade";FS=OFS="\n"}{printf "%s,", gensub(/=([0-9]+).*/, "\\1","1",$1);for(i=1;i<=NF;i++){if($i ~ /Content Step/){printf "%s,",gensub(/^[^>]+>([^<]+)<.*/,"\\1","1", $i)}}printf "\n"}' file 
,
1,Romero,Julie,Stephen,
2,Thomas,
3,Mary,Flora,Michael,Jerry,

You get no headers, and the comma count is not the same on
all lines ....

XML is not a line-oriented format, will your code work for records whose opening and closing tags are not on the same line ?

Also, what will your code do with ill-formed XML ?

grail · 04-12-2010, 07:08 AM

Now I know Sergei is going to beat me about the head and ears, but I just thought I would put up an alternative to Tinkster's
as a learning solution for parsing files in general (although acknowledge not solely the best way top approach this):

Code:

awk 'BEGIN{RS="</Level>\n";FS=">\n";OFS=","}{for(i=1;i<NF;i++)gsub(/.*[A-Z]>|<\/.*|<L.*=/,"",$i);gsub(OFS"$","")}1' infile

@Tinkster - I have removed the trailing commas, but the headers would take some more work

Sergei Steshenko · 04-12-2010, 07:19 AM

Quote:

Originally Posted by grail

Now I know Sergei is going to beat me about the head and ears, but I just thought I would put up an alternative to Tinkster's
as a learning solution for parsing files in general (although acknowledge not solely the best way top approach this):

Code:

awk 'BEGIN{RS="</Level>\n";FS=">\n";OFS=","}{for(i=1;i<NF;i++)gsub(/.*[A-Z]>|<\/.*|<L.*=/,"",$i);gsub(OFS"$","")}1' infile

@Tinkster - I have removed the trailing commas, but the headers would take some more work

Folks, I've seen too many production environment failures because of "childish" parsers. Childishness is assuming certain line oriented-ness when the language does not impose it.

The only legitimate cases to write "childish" parsers is when their input is generated automatically and the one who writes the parser can guarantee the input format won't change.

knudfl · 04-12-2010, 07:34 AM

May be ...
http://www.google.com/linux?hl=en&q=xml2csv&btnG=Search

http://sourceforge.net/projects/java-xml2csv/files/

At least it can convert the included example with this command:
java -cp bin gs.xml2csv.xml2csv
.....

knudfl · 04-12-2010, 07:34 AM

May be ...
http://www.google.com/linux?hl=en&q=xml2csv&btnG=Search

http://sourceforge.net/projects/java-xml2csv/files/

At least it can convert the included example with this command:
java -cp bin gs.xml2csv.xml2csv
.....

grail · 04-12-2010, 07:59 AM

@Sergei

Quote:

Folks, I've seen too many production environment failures because of "childish" parsers. Childishness is assuming certain line oriented-ness when the language does not impose it.

The only legitimate cases to write "childish" parsers is when their input is generated automatically and the one who writes the parser can guarantee the input format won't change.

Agree

Just my

if it were oriented

grob115 · 04-12-2010, 10:55 AM

First, thanks for the responses. Questions:
Sergei, can you show me some examples (maybe online) of these Perl modules? I don't know anything about them, and definitely nothing about gluing modules together.

knudfl, thanks for the link on the xml2csv java binary. Can you tell me where you found the way to input that command? I looked over the Sourceforge site but didn't find any documentation. Do they have both Windows and Linux versions?

knudfl · 04-12-2010, 11:09 AM

cd xml2csv/
cat xml2csv.bat

The file xml2csv.bat is a four liner,
line 2 = java -cp bin gs.xml2csv.xml2csv

.....

Sergei Steshenko · 04-12-2010, 11:58 AM

Quote:

Originally Posted by grob115

First, thanks for the responses. Questions:
Sergei, can you show me some examples (maybe online) of these Perl modules? I don't know anything about them, and definitely nothing about gluing modules together.

knudfl, thanks for the link on the xml2csv java binary. Can you tell me where you found the way to input that command? I looked over the Sourceforge site but didn't find any documentation. Do they have both Windows and Linux versions?

http://search.cpan.org/search?query=xml+parser&mode=all ->
http://search.cpan.org/~msergeant/XM...2.36/Parser.pm ;
http://search.cpan.org/~andya/XML-De...XML/Descent.pm
.

Regarding writing in CSV format - it's probably for your case faster to just write it manually than to use a module; still,

http://search.cpan.org/search?query=CSV&mode=all ->
http://search.cpan.org/~makamaka/Tex...ib/Text/CSV.pm ;
http://search.cpan.org/~djr/Class-CSV-1.03/CSV.pm ;
http://search.cpan.org/~gwyn/Data-Ta.../Dumper/CSV.pm .