Convert XML to CSV (Pipe Delimited) - Large Files 100mb to 1GB

The XSLT would be really simple to get this done,
but I don't think that XSLT is the right answer with these filesizes.
You definitely need a streaming parser for this

You could look at STX
http://stx.sourceforge.net/
which is a forward only subset of XSLT, but that works with an extremely low footprint
Have a look first whether or not this would fit your architecture
I can then help you with the STX, whch would be as simple as the XSLT required for this
If you have some Perl knowledge XML::STX would work fine

There is a Streaming PullParser for Java, StAX,
you could use this for cutting your file in pieces (of 50MB eg.)
create the pipe-CSV for each piece using XSLT
and bring the result together after XSLT-ing
I am not a Java programmer, so I can't help you with that

Here is a nice introduction to StAX if you are a java programmer
http://www.ibm.com/developerworks/library/x-stax1.html
The technique is know as pull parsing, if you are a Python programmer you could use pulldom
http://www.ibm.com/developerworks/library/x-tipulldom.html
Uche has a more recent article where he has such a splitter in the middle of the article
http://www.ibm.com/developerworks/library/x-increfeed.html

cheers

Geert

Oh, btw, the above *only* works on a 2.0 processor! The one I mentioned, Saxon-SA: www.saxonica.com, is a 2.0 processor, and arguably the best and fastest around. In terms of speed, the most recent MSXML and .NET processors are quicker for most scenarios, but they cannot stream and they do not do the much more versatile XSLT 2.0 language.

If you cannot do Saxon 8, you should consider running the whole thing on a machine that has at least 4 tmes the amount of memory that your data has (true for MSXML, Saxon, libxslt, Gestalt and Xalan). If you don't, the machine will start swapping and the processing time goes from speedy to exponentially slow.

If you use a Java based browser on a windows machine, you have another problem: the Java JVM for clients has a maximum of approximately 300-400MB. You can allocate more on the commandline, but it won't be used and there starts the swapping around again. This problem should not occur with the server versions of the JVM. Nor should they occur on non-windows systems (not checked though).

As to your solution: you may want to change the order of the elements. If they need to be in the same order as the input document, the above solution will suffice pretty well. If they don't, you should consider using the comma-operator (again: only XSLT 2.0). You xsl:value will then look like this:

<xsl:value-of select="item_unique_id , item_ean  , itam_upc" separator="|" />

Or, if there is quite a lot, you can use whitespace to make it clearer, and you may even add comments (again, only XSLT 2.0) to the XPath:

<xsl:value-of select="
     item_unique_id,    (: the unique identifier :)
     item_ean ,            (: the EAN number of the item :)
     itam_upc ,
     item_xxx" separator="|" />

Etc.

Since you are new to XSLT: do not confuse the "|" operator with the "|" character in your output. The pipeline as operator instructs the processor to take the distinct union of all nodes that match the combined criteria:

    item_upc only selects all elements that match item_upc
    Item_upc | item_ean  will select all elements that match either item_upc or item_ean and will return them in document order.


HTH,

Cheers,
-- Abel Braaksma

On using streaming large documents, for Saxon, the article is here: http://www.saxonica.com/documentation/sourcedocs/serial.html

Note that, from these instructions, you must modify your XSLT document slightly:


<xsl:stylesheet
    xmlns:my="http://myfunctions"
    xmlns:saxon="http://sf.net/saxon/"
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="2.0">

    <!-- tell the processor to output text -->
    <xsl:output method="text" />

    <!-- use this param to select the file on the commandline -->
    <xsl:param name="document-url" />

    <!-- start push mode using the copy-of / document function -->
    <xsl:template name="main">
         <xsl:apply-templates select="my:read-document()" />
    </xsl:template>

    <!-- we need a function to prevent copying the tree into memory -->
    <xsl:function name="my:read-document">
        <xsl:copy-of select="document($document-url)//item_basic_data" saxon:read-once="true" />
    </xsl:function>

   <!-- here you can make one long list of all items you want in the CSV row -->
   <xsl:template match="item_basic_data">
     
<xsl:value-of select="
     item_unique_id,    (: the unique identifier :)
     item_ean ,              (: the EAN number of the item :)
     itam_upc ,
     item_xxx" separator="|" />

      <xsl:text>&#xA;</xsl:text>
   </xsl:template>

