Beefy Boxes and Bandwidth Generously Provided by pair Networks
more useful options
 
PerlMonks  

Using Perl to go from XML to EXCEL

by mlaursen (Initiate)
on Mar 17, 2011 at 17:40 UTC ( #893816=perlquestion: print w/ replies, xml ) Need Help??
mlaursen has asked for the wisdom of the Perl Monks concerning the following question:

I have this perl script:
use Spreadsheet::WriteExcel::Big; use XML::SAX::ExpatXS; #use lib qw(/vweb/cl2000/docs/); use lib qw(/opt/perl/scripts); use ExcelSAX; $excel_file_path = "/proddata/coll/targift/ftp/"; $book_cnt = 1; $sheet_cnt = 1; # The location of the XML file to be parsed. $file = "/proddata/coll/targift/ftp/TARGIFT_CARDSALES_RPT_031711_0933. +xls"; #$file = "OPERATOR_LEAD_RPT062405_51457.xml"; $day = (localtime(time))[3]; $month = ((localtime(time))[4]) + 1; $year = ((localtime(time))[5]) + 1900; $basename = "MONTHLY_INVENTORY_$year"."_$month"."_$day"; $callback = sub { my $worksheet = shift; my $workbook = shift; my $format1 = $workbook->add_format(); $format1->set_num_format('$#,##0.00'); $worksheet->set_landscape(); # Landscape mode $worksheet->set_column(0, 0, 10); $worksheet->set_column(1, 1, 35); $worksheet->set_column(2, 2, 15);; $worksheet->fit_to_pages(1,0); # set to 1 page wide and as long as nee +ded $worksheet->set_footer('&CPage &P of &N'); }; $handler = ExcelSAX->new("$excel_file_path", 0, $callback); $parser = XML::SAX::ExpatXS->new( Handler => $handler ); $parser->parse_uri($file);
This is the error that I am receiving.
perl /vweb/cl2000/docs/targift/xml/targift_card_sales_rpt_MARKL.pl Created workbook /proddata/coll/targift/ftp/TARGIFT_CARDSALES_RPT_0317 +11_1235.xls unclosed token at line 2191, column 3, byte 32765 at /vweb/cl2000/docs +/targift/xml/targift_card_sales_rpt_MARKL.pl line 55
The data is as follows. There are three columns trying to be created.
<ROW> <COL1 >T1500</COL1> <COL2 >1</COL2> <COL3 >50</COL3> </ROW> <ROW> <COL1 >T1502</COL1> <COL2 >1</COL2> <COL3 >50</COL3> </ROW> <ROW> <COL1 >T1505</COL1> <--- Line in the error. <COL2 >1</COL2> <COL3 >50</COL3> </ROW> <ROW> <COL1 >T1506</COL1> <COL2 >1</COL2> <COL3 >50</COL3> </ROW> <ROW>
Not a perl programmer so I am lost at why this is not working. Thanks

Comment on Using Perl to go from XML to EXCEL
Select or Download Code
Re: Using Perl to go from XML to EXCEL
by philipbailey (Chaplain) on Mar 17, 2011 at 21:26 UTC

    I'm not familiar with some of the modules you have used, so do not have a definitive reply. I was also puzzled why the error message mentions a problem at line 55 in a 35 (or so) line program: did you show us the actual program you ran?

    It does sound, though, that this is likely to be a problem with the input data, presumably an unmatched tag. Generally the line number in the error message of parsers represents the latest possible place the error lies--i.e. it may be earlier than the fragment you show. If the error is not obvious by inspection, some XML "well-formedness" checker may show you the problem. Also check against the DTD or XML schema if the XML document has one.

Re: Using Perl to go from XML to EXCEL
by Khen1950fx (Canon) on Mar 17, 2011 at 23:52 UTC
    I fixed the data. It was missing the element declaration.
    <?xml version="1.0" encoding="UTF-8"?><ROW> <COL> <COL1>T1500</COL1> <COL2>1</COL2> <COL3>50</COL3> </COL> <COL> <COL1>T1502</COL1> <COL2>1</COL2> <COL3>50</COL3> </COL> <COL> <COL1>T1505</COL1> <COL2>1</COL2> <COL3>50</COL3> </COL> <COL> <COL1>T1506</COL1> <COL2>1</COL2> <COL3>50</COL3> </COL> </ROW>
Re: Using Perl to go from XML to EXCEL
by dHarry (Abbot) on Mar 18, 2011 at 11:08 UTC

    Although you are looking for a Perl solution (as a non-Perl programmer?) I will mention two alternative solutions.

    1. Depending on the version of Excel that you are using you can also directly import XML into Excel. The web is full of examples.

    2. You can solve it using a more XML-ish way. In general, whenever you have to transform XML into something think XSLT. The simplest way of producing something that can be imported in Excel is to produce CSV. See for example Using XML::XSLT to convert XML to CSV for some ideas.

    Cheers

    Harry

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://893816]
Approved by philipbailey
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others examining the Monastery: (9)
As of 2014-12-28 21:31 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    Is guessing a good strategy for surviving in the IT business?





    Results (183 votes), past polls