Hi,
Every week, I am going to need to parse a large XML file into Excel. The file is too big to parse automatically by just opening it with Excel. In addition, even when the file size is small enough, the file data often creates funky characters that result in Excel refusing to parse it. I am looking at Magic Parser but I'm not a programmer. The demo worked well and generated this code:
<?php
require("MagicParser.php");
function myRecordHandler($record)
{
// This is where you write your code to process each record, such as loading a database
// Here we just display the record contents using PHP's internal print_r() function
print_r($record);
}
MagicParser_parse("V6 Red Obligation Delta1183375140455.xml","myRecordHandler","xml|DATA/BOND/");
?>
What could I use in place of print_r($record);
to get the output into an Excel file?
Thanks,
Elaine
Thanks for the quick and detailed reply! I'll try feedprocessor.com.
Regards, Elaine
Hello Elaine,
Thank you for your interest in Magic Parser.
Besides programming skills, in order to use Magic Parser to process your XML document you would need a web server running the PHP scripting language on which to run the script that you have developed to do the conversion for you. I'm afraid it sounds therefore that it is not really a viable solution in this instance.
Having said that, what you are looking for is basically a simple XML to CSV conversion - a CSV file (comma separated values) can be easily imported by Excel. The code to do this, based on the auto-generated code above would be along these lines:
<?phprequire("MagicParser.php");
function myRecordHandler($record)
{
global $fp;
fwrite($fp,implode(",",$record)."\n");
}
$fp = fopen("output.csv","w");
if (!$fp)
{
print "Could not create output.csv - check permissions!";exit();
}
MagicParser_parse("V6 Red Obligation Delta1183375140455.xml","myRecordHandler","xml|DATA/BOND/");
fclose($fp);
?>
The way this script works is to open a file for output (using fopen), and then for each record in the feed it writes the record as a string of values with comma separation (using PHP's implode function) and finally closes the file. This would leave you with the file output.csv in the same directory as the script - provided of course that PHP has write access to the current directory!
However...
All is not lost because I run another website that offers a free service aimed at the affiliate marketing arena, but may be useful in this application. The website is:
http://www.feedprocessor.com/
FeedProcessor.com uses Magic Parser "behind the scenes", and is a free, web based tool that lets you run a suite of tools against feeds that you upload to your account. One of those tools is "CSV Export", and if you use FeedProcessor.com to upload your XML, and then process it using CSV Export you may find that the output can be loaded into Excel easily. To get started, try these steps:
1) Head over to www.feedprocessor.com/ and create an account.
2) Click "My Feeds", and then "Add Feed".
3) Use the "Merchant Name" field as a generic describer for the file you are processing. Select upload from File, and "Autodetect" for the data representation format.
4) Upload the feed. FeedProcessor.com should pick out the records you require in exactly the same way as the demo tool on this website.
5) Click "My Jobs", and then "New Job".
6) Select the feed that you have just uploaded, and "CSV Export" as the tool. You will then go to the tool configuration page
7) The default settings should suffice. Click "Save and Run". After the job has completed, you will be able to download a CSV file of the XML data that you uploaded.
In subsequent weeks, all you need to do is upload your feed again, go to "My Jobs" and click "Run" against the job you created in step 5 - you don't need to go through all the steps again.
FeedProcessor.com is aimed at a very different audience (affiliate marketing), so the terminology may not make sense, but I think the tool may help you out here!
Cheers,
David.
MagicParser.com