</xsl:stylesheet>


Now we are only processing what we want, so no need for the "throw away" template anymore  (the one with match="*"). In addition, we have added three elements that should take care of processing in push mode. This may seem like a lot of fuzz for getting push mode to work, but once you look past it, the simple processing instructions in the template matching declarations are still the same simple ones.

Cheers,
-- Abel Braaksma

Thanks for your comments.

Unfortunately I have very limited experience with XML (only what I have experimented with in trial and error).  I am not a java programmer, don't know any perl or python, but I can somewhat appreciate the structure of xml through my experience with xhtml.

I am trying to convert around 10 or 12 such large xml files to csv format.  I have been using some programs such as xml2csv which is command line based, so I don't even open the original xml file, but the conversion  has the effect of reducing the filesize enough for me to open the csv file  up in latest version of Excel (300,000k rows or records).  However, it seems to be causing some glitches because it is ',' comma delimited and the content has comma's too.

The program I am looking at now is twxml2csv which is gui based, but it needs an xsl file.

http://babelfish.altavista.com/babelfish/trurl_pagecontent?lp=de_en&trurl=http%3a%2f%2fwww.wenzlaff.de%2fxmltocsv.html
original in german: http://www.wenzlaff.de/xmltocsv.html


As a minimum, I am trying to extract the following headers from the xml file in the original post;

item_unique_id|item_ean|item_sku|item_upc|item_mpn|item_brand|item_name|item_model|item_category|item_short_desc|item_page_url|item_image_url|item_image_url_small|item_salesrank|item_price|item_inventory|item_shipping_charge|amzn_price|amzn_inventory|amzn_shipping_charge|fm_price|fm_inventory|fm_shipping_charge|tp_new_price|tp_new_inventory|tp_new_shipping_charge|tp_used_price|tp_used_inventory|tp_used_shipping_charge


After that, if possible, we can try extracting  the merch_cat_path field (one instance of it per product).

>>
"Since you are new to XSLT: do not confuse the "|" operator with the "|" character in your output. The pipeline as operator instructs the processor to take the distinct union of all nodes that match the combined criteria:"
<<

Although I don't fully understand, but I wasn't aware there was a distinct usage of the pipe character as an operator in xslt.

If you could please tell me what I need to do in layman terms, to convert the xml to csv file, with pipe as the delimiter.  I don't need to open the original xml files, only convert them to csv - because once that is done, I should be ok to open them in excel.

Ok, I see the problem here. Then, from an XSLT standpoint, I can offer you a working solution, but you'll have to use the non-free version of Saxon for it. But I think XSLT is far too far an overkill for what you want.

The tool you mention won't help you: it cannot handle the size you talk of. It uses XSLT 1.0 and cannot make use of the extensions I mentioned above.

But I can help you with a method that will transform your files into CSV in minutes. I used that technique several times with success to downsize large files from SAP. All you need is an installation of Perl (or Ruby or Python, but you say you are not a programmer, so let's stick to the easiest around) and the ability to call something on the commandline.

I'll construct a nice regular expression for you that processes the file. All you need to do is tell the program what fields to pick up. You do not need to check you XML from the 'inside' or learn XSLT or Perl for it.

If that will help your problem (and I think it does), I'll be happy to provide you with a little code for doing just that.

Cheers,
-- Abel Braaksma

Btw: your XML is not well formed. It seems to me that you loaded it into IE and than copied / pasted it from there. Instead, it is usually better to do a "view source" or to open the document in a text editor or an XML aware editor.

IE puts '-' signs in front of collapsable nodes. And it interprets entities like &amp; and shows the & instead of the source &amp;. When you copy what you see, your file is not what it was anymore ;)

Here's a very naive, but perhaps well-enough, attempt to do what you want. It captures all the tags you mention and outputs is contents. It also captures the first merch_cat_path and leaves any additionals.

A few buts:
 - This is clearly a one-off solution as it is not really easy to maintain it in the long run
 - It is quite naive: it only prints a field when it finds one, not when it does not find one

