Backstage - HTM to XLS Reports
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 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 <mailto:nate@bslw.com?subject=Automation%20Services%20-%20Inquiry> * www.bslw.com
This is excellent news! Thank you for working on this. We've used Ted Waller's procedures for converting the reports to Excel, which he posted on this list a few years ago. Being able to go directly to Excel files would be such a time saver. Linda --------------- Linda Ballinger Principal Cataloging Librarian Newberry Library Chicago, IL ballingerl@newberry.org Nate Cothran wrote:
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
<snip>
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.
I completely agree with Linda--thanks so much! And kudos to Ted who's procedure had been critical to my work for years. --Martha Sent from my iPad On Oct 8, 2012, at 9:48 AM, Linda Ballinger <ballingerl@newberry.org> wrote:
This is excellent news! Thank you for working on this. We've used Ted Waller's procedures for converting the reports to Excel, which he posted on this list a few years ago. Being able to go directly to Excel files would be such a time saver.
Linda --------------- Linda Ballinger Principal Cataloging Librarian Newberry Library Chicago, IL ballingerl@newberry.org
Nate Cothran wrote:
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
<snip>
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.
BSLWAC mailing list BSLWAC@mailman.xmission.com http://mailman.xmission.com/cgi-bin/mailman/listinfo/bslwac
Thank you everyone for your feedback so far, it is encouraging. As we get this ready to send out to everyone (that wants it), we imagine we will need to make tweaks to the programming to work out any kinks that we haven’t come across yet or add in new functionality that could further reduce the time spent at your end. Also, since this is a conversion program from existing HTM reports, if you do wish to see both sets of reports (HTM + XLS), we are fairly certain we can make that happen. As I think about it, what will probably happen is we would generate one set of HTM reports and another corresponding set of XLS reports (for those HTM reports that we can convert, that is). That way, you still have the option to look at the HTM report when necessary. Our hope is to get this in place before the end of October, so we will keep you posted on our progress towards that goal. Nate Cothran Backstage Library Works From: bslwac-bounces+nate=bslw.com@mailman.xmission.com [mailto:bslwac-bounces+nate=bslw.com@mailman.xmission.com] On Behalf Of HELIN Sent: Monday, October 08, 2012 10:19 AM To: Backstage Library Works Authority Contol Listserv Cc: Backstage Library Works Authority Contol Listserv Subject: Re: [BSLWAC] Backstage - HTM to XLS Reports I completely agree with Linda--thanks so much! And kudos to Ted who's procedure had been critical to my work for years. --Martha Sent from my iPad On Oct 8, 2012, at 9:48 AM, Linda Ballinger <ballingerl@newberry.org> wrote: This is excellent news! Thank you for working on this. We've used Ted Waller's procedures for converting the reports to Excel, which he posted on this list a few years ago. Being able to go directly to Excel files would be such a time saver. Linda --------------- Linda Ballinger Principal Cataloging Librarian Newberry Library Chicago, IL ballingerl@newberry.org Nate Cothran wrote: 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 <snip> 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. _______________________________________________ BSLWAC mailing list BSLWAC@mailman.xmission.com http://mailman.xmission.com/cgi-bin/mailman/listinfo/bslwac
Thanks so much! I also like the option of looking at both formats. We have the work distributed here between staff, and this will be very helpful! Éva From: bslwac-bounces@mailman.xmission.com [mailto:bslwac-bounces@mailman.xmission.com] On Behalf Of Nate Cothran Sent: Monday, October 08, 2012 3:56 PM To: Backstage Library Works Authority Contol Listserv Subject: Re: [BSLWAC] Backstage - HTM to XLS Reports Thank you everyone for your feedback so far, it is encouraging. As we get this ready to send out to everyone (that wants it), we imagine we will need to make tweaks to the programming to work out any kinks that we haven’t come across yet or add in new functionality that could further reduce the time spent at your end. Also, since this is a conversion program from existing HTM reports, if you do wish to see both sets of reports (HTM + XLS), we are fairly certain we can make that happen. As I think about it, what will probably happen is we would generate one set of HTM reports and another corresponding set of XLS reports (for those HTM reports that we can convert, that is). That way, you still have the option to look at the HTM report when necessary. Our hope is to get this in place before the end of October, so we will keep you posted on our progress towards that goal. Nate Cothran Backstage Library Works From: bslwac-bounces+nate=bslw.com@mailman.xmission.com [mailto:bslwac-bounces+nate=bslw.com@mailman.xmission.com] On Behalf Of HELIN Sent: Monday, October 08, 2012 10:19 AM To: Backstage Library Works Authority Contol Listserv Cc: Backstage Library Works Authority Contol Listserv Subject: Re: [BSLWAC] Backstage - HTM to XLS Reports I completely agree with Linda--thanks so much! And kudos to Ted who's procedure had been critical to my work for years. --Martha Sent from my iPad On Oct 8, 2012, at 9:48 AM, Linda Ballinger <ballingerl@newberry.org<mailto:ballingerl@newberry.org>> wrote: This is excellent news! Thank you for working on this. We've used Ted Waller's procedures for converting the reports to Excel, which he posted on this list a few years ago. Being able to go directly to Excel files would be such a time saver. Linda --------------- Linda Ballinger Principal Cataloging Librarian Newberry Library Chicago, IL ballingerl@newberry.org<mailto:ballingerl@newberry.org> Nate Cothran wrote: 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 <snip> 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. _______________________________________________ BSLWAC mailing list BSLWAC@mailman.xmission.com<mailto:BSLWAC@mailman.xmission.com> http://mailman.xmission.com/cgi-bin/mailman/listinfo/bslwac
Hi, Judy, Happy Columbus Day! Would it be possible to have both? I would want the new report (000) to stay in HTML and the others in Excell. I can always ask ;) --Martha Sent from my iPad On Oct 5, 2012, at 5:31 PM, "Nate Cothran" <nate@bslw.com> wrote:
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
<R25 - Unrecognized $z Subfields.xls> _______________________________________________ BSLWAC mailing list BSLWAC@mailman.xmission.com http://mailman.xmission.com/cgi-bin/mailman/listinfo/bslwac
participants (4)
-
Bolkovac, Eva -
HELIN -
Linda Ballinger -
Nate Cothran