Out of memory error while converting XML to CSV

Question

I want to convert an XML file into CSV using Anydata-0.12. The XML file is looking like this:

<FIXML r="20030618" s="20040109" v="4.4" xr="FIA" xv="1" xmlns="http://www.fixprotocol.org/FIXML-4-4">
<Batch>
<MktDataFull RptID="23520135" BizDt="2016-12-09"><Instrmt Sym="OEF" MMY="20171215" MatDt="2017-12-15" CFI="OCASPS" StrkPx="100" StrkMult="1" StrkValu="100" Mult="100" StrkCcy="USD"/><Full Typ="5" Px="5.7367" Ccy="USD" PxDelta="0.5" Dt="2016-12-09"/><Full Typ="D" Px="100.15" Dt="2016-12-09"/></MktDataFull>
<MktDataFull RptID="30818621" BizDt="2016-12-09"><Instrmt Sym="OEF" MMY="20180615" MatDt="2018-06-15" CFI="OCASPS" StrkPx="100" StrkMult="1" StrkValu="100" Mult="100" StrkCcy="USD"/><Full Typ="5" Px="7.3603" Ccy="USD" PxDelta="0.52" Dt="2016-12-09"/><Full Typ="D" Px="100.15" Dt="2016-12-09"/></MktDataFull>
<MktDataFull RptID="31165289" BizDt="2016-12-09"><Instrmt Sym="OEF" MMY="20170317" MatDt="2017-03-17" CFI="OCASPS" StrkPx="101" StrkMult="1" StrkValu="100" Mult="100" StrkCcy="USD"/><Full Typ="5" Px="1.7973" Ccy="USD" PxDelta="0.46" Dt="2016-12-09"/><Full Typ="D" Px="100.15" Dt="2016-12-09"/></MktDataFull>
<MktDataFull RptID="31165443" BizDt="2016-12-09"><Instrmt Sym="OEF" MMY="20170317" MatDt="2017-03-17" CFI="OCASPS" StrkPx="102" StrkMult="1" StrkValu="100" Mult="100" StrkCcy="USD"/><Full Typ="5" Px="1.2775" Ccy="USD" PxDelta="0.35" Dt="2016-12-09"/><Full Typ="D" Px="100.15" Dt="2016-12-09"/></MktDataFull>
<MktDataFull RptID="31165368" BizDt="2016-12-09"><Instrmt Sym="OEF" MMY="20170317" MatDt="2017-03-17" CFI="OCASPS" StrkPx="103" StrkMult="1" StrkValu="100" Mult="100" StrkCcy="USD"/><Full Typ="5" Px="0.8861" Ccy="USD" PxDelta="0.25" Dt="2016-12-09"/><Full Typ="D" Px="100.15" Dt="2016-12-09"/></MktDataFull>
<MktDataFull RptID="31165483" BizDt="2016-12-09"><Instrmt Sym="OEF" MMY="20170317" MatDt="2017-03-17" CFI="OCASPS" StrkPx="104" StrkMult="1" StrkValu="100" Mult="100" StrkCcy="USD"/><Full Typ="5" Px="0.5858" Ccy="USD" PxDelta="0.25" Dt="2016-12-09"/><Full Typ="D" Px="100.15" Dt="2016-12-09"/></MktDataFull>
<MktDataFull RptID="25807539" BizDt="2016-12-09"><Instrmt Sym="OEF" MMY="20170616" MatDt="2017-06-16" CFI="OCASPS" StrkPx="105" StrkMult="1" StrkValu="100" Mult="100" StrkCcy="USD"/><Full Typ="5" Px="1.321" Ccy="USD" PxDelta="0.26" Dt="2016-12-09"/><Full Typ="D" Px="100.15" Dt="2016-12-09"/></MktDataFull>
<MktDataFull RptID="30818579" BizDt="2016-12-09"><Instrmt Sym="OEF" MMY="20180615" MatDt="2018-06-15" CFI="OCASPS" StrkPx="105" StrkMult="1" StrkValu="100" Mult="100" StrkCcy="USD"/><Full Typ="5" Px="4.7838" Ccy="USD" PxDelta="0.4" Dt="2016-12-09"/><Full Typ="D" Px="100.15" Dt="2016-12-09"/></MktDataFull>
<MktDataFull RptID="32444397" BizDt="2016-12-09"><Instrmt Sym="OEF" MMY="20170616" MatDt="2017-06-16" CFI="OCASPS" StrkPx="106" StrkMult="1" StrkValu="100" Mult="100" StrkCcy="USD"/><Full Typ="5" Px="1.0134" Ccy="USD" PxDelta="0.26" Dt="2016-12-09"/><Full Typ="D" Px="100.15" Dt="2016-12-09"/></MktDataFull>
<MktDataFull RptID="32868839" BizDt="2016-12-09"><Instrmt Sym="OEF" MMY="20170120" MatDt="2017-01-20" CFI="OCASPS" StrkPx="107" StrkMult="1" StrkValu="100" Mult="100" StrkCcy="USD"/><Full Typ="5" Px="0.0079" Ccy="USD" PxDelta="0" Dt="2016-12-09"/><Full Typ="D" Px="100.15" Dt="2016-12-09"/></MktDataFull>
<MktDataFull RptID="32444384" BizDt="2016-12-09"><Instrmt Sym="OEF" MMY="20170616" MatDt="2017-06-16" CFI="OCASPS" StrkPx="109" StrkMult="1" StrkValu="100" Mult="100" StrkCcy="USD"/><Full Typ="5" Px="0.4888" Ccy="USD" PxDelta="0.11" Dt="2016-12-09"/><Full Typ="D" Px="100.15" Dt="2016-12-09"/></MktDataFull>
....
....
</Batch>
</FIXML>

