Several of our clients have requested whether it was possible to deliver our reports in Excel format (XLS) instead of HTM like we do currently. While HTM allows for prettier displays than plain text, its two main detriments are the ability to re-sort the data or make any edits to it. Also, it is difficult to parcel the HTM reports out to colleagues or staff to tackle specific sections of the reports.
In the past, we tended to think just copying the files to excel (copy *.htm *.xls) was the panacea we were looking for. But this introduces formatting errors and it never seems to be a clean one-to-one solution.
We investigated changing our native code, which collates the headings, then generates them into respective HTM formatted reports. But this has proven to be far more difficult than we had hoped.
Ultimately, we decided to convert the existing HTM files to XLS using the powerful scripting language, Python. Python has access to XML/HTML libraries which helped us to glean the information we needed from the HTM tables. Then, we used an XLS library to shunt that data into an XLS spreadsheet, formatting the cells & data on the fly as we went along. Because of the control we have addressing the conversion in this manner, we can also add in line numbers for each heading, as well as counts for entire reports.
Some reports may or may not make that trek from HTM to XLS, however: reports such as R00 for Near Matches would take some serious work since it is very different from our other standard reports; also, R06 for Name headings has proven to be especially difficult, mainly due to the 1XX/240 combinations that are listed.
With that said, we have most of the ‘single-line’ reports in good shape for a conversion between HTM and XLS. So, reports like: R06 (excluding Names), R07, R09, R10, R11, R13, R14, R25. We’re currently working on reports with ‘double-lines’ like R30, R31, and R32 where the report lists an ‘old’ heading with a ‘new’ heading underneath.
As a sample of what’s possible so far, I’ve attached R25 – Unrecognized $z Subfields.xls to this email. This represents a straight conversion using our script, with no post-formatting done (all of that was handled via the script). It took about 1 second to convert this HTM report to XLS format. Out of the 96 headings in this report, there were 8 which our conversion had some issues with; even with those, we still list out the full heading for further review. Hopefully, as we make further progress on these conversions we can iron most of those issues.
If this is something that you would be interested in having us start doing for your reports, please let us know. We don’t think we are very far off from being able to do this, but we did want to let you know one of the things we have been working on.
Thanks,
Nate
<image001.gif>
<image002.gif>
Nate Cothran
Vice President, Automation Services
533 East 1860 South
Provo, Utah 84606
Phone: +1.800.288.1265, ext. 697
Direct: +1.801.342.5697
nate@bslw.com • www.bslw.com