In message <[log in to unmask]>, John
Pitman <[log in to unmask]> writes
>Why not just have them print to a suspended spool device, and send them the
>spool file?
>We have a process where we configure a printer (ldev 74) as a suspended
>spooler, and on hp is a posix job that looks for this device in out.hpspool,
>copies it to posix, ftps it to a PC running Bullet proof ftp surfer, in a
>directory that reelects the user's logon and account (eg fred.sales), and
>they wait a few minute after running report, download, do whatever they
>need. Importing into xl is pretty easy really.
>
>jp
>
>-----Original Message-----
>From: HP-3000 Systems Discussion [mailto:[log in to unmask]]On Behalf Of
>Tim Valder
>Sent: Wednesday, September 29, 2004 8:55 AM
>To: [log in to unmask]
>Subject: [HP3000-L] Email Reports as Excel Attachments from HP3K
>
>I am continually deluged with users asking "can you send me that report in
>Excel?". Well, I can, but not without getting involved in converting it.
>I believe I remember from a former life that there are products that will
>run from the HP3000 to do just that. Can someone suggest such a product or
>download. TIA. --Tim
For the past couple of years, I've had the weekly task of distributing
copies of our updated Parts Catalogue to five or six of our major
customers.
The data lives on a TurboImage database on the HP3000, and the output
file is emailed as a SYLK (comma separated values) file, which can be
read straight into Excel by the recipients without an import stage at
all; the file opens just as if it had been sent as an .xls.
The software that accomplishes this is M B Foster's UDALink, where we
have set up a procedure to extract the data - which involves linking
across three different datasets, on two different keys - present it as a
SYLK file, one of UDALink's many standard output options, and download
it to the PC which is running the procedure, via Reflection.
I invoke the procedure, confirm that Reflection can overwrite the
previous file when it asks, and that's it. Then I just insert the file
into a pre-prepared email template with the customers as Bcc: addresses,
and Send it.
That's the easiest way I know, although - as you will see if you read my
article in the upcoming NewsWire - I'm a big fan of Excel's powerful
'Text to Columns' feature for reformatting from non-csv files.
However, either of these methods has something of a gotcha unless you
are talking about specifically written data-only outputs. Any reasonably
complex formatted report that was intended for human intake is likely to
require some serious massaging before the data can be used from Excel.
Firstly, it has to be cut back to just the detail lines, dropping
headers and footers, subtotals and totals. Then you find that, for
instance in a multi-location stock report, the Part Number is only shown
once, at the start of its entries and on each new page of them, and
suppressed against all the repeat entries that differ only on Location.
Worse, there may be a Category split that only appears in a control
break, and not on a detail line at all, and yet you need it on each
detail line for a file that you can use in Excel.
I'm sure that most of your users could spend many a happy hour fixing
this, each time they get such a report, but there is a product that will
do this automatically - Monarch, from Datawatch:
http://monarch.datawatch.com/monarch-report-mining.asp
I call it the Unprogramming tool - it's kind of weird to watch it taking
apart a lovingly formatted report, and spitting out the raw data it was
built from.. :-)
But it works, and it's very powerful. One time I used it, years back,
was as a stopgap when two inventory systems were to be integrated, and
it was clear that the interface wouldn't be ready on time. All that was
available was a user-oriented print report of the inventory in that
second system, one with all the issues outlined above, and more. But we
were able to get this as a file, at least, and Monarch let us rewrite
this to a file that met the spec of what was planned to come over the
interface. I'm not sure that the planned interface ever did get
built....
(NB: No <plugs> needed, I hope, as I have no financial incentives to
push the above products - just a happy user of them)
--
Roy Brown 'Have nothing in your houses that you do not know to be
Kelmscott Ltd useful, or believe to be beautiful' William Morris
* To join/leave the list, search archives, change list settings, *
* etc., please visit http://raven.utc.edu/archives/hp3000-l.html *
|