The CSV file contains parts of the XML. It should have the column headers used in the XML file and look like this:

RptID,BizDt,StrkMult,Sym,StrkValu,Mult,MatDt,CFI,StrkCcy,MMY,StrkPx
23520135,2016-12-09,1,OEF,100,100,2017-12-15,OCASPS,USD,20171215,100
30818621,2016-12-09,1,OEF,100,100,2018-06-15,OCASPS,USD,20180615,100
31165289,2016-12-09,1,OEF,100,100,2017-03-17,OCASPS,USD,20170317,101
31165443,2016-12-09,1,OEF,100,100,2017-03-17,OCASPS,USD,20170317,102
31165368,2016-12-09,1,OEF,100,100,2017-03-17,OCASPS,USD,20170317,103
31165483,2016-12-09,1,OEF,100,100,2017-03-17,OCASPS,USD,20170317,104
...

I am running this code:

use AnyData;
my $input_xml = "oc170120.xml"; #name of the XML file
my $output_csv = "test3.csv"; #name of the output file
$flags->{record_tag} = 'Instrmt';
my $table = adTie( 'XML', $input_xml, 'r', $flags );
....

It is working and with a small file for test purposes everything is fine. But after a while I am getting

Out of Memory!

as adtie() tries to read the entire file into the memory and the XML file has more than 400000 records.

I am using Perl 5.24.1 on a 64bit system.

What are you doing after creating $table? Is it giving Out of Memory Error in this line or After creating the table where you are doing operations on data? — AbhiNickz
– AbhiNickz, Commented Feb 27, 2017 at 9:37
If the XML file is large, you can't load it into memory. Search for streaming processing of XML, i.e. XML::LibXML::Reader or XML::SAX. I'm not sure the deprecated AnyData can use either. — choroba
– choroba, Commented Feb 27, 2017 at 9:42
Thank you for your answers. It is giving the Out of Memory Error while processing the creation of the table. I am not able to do anything with the data as the program collapses before already... — odelys
– odelys, Commented Feb 27, 2017 at 9:57
That's because it tries to read a lot of data. How big is that file, in bytes? — simbabque
– simbabque, Commented Feb 27, 2017 at 12:22

Sobrique · Accepted Answer · 2017-02-27 14:55:35Z

Ok, so the problem with XML is that you can realistically assume that in memory, it's about 10x the size as 'on disk'.

So reading the whole thing, and then dumping it out is very memory inefficient, and for larger files - well, as you've see, that's a big problem.

