Subfiles to Excel is OK. But why not use ... Excel's Pivot Tables !

[Seamus at partage.org]

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