why not use ... Excel's Pivot Tables or ODBC driver??

[Paul (Pablo) Grim]

In reference to this topic, I'd like to recommend the CONNX ODBC driver for
remote access to various databases and flat files.  This will allow real
time access to the actual data rather than making a copy of the data and
downloading it.

We use the product on the Alpha platform to access RMS files via Microsoft
Access, Query, Excel, etc...  The really cool thing is that CONNX is set up
to read and understand Powerhouse PDL files so this makes the system
administration a piece of cake.  You create your PDL on the server (Alpha),
then "import" the PDL to the CONNX administrator software on the PC.  The
software then automatically creates a CDD file for SQL acess on your PC.
It's a mature, efficient (at least on the Alpha), reliable, and well
supported product.  I think we paid about 2K for it.  It's a simple and
cheap way to give your users some GUI access to legacy text based data
warehouses.

Seamus, do these Pivot Tables read from the original file on the server or
from a copy of the file on the client?

p

> From: "Seamus at partage.org" <Seamus.Browne@partage.org>
> Date: Sat, 13 Jul 2002 16:22:28 +0200
> To: <powerh-l@cube.swau.edu>
> Subject: Subfiles to Excel is OK. But why not use ... Excel's Pivot Tables !
> 
> I am amazed that no mention is made of Pivot tables when the discussion gets
> round to subfiles and Excel.
> 
> I find that Excel Pivot tables are the best (and cheapest) for
> **automatically** presenting data extracted by PH.
> I've been told that it's a bit like Impromptu. But you don't have buy it if
> you already have Excel. (And who doesn't ?)
> 
> In our place, not only do we shift the data from PH subfiles to Excel, we
> have set up Excel to automatically use Pivot Tables to automatically re-read
> the data file and automatically present it in an intelligible form.
> 
> I did post a message about this a year ago and - another time - about a year
> before that.
> But nobody took any notice.
> 
> Was it because not many people need to do that ?
> Was it because it was an old trick ?
> Or was it because it was too new ?
> Or was it because nobody knows what I'm talking about ?
> Anyway, I gave up posting messages on that subject because I got zero
> feedback.
> 
> But, now the talk has got back to Excel, here I go again...
> So, what's it all about ?
> 
> Well this is what we do, keeping it short and uncomplicated.
> 
> The fist bit is to get the PH data file in the correct shape on the big
> machine.
> We're on a HP3000 so we run the extraction in Quiz and transfer the data
> file
> to the PC by FTP.
> 
> 
> !JOB JPARTS ... etc
> :QUIZ
> 
> ACC PARTS
> SET REP DEV DISC NAME LISTPART
> SET PAGE LEN 0 WID 250
> SET REP SPA 0
> SET NOHEAD
> 
> DEF HEADER CHAR * 40 = &,
> ("PART-NAME;PART-NUMBER;PART-ORIGIN;PART-PRICE;PART-DATE;X")
> 
> DEF PACKET CHAR * 240  =              &,
> PACK  (PART-NAME            + ";" + &,
> ASC(PART-NUMBER,6)       + ";" + &,
> PART-ORIGIN          + ";" + &,
> ASC(PART-PRICE,10[1:8])  + "." + &,
> ASC(PART-PRICE,10[9:2])  + "." + &,
> ASC(PART-DATE,8[7:2])    + "/" + &,
> ASC(PART-DATE,8[5:2])    + "/" + &,
> ASC(PART-DATE,8[1:4])    + ";X" )
> 
> ;In the PACKET you have an example of how to deal with
> ;character fields, numeric fields and date fields.
> ;
> ;PART-NAME and PART-ORIGIN as examples of straight character data,
> ;
> ;PART-NUMBER which has to be ASCII-fied for the PACK function to work,
> ;PART-NUMBER,6 to make sure that the whole field (length 6) is read.
> 
> ;PART-PRICE,10 to make sure that the whole field (length 10) is read
> ;and then sectionned into two bits with a "." to separate the units from the
> cents.
> 
> ;PART-DATE,8 to make sure that the whole field (length 8) is read.
> ;Then with "/" as separators for the date.
> ;Note that you may have to modify this if the date you want is in
> ;MM/DD/YYYY and not DD/MM/YYYY.
> ;
> ;The "X" field is a dummy field, just to be safe, because sometimes you can
> get
> ;trailing blanks in the output subfile if the last field is numeric.
> ;
> ;Then ...
> 
> SET REPORT LIMIT nnnn
> 
> INITIAL HEADING HEADER
> REPORT SUMMARY PACKET
> 
> GO
> EXIT
> 
> ; Then, if you're on two different platforms, before you end the job,
> ; run FTP to transfer the data file, otherwise Copy
> 
> :FTP
> OPEN target-server
> USER USERNAME
> PASS PASSWORD
> PUT LISTPART LISTPART.TXT
> QUIT
> 
> !EOJ ('END OF JOB)
> 
> The Excel side of it looks a bit tricky to set up but no mre tricky that
> anything else.
> I have 15 or 20 jobs that run like this every night and dump data files on
> the PC server.
> Things to remember - or master - if this is to work.
> 
> 1. Dump the data files all in the same folder on the PC server.
> Share that folder. Call it XLDATA or something significant.
> 
> 2. To get Excel to automatically read the files, create another folder and
> call it XLViewers.
> This is where you have to save the Excel files that you will make for the
> users to view the data.
> 
> 3. On any user PC that wants to view the data you have to map a network disk
> to the XLDATA folder.
> You can do this with the Explorer and check the box so that the mapped disk
> will be re-connected on startup every time.
> You too will have to have a mapped disk connection to that same XLDATA
> folder and everybody in the Workgroup/Company/Domain will have to have the
> **same letter** mapped to the same folder. This is important.
> This can be included in a login profile command file :
> E. g. "Net use X: \\ DATASERVER\ XLDATA"
> 
> 
> 4. You set up the Excel file that will view the data. Yes, a bit like
> Impromptu.
> I will not include a step by step on that because most of it is obvious if
> you have an idea what a Pivot Table is and does.
> If you haven't figured out what a Pivot Table does, read some help files or
> scoot over to Google. I've been told that it's more or less like Impromptu.
> 
> 5. The important thing is when you start setting up the pivot table, Excel
> will ask where the data is.
> The default setting is an Excel list. Well that's NOT OK. And don't copy
> paste the data into an Excel spreadsheet either. This is where you tell
> Excel that the data is on an "external data source".
> (I'm sorry if I don't have the exact term. My Excel is in French !)
> And this is where you tell Excel to use the mapped disk connection
> mentionned in point 3. You can't just navigate to the folder. It has to be a
> folder referred to as a disk. So use the letter that was used in point 3,
> which should be pointing to the folder mentionned in point 1. (!)
> 
> 6. Then there's a question about do you wan't to use MS Query to import the
> data using ODBC. The answer is "yes".
> Then there's questions like
> Do you want to create a data source? Answer NO.
> Which driver ? There should be a dropdown list with "MS Text files". That's
> the one to click.
> 
> If you can't see that you have to quit and install the "MS Office Text
> Importers and Converters and ODBC stuff" from the Control Panel, Add /
> Remove Programmes, MS Office, Details or Options or ...
> 
> The iportant thing here is that you don't have to create a "dsn".
> 
> In MS query : Menu, Add table, .. navigate to X: (Yes, the mapped disk
> mentionned in point 3. )
> You should see the data file LISTPART.TXT. Click to Add. Then close the
> dialog box.  Import the entire table using the asterisk, drag and drop.
> Then in the File Menu, Close and Send the data back to Excel.
> 
> 7. Then in Excel you will find yourself back in the Pivot table wizard. Mind
> how you specify the columns, numeric, character and date. Go to the end of
> the Wizard.
> 
> 8. Then you can play around until the data is how you want: Coumns,
> headings, data .. Options..
> Right click on the data to get back to the Wizard.
> 
> 9. Now when you're ready to Quit Excel, two things to remember.
> Save the file as a template, that way the users who mess up your
> presentation can't save thier "preferences" to the file.
> When you try to save the file as a Excel template, Excel will direct you to
> the default template folder which may (or may not) be where you wan't to
> save it. I save all our "Data Viewers" in the XLViewers folder mentionned in
> point 2.
> 
> 10. As you try to quit, Excel realises that the Pivot Table contains
> External data. So - and this is smart - asks the question "Do you want Excel
> to empty the data and re-read the external data file next time someone opens
> this file ?"
> Reply ... Yes! OK !
> 
> 
> The moral of the story is that the job or stream on the big machine can
> update the data file every night, or every 10 minutes, and the Excel data
> can be
> - manually refreshed if the file is already open, (right click on data,
> refresh)
> - automatically refreshed with a looping macro, if the file is already open
> - automatically refreshed each time a user opens the file, once a day, once
> a month, ...
> 
> That's enough for now.
> But then you can add graphs too.
> 
> 
> Seamus
> 
> 
> 
> 
> 
> = = = = = = = = = = = = = = = = = = = = = = = = = = = =
> Mailing list: powerh-l@lists.swau.edu
> Subscribe: "subscribe" in message body to powerh-l-request@lists.swau.edu
> Unsubscribe: "unsubscribe" in message body to powerh-l-request@lists.swau.edu
> http://lists.swau.edu/mailman/listinfo/powerh-l
> This list is closed, thus to post to the list you must be a subscriber.