For this sort of task (and to be fair, most XML tasks - I'm a huge fanboy) I like XML::Twig, because it lets you use twig_handlers to parse a file and discard 'processed' bits as you go - this keeps the memory footprint down.

So for your example:

#!/usr/bin/env perl

use strict;
use warnings;

use XML::Twig; 

my @keys = qw ( RptID BizDt Sym StrkValu Mult MatDt CFI StrkCcy MMY StrkPx );


sub process_data {
    my ( $twig, $data ) = @_; 
   # print join ",", map { $data -> get_xpath(".//*[\@$_]",0 )-> text } @keys;

    my %atts = map { %{$_->atts} } $data , $data -> children;
    print join ",", (map { $atts{$_} // '' } @keys),"\n";
    $data -> purge;
}

print join ",", @keys, "\n";
XML::Twig -> new ( twig_handlers => { 'MktDataFull' => \&process_data } ) -> parse (\*DATA);

__DATA__
<FIXML r="20030618" s="20040109" v="4.4" xr="FIA" xv="1" xmlns="http://www.fixprotocol.org/FIXML-4-4">
<Batch>
<MktDataFull RptID="23520135" BizDt="2016-12-09"><Instrmt Sym="OEF" MMY="20171215" MatDt="2017-12-15" CFI="OCASPS" StrkPx="100" StrkMult="1" StrkValu="100" Mult="100" StrkCcy="USD"/><Full Typ="5" Px="5.7367" Ccy="USD" PxDelta="0.5" Dt="2016-12-09"/><Full Typ="D" Px="100.15" Dt="2016-12-09"/></MktDataFull>
<MktDataFull RptID="30818621" BizDt="2016-12-09"><Instrmt Sym="OEF" MMY="20180615" MatDt="2018-06-15" CFI="OCASPS" StrkPx="100" StrkMult="1" StrkValu="100" Mult="100" StrkCcy="USD"/><Full Typ="5" Px="7.3603" Ccy="USD" PxDelta="0.52" Dt="2016-12-09"/><Full Typ="D" Px="100.15" Dt="2016-12-09"/></MktDataFull>
<MktDataFull RptID="31165289" BizDt="2016-12-09"><Instrmt Sym="OEF" MMY="20170317" MatDt="2017-03-17" CFI="OCASPS" StrkPx="101" StrkMult="1" StrkValu="100" Mult="100" StrkCcy="USD"/><Full Typ="5" Px="1.7973" Ccy="USD" PxDelta="0.46" Dt="2016-12-09"/><Full Typ="D" Px="100.15" Dt="2016-12-09"/></MktDataFull>
<MktDataFull RptID="31165443" BizDt="2016-12-09"><Instrmt Sym="OEF" MMY="20170317" MatDt="2017-03-17" CFI="OCASPS" StrkPx="102" StrkMult="1" StrkValu="100" Mult="100" StrkCcy="USD"/><Full Typ="5" Px="1.2775" Ccy="USD" PxDelta="0.35" Dt="2016-12-09"/><Full Typ="D" Px="100.15" Dt="2016-12-09"/></MktDataFull>
<MktDataFull RptID="31165368" BizDt="2016-12-09"><Instrmt Sym="OEF" MMY="20170317" MatDt="2017-03-17" CFI="OCASPS" StrkPx="103" StrkMult="1" StrkValu="100" Mult="100" StrkCcy="USD"/><Full Typ="5" Px="0.8861" Ccy="USD" PxDelta="0.25" Dt="2016-12-09"/><Full Typ="D" Px="100.15" Dt="2016-12-09"/></MktDataFull>
<MktDataFull RptID="31165483" BizDt="2016-12-09"><Instrmt Sym="OEF" MMY="20170317" MatDt="2017-03-17" CFI="OCASPS" StrkPx="104" StrkMult="1" StrkValu="100" Mult="100" StrkCcy="USD"/><Full Typ="5" Px="0.5858" Ccy="USD" PxDelta="0.25" Dt="2016-12-09"/><Full Typ="D" Px="100.15" Dt="2016-12-09"/></MktDataFull>
<MktDataFull RptID="25807539" BizDt="2016-12-09"><Instrmt Sym="OEF" MMY="20170616" MatDt="2017-06-16" CFI="OCASPS" StrkPx="105" StrkMult="1" StrkValu="100" Mult="100" StrkCcy="USD"/><Full Typ="5" Px="1.321" Ccy="USD" PxDelta="0.26" Dt="2016-12-09"/><Full Typ="D" Px="100.15" Dt="2016-12-09"/></MktDataFull>
<MktDataFull RptID="30818579" BizDt="2016-12-09"><Instrmt Sym="OEF" MMY="20180615" MatDt="2018-06-15" CFI="OCASPS" StrkPx="105" StrkMult="1" StrkValu="100" Mult="100" StrkCcy="USD"/><Full Typ="5" Px="4.7838" Ccy="USD" PxDelta="0.4" Dt="2016-12-09"/><Full Typ="D" Px="100.15" Dt="2016-12-09"/></MktDataFull>
<MktDataFull RptID="32444397" BizDt="2016-12-09"><Instrmt Sym="OEF" MMY="20170616" MatDt="2017-06-16" CFI="OCASPS" StrkPx="106" StrkMult="1" StrkValu="100" Mult="100" StrkCcy="USD"/><Full Typ="5" Px="1.0134" Ccy="USD" PxDelta="0.26" Dt="2016-12-09"/><Full Typ="D" Px="100.15" Dt="2016-12-09"/></MktDataFull>
<MktDataFull RptID="32868839" BizDt="2016-12-09"><Instrmt Sym="OEF" MMY="20170120" MatDt="2017-01-20" CFI="OCASPS" StrkPx="107" StrkMult="1" StrkValu="100" Mult="100" StrkCcy="USD"/><Full Typ="5" Px="0.0079" Ccy="USD" PxDelta="0" Dt="2016-12-09"/><Full Typ="D" Px="100.15" Dt="2016-12-09"/></MktDataFull>
<MktDataFull RptID="32444384" BizDt="2016-12-09"><Instrmt Sym="OEF" MMY="20170616" MatDt="2017-06-16" CFI="OCASPS" StrkPx="109" StrkMult="1" StrkValu="100" Mult="100" StrkCcy="USD"/><Full Typ="5" Px="0.4888" Ccy="USD" PxDelta="0.11" Dt="2016-12-09"/><Full Typ="D" Px="100.15" Dt="2016-12-09"/></MktDataFull>
</Batch>
</FIXML>

Now, you probably want to use:

XML::Twig -> new ( ... ) -> parsefile ('your_xml_file');

And maybe open a file handle to print that output to (at the moment it'll go to STDOUT, for illustration purposes)

But the important point in the above is that purge call, which tells XML::Twig that you're done parsing, and to vacate the 'processed' data from memory.

So should do what you want with a MUCH lower footprint.

Collectives™ on Stack Overflow

Out of memory error while converting XML to CSV

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related