Hello
Here're two scrips you may try.
The SCRIPT 1 is intended to save the data as Excel file but the part populating and saving Excel sheet is not tested because I don't have Excel at hand. Note that, even if it works, numbers will be treated as text in the result sheet. If you want numbers as numbers, we need to modify the code as such.
The SCRIPT 2 is a provision in case SCRIPT 1 fails. It will save the data as csv file which may be opened by Excel. Note that the field separator is set to comma and not semi-colon. You can change it to semi-colon if you wish by setting $, to qq(;) in Perl script.
In both scripts, you need to set infile to the combined xml file created by the previous script and set the outfile to the result file. Currently, infile is assumed to be a file named out.xml in your desktop and outfile will be out.xls (in SCRIPT 1) or out.csv (in SCRIPT 2) in your desktop.
Good luck,
H
--SCRIPT1
(*
save xml data as xls file
*)
main()
on main()
set infile to (path to desktop)'s POSIX path & "out.xml"
set outfile to (path to desktop)'s POSIX path & "out.xls"
set r to do shell script "/usr/bin/perl -CSDA <<'EOF' - " & infile's quoted form & "
use strict;
use XML::LibXML;
my $f = $ARGV[0] or die qq(Usage: $0 input_xml_file);
my $parser = XML::LibXML->new();
my $doc = $parser->parse_file($f);
local $\\ = qq(\\n);
local $, = qq(\\t);
print qw(from jobname pages priority timezone year month day hour id);
my @jobs = $doc->findnodes('/Metadataobject/job');
for my $j ( @jobs ) {
my $from = $j->find('from');
my $jobname = $j->find('jobname');
my $pages = $j->find('pages');
my $priority = $j->find('priority');
my $timezone = $j->find('timezone');
my $year = $j->find('year');
my $month = $j->find('month');
my $day = $j->find('day');
my $hour = $j->find('hour');
my $id = $j->getAttribute('id');
print ($from, $jobname, $pages, $priority, $timezone, $year, $month, $day, $hour, $id);
}
EOF"
set rr to _text2array(r, tab, return) -- 2d-array representation of data
--return rr -- for test
save_array_as_xls(rr, outfile) -- # not tested
end main
on _text2array(t, cs, rs)
script o
property pp : _split(rs, t)
property qq : {}
repeat with p in my pp
set end of my qq to _split(cs, p's contents)
end repeat
return my qq's contents
end script
tell o to run
end _text2array
on _split(d, t)
(*
string or list d : separator(s)
string t : source string
return list : t splitted by d
*)
local astid, astid0, tt
set astid to a reference to AppleScript's text item delimiters
try
set {astid0, astid's contents} to {astid's contents, {} & d}
set tt to t's text items
set astid's contents to astid0
on error errs number errn
set astid's contents to astid0
error errs number errn
end try
return tt
end _split
on save_array_as_xls(_array, _file) -- # not tested
(*
list _array : list of lists representing 2d array of values of target range
string _file : POSIX path of file to be saved
* target range starts with $A$1 and ends with the cell determined by the size of _array
*)
set ix to count _array -- row count in _array
set jx to count (_array's item 1) -- column count in _array
tell application "Microsoft Excel"
set wbk to make new workbook
tell active sheet
set _range to "$A$1:" & (get address (row ix's cell jx))
set value of range _range to _array
end tell
save workbook as wbk filename (_file as POSIX file as string)
close active workbook saving no
end tell
end save_array_as_xls
--END OF SCRIPT 1
--SCRIPT 2
(*
save xml data as csv file
*)
set infile to (path to desktop)'s POSIX path & "out.xml"
set outfile to (path to desktop)'s POSIX path & "out.csv"
do shell script "/usr/bin/perl -CSDA <<'EOF' - " & infile's quoted form & " > " & outfile's quoted form & "
use strict;
use XML::LibXML;
my $f = $ARGV[0] or die qq(Usage: $0 input_xml_file);
my $parser = XML::LibXML->new();
my $doc = $parser->parse_file($f);
#
# CSV spec
#
# - record separator is CRLF
# - field separator is comma
# - every field is quoted
# - text encoding is UTF-8
#
local $\\ = qq(\\r\\n); # CRLF
local $, = qq(,); # comma
print qw(\"from\" \"jobname\" \"pages\" \"priority\" \"timezone\" \"year\" \"month\" \"day\" \"hour\" \"id\"); # header
my @jobs = $doc->findnodes('/Metadataobject/job');
for my $j ( @jobs ) {
my $from = $j->find('from');
my $jobname = $j->find('jobname');
my $pages = $j->find('pages');
my $priority = $j->find('priority');
my $timezone = $j->find('timezone');
my $year = $j->find('year');
my $month = $j->find('month');
my $day = $j->find('day');
my $hour = $j->find('hour');
my $id = $j->getAttribute('id');
print map { s/\"/\"\"/og; qq(\").$_.qq(\"); } ($from, $jobname, $pages, $priority, $timezone, $year, $month, $day, $hour, $id);
}
EOF"
--END OF SCRIPT 2
Message was edited by: Hiroto (Oops, fixed some code in SCRIPT 1. Sorry.)