The last one is important. If you can say for sure that the tags you mention are always there (and are just empty, but still there, when they don't have content) and in the same order, there is no problem. If not, we have to reverse the way the logic is build in Perl.

Download perl (i.e., from activestate.com) and copy the following script (starting with the '#' sign) to a text file and call it xml_to_csv.pl. Most current installers add Perl to the command path. You can then call it as follows to process your files:

perl xml_to_csv.pl < myinput.xml > myoutput.csv

Then the file "myinput.xml" obviously contains your input and the file "myoutput.csv" will contain your output. If you want to append to the output you need to use >> output.csv instead.

The tags that you want to match go into a file with the name tags_to_match.txt. This is just a list of tags where each tag goes on one line on itself (no spaces appended or anything!)

This is the perl script:
-----------------------------
#!/usr/bin/perl -w
use strict;

# little script for thyros, Expert-Exchange.com Q_22833778.html
# see http://www.experts-exchange.com/Q_22833778.html

open TAGS, "tags_to_match.txt" or die("tags file 'tags_to_match.txt' not found");
my $all_tags = do { local $/; <TAGS> };
$all_tags = join("|", split(/\n/,$all_tags));

my $merge_cat_path_count = 0;
while (<>) {
      # new record
      if(m!</item_data>!) {
            print "$/" ;
            $merge_cat_path_count = 0;
      }
      print "$1|" if m!<(?:$all_tags)>(.*)</(?:$all_tags)>!;
      if (m!<(?:merch_cat_path)>(.*)</(?:merch_cat_path)>!) {
            print "$1" if $merge_cat_path_count == 0;
            $merge_cat_path_count++;
      }
}



This is the tags_to_match.txt i used
------------------------------------------------
item_unique_id
item_ean
item_sku
item_upc
item_mpn
item_brand
item_name
item_model
item_category
item_short_desc
item_page_url
item_image_url
item_image_url_small
item_salesrank
item_price
item_inventory
item_shipping_charge
amzn_price
amzn_inventory
amzn_shipping_charge
fm_price
fm_inventory
fm_shipping_charge
tp_new_price
tp_new_inventory
tp_new_shipping_charge
tp_used_price
tp_used_inventory
tp_used_shipping_charge


This is the output I received:
--------------------------------------
0115526102|9780115526107|0115526102|Stationery Office Books|The Official Learning to Drive Pack (Driving Skills)|Book|Paperback, Stationery Office Books|http://www.amazon.co.uk/exec/obidos/ASIN/0115526102/AssocID/ref=nosim|http://ec1.images-amazon.com/images/I/31DY01WGJ1L.jpg|http://ec1.images-amazon.com/images/I/11FE11DK7YL.jpg|233499|21.23|Usually dispatched within 1-2 business days|Check Site.|24.99|Usually dispatched within 24 hours|Free!|24.99|Usually dispatched within 24 hours|Free!|21.23|Usually dispatched within 1-2 business days|Check Site.|20.00|In Stock|Check Site.|Books/Subjects/Reference/Transport/Automotive/Driving &amp; the Highway Code
0115528423|9780115528422|0115528423|The Stationary Office (TSO)|The Official DSA Theory Test for Motorcyclists CD-ROM|Software|, Platforms: Windows XP|http://www.amazon.co.uk/exec/obidos/ASIN/0115528423/AssocID/ref=nosim|http://ec1.images-amazon.com/images/I/31Au9yM7IZL.jpg|http://ec1.images-amazon.com/images/I/11ldivFAIML.jpg|1068|16.99|Not yet released|Free!|16.99|Not yet released|Free!|16.99|Not yet released|Free!|Books/Subjects/Reference/Transport/Automotive/Driving &amp; the Highway Code


Of course, the web server will word-wrap the output. It is really only two lines because your input in your question only contained two records.

HTH, let me know what you find,

Cheers,
-- Abel Braaksma

Hello Abel,

I followed your instructions, and managed to process the files.  I checked the results in excel, and some of the fields were shifting to the left, i.e. merch_cat_path was showing in the shipping or stock column.  I think this is due to the element missing in the original xml when you said "it only prints a field when it finds one, not when it does not find one".  

We are using the final csv files to be parsed into a database, which relies on a consistent column structure, so the final csv would need to have something like:

item_unique_id|item_ean|iteam_upc|etc|etc|etc
12345678901|12345678|12345678|||abc
12345678901|12345678|12345678|sd||abc
12345678901|12345678|12345678|||abc

So there would be pipe characters present for each element, even if the element content was empty.  Also, if it is possible, can you please tweak the script to output the field headers into the final csv file as above.

Just to clarify, if we wanted to append results (not overwrite?) to an existing file, we'd use a command like so; ?

perl xml_to_csv.pl < myinput.xml >> myoutput.csv


Thanks for your help and patience.

I'm glad it works so far (but not yet well enough).

yes, that would be the correct command. But, if you want the headers, you will output the headers again... I'll try to customize the script for you. I am currently @work, so bear with me for a short while (probably this evening european time).

If you could, please.  Thanks very much, I appreciate it.

Hi again,

If you have the time to do it, I would like to request one change please.  In the earlier post I mentioned that we only need to extract one instance of 'merch_cat_path' (since there were sometimes upto 4 or 5 copies of it), however, if it is possible and not too much trouble, I'd like to be able to extract upto 5 instances of them provided they are all on the same row but separated as usual by pipe - so there would be some more field headers..

merch_cat_path|merch_cat_path2|merch_cat_path3|merch_cat_path4|merch_cat_path5

Only if it is not too much trouble.. I appreciate one instance of it just fine aswell, so its up to you.

Thanks again.

Hi Thyros

It will *always* output a | character for each line in the tags-file. If the tag is found in the current record (defined by </item_data>) it will print the contents of that element.

As it is now, it will output one empty row at the end of the loop. My guess was that that wouldn't be  too much of a problem

I added minor error checking: if the file you try to parse contains | characters, it will stop processing and output an error. That is because when you would have a pipeline in the text, it would make the output wrong (shifting columns).

Finally, I added a little feature, following your request. If one field can occur more than once, for each entry, add the field name in the tags-file *plus* a number indicating the occurrence you want to capture. I.e.:

merch_cat_path1 will catch the first occurrence of merch_cat_path
merch_cat_path5 will catch the fifth occurrence of merch_cat_path

if you do not add a number, the first occurrence and only the first occurrence will be captured. Using this style, you have all the freedom to place these merch_cat_path or any other repeatable item at any place in the column order. The contents of the tags-file I used while testing is similar to the previous one, but I added:

merch_cat_path1
merch_cat_path2
merch_cat_path3
merch_cat_path4
merch_cat_path5
merch_cat_path6


Hope this one works well for you!

Cheers,
-- Abel Braaksma

Hi Abel,

Thanks very much for this, it does the job great and has just made my life easier.  Just one thing:  it's not a big deal, but would it be possible to output the contents of the tags_to_match fields into the first row of the output.csv file?  If not, it's no problem, the file does the job as it is.

The selective merch_cat_path function is very nifty! and as for the pipeline error checking - this was also an excellent and very much essential addition - because in my tests, one of the first xml files tested was converted over perfectly, however in some other files which I was having problems with earlier turns out to be due to having pipe character in the content.. so this extra feature picked up on that and alerted me to it.  Simply brilliant.

I am going to post another question about how to strip the pipe character from large xml files without having to open the document - if you have any ideas.. check my recent questions in the next few minutes to post your answer.

Once again, thanks for this solution, I have spent the past 5 days non-stop researching and testing different things to make it work.. but none of them have been this elegant and simple.. so it means alot to me. Thanks.

A+

Peace..

> Just one thing:  it's not a big deal, but would it be possible to output the contents of the tags_to_match fields into the first row of the output.csv file?  

of course it is possible, but you wanted to concatenate the output from several runs. That would mean (since the perl program is unaware of previous runs) that you would end up having the headers several times in the file, i.e., for each concatenated output.

> this was also an excellent and very much essential addition
glad to hear that it worked out well :)


> without having to open the document
well, that's not really true: the program will open it for you, but you don't see it ;)

> but none of them have been this elegant and simple.. so it means alot to me.
you're welcome! Thanks for the points!

> I am going to post another question about how to strip the pipe character
add this line under the while(<>) {
s/\|//g;

not tested though.

Cheers,
-- Abel Braaksma

PS: be aware of the fact that this little tool is *absolutely not* capable of understanding XML. It is just a quick and easy hack to get this simple job done and it does so by looking at the XML as a string, not as an XML tree. It is not (or hardly) extendable in the long run. If you want a more stable or more capable solution, there're some options.... but they will look different from this one.