2

i need to convert a xml to csv using script. I found a solution with xmlstarlet but it is not available so i am returning to point 0. Here is my xml.

<root>
  <record>
  <id_localisation>8PJ</id_localisation>
  <data>
   <id_client>50C</id_client>  
      <mail>1@mail.com</mail>
      <adress>10  </adress>
      <num_tel>001</num_tel>
      <key>C</key>
     <contact>
        <name>toto</name>
        <birth>01/30/009</birth>
        <city>London</city>
      </contact>
  </data> 
  <data>
  <id_client>25C</id_client> 
      <mail>2@gmaiil.com</mail>
      <adress>20</adress>
      <num_tel>02200</num_tel>
      <key>D1</key>
      <contact>
        <name>tata</name>
        <birth>02/08/2004</birth>
        <city>Spa</city>
      </contact>
  </data> 
</record>
  <record>
  <id_localisation>ESP31</id_localisation>
  <data>
   <id_client>70D</id_client>  
            <mail>3@gmail.com</mail>
          <adress>7Bcd</adress>
          <num_tel>5555</num_tel>
          <key>D2</key>
      <contact>
        <name>titi</name>
        <birth>05/07/2014</birth>
        <city>StMarine</city>
      </contact>
  </data>
  <data>
        <id_client>10D</id_client>
          <mail>4@gmail.com</mail>
          <adress>888</adress>
          <num_tel>881.0</num_tel>
          <key>D3</key>
      <contact>
        <name>awk</name>
        <birth>05/08/1999</birth>
        <city>Bahrein</city>
      </contact>
  </data>
 </record>
 </root>

And the csv i need, note that the headers are output

id_localisation;id_client;key
8PJ;50C;C
 8PJ;25C;D1
 ESP1;70D;D2
 ESP1;10D;D3

I can't install any library but i can use awk, perl, bash so i'am open on solution.

  • do you have xsltproc ? – Archemar Apr 21 '15 at 14:53
  • 2
    Without libraries this is difficult - XML is pretty fundamentally a thing that needs proper parsing. – Sobrique Apr 21 '15 at 14:54
  • no i do not have it, and i can't install anything on the server – iceman225 Apr 21 '15 at 14:54
  • 1
    Without xmlstarlet or one of the XSLT processors you're in a situation akin to using a kitchen knife not only to fix a screw but also to drill the hole it's got to fit into. You really need to get XML parsing tools installed on this server if you're being expected to parse XML. Really. – roaima Apr 21 '15 at 15:54
  • "i can't install anything on the server": If you have writable home directory, you can install virtually anything. The only limit is running things that requires super-user privileges to run. XMLStarlet does not require any special permissions. – Kusalananda Jul 11 '16 at 22:40
2

OK. There's a pretty fundamental problem here. XML is complex language with a detailed spec. Without libraries this is difficult - XML is pretty fundamentally a thing that needs proper parsing.

So for example using XML::Twig with Perl, you'd get:

use strict;
use warnings;

use XML::Twig;

my $twig = XML::Twig->new()->parsefile ( 'your_xml_file.xml' );

print "id_localisation;id_client;key\n";
foreach my $record ( $twig->root->children('record') ) {
    foreach my $data ( $record->children('data') ) {
        print join( ";",
            $record->first_child_text('id_localisation'),
            $data->first_child_text('id_client'),
            $data->first_child_text('key'),
            ),
            "\n";
    }
}

You may find that XML::Twig is already available - it's a fairly common 'default install'. So check that first.

There is a dirty sort of hacky way of doing it, but I really wouldn't recommend doing this. I mean really really, because it makes brittle and horrible code.

I cannot reiterate this in any stronger terms. Before you do this, have a look at: https://stackoverflow.com/a/1732454/2566198

And: https://stackoverflow.com/a/28913945/2566198

Note that you can install perl modules 'per user' in certain circumstances, which may be particularly relevant here.

And recognise that your code deliberately violates the XML spec - treating it as a text file instead. This is all sorts of bad juju, not least because there are some perfectly valid things you can do to XML which will break this.

However if you ABSOLUTELY MUST:

use strict;
use warnings;

open ( my $input_xml, "<", 'your_xml_file.xml' ) or die $!;
my $loca = "";
print "id_localisation;id_client;key\n";

for (<$input>) {
    my ($value) = (m/>(\w+)</);
    if (m/id_localisation/) { $loca = $value; }
    if (m/id_client/)       { print "$loca;$value;"; }
    if (m/key/)             { print "$value\n"; }
}
close($input);

Which will print:

id_localisation;id_client;key
8PJ;50C;C
8PJ;25C;D1
ESP31;70D;D2
ESP31;10D;D3

If you're planning on using this for any sort of production function, then first poke whoever won't let you install things and get them to install the libraries you need. That may sound like hard work, but trust me - there is nothing worse than trying to troubleshoot something that mysteriously broke one day because the upstream data format changed (in a spec valid fashion) but a downstream script doesn't implement the standard.

  • 1
    Can't decide whether this is +1 for solving the problem at hand or -1 for using REs to parse XML... (leaning towards +1) – roaima Apr 21 '15 at 15:52
  • 1
    Yeah, I know. It's nasty. It only works if you close your eyes and wish really hard that it was plain text. In my defence - I've offered a 'right way' first :) – Sobrique Apr 21 '15 at 15:58
  • thank for your answer, the major problem is the fact that it is our client's server so we cannot acceed it. I start using your script but i have further information about the use of this xml to csv. It is just for loading data in database. However it can be done by pl/sql so i will start by your script until i find how to do it in pl/sql – iceman225 Apr 21 '15 at 16:13
  • You may well find that your client is receptive to a request to add a parser. After all, it's for their benefit. – Sobrique Apr 21 '15 at 16:23
1

I would have suggest following xsl file

<?xml version="1.0"?>

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:output type="text" indent="yes" omit-xml-declaration="yes" />

<xsl:template match="//record">

<xsl:for-each select="data"  >
<xsl:value-of select="../id_localisation" />;<xsl:value-of select="id_client" />;<xsl:value-of select="key "/><xsl:text>;
</xsl:text>
</xsl:for-each>
</xsl:template>

</xsl:stylesheet>

to be use with

xsltproc sample.xsl sample.xml
0

Use lxprintf from the ltXML2 toolkit (Edinburgh University), eg:

$ lxprintf -e data "%s;%s;%s\n" ../id_localisation id_client key test.xml
8PJ;50C;C
8PJ;25C;D1
ESP31;70D;D2
ESP31;10D;D3

Using XSLT2 is fine, but overkill for this kind of extracting.


XML FAQ: http://xml.silmaril.ie/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.