[SOLVED] Append Excel Files

After trying several times..to APPEND 2 EXCEL FILES TOGETHER.

I discover from the IBI site the following:
An EXCEL file must be created containing data from two files. FILEDEF
or ALLOCATE the EXCEL output with a DISP=MOD, (or APPEND depending on
environment) does not contain both answer sets, but only the second.

- Problem Section

A HOLD FORMAT EXCEL issues its own FILEDEF/ALLOCATE, over-riding the
instructions to APPEND

Solution:
use the MORE option of universal
concatenation to concatenate the input.
TABLE FILE FN
WRITE FIELD1....BY SORT1...
ON TABLE HOLD FORMAT EXCEL
MORE
FILE2
END

Now maybe it is just me...but how is that putting two excel sheets together?
Anyone have any thought or clues.

This message has been edited. Last edited by: Kerry,

In Focus since 1993. WebFOCUS 7.7.03 Win 2003

Posts: 1903 | Location: San Antonio | Registered: February 28, 2005

<JG>

posted

Prarie,

As you are a 5.35 site, using the compound report syntax for EXL2K should deliver what you want.

However to expand on the not so helpful hottrack solution that you found.

MOD or APPEND works to create a single output file to the destination dataset,
The problem is, for EXL2K output the format is actually extended html.

WebFocus correctly generates the output to a single file however Microsoft Excel cannot read what is delivered.

The reason is that WebFocus generates a single file with 2 complete documents in the 1 file and Excel reads the first and then stops.

Using more gives you a single output from two sources with exactly the same format.

It is very definitely NOT 2 different excel reports in the same document.

If you want to have multiple EXl2K Reports in the same sheet (pre EXL2K compound functionality) you must manipulate the output files to give you a single document with multiple HTML tables. (use a variation on my posting about Macro's in pre 7.1.

sample code is as follows.

 
FILEDEF EXCEL DISK EXCEL.MAS
-RUN
-*
-* write your dummy master to allow Focus to read the .XHT file as input
-*
-WRITE EXCEL FILE=EXCEL          ,SUFFIX=FIX
-WRITE EXCEL SEGNAME=EXCEL       ,SEGTYPE=S0
-WRITE EXCEL FIELDNAME   =DATALINE       ,E01         ,A256    ,A256     ,$
-RUN
TABLE FILE CAR
"Report 1"
PRINT MODEL
ON TABLE HOLD AS X FORMAT EXL2K
END
-RUN
TABLE FILE CAR
" "
"Report 2"
PRINT  MODEL
BY COUNTRY
ON TABLE HOLD AS Y FORMAT EXL2K
END
-RUN
FILEDEF Z DISK c:\ibi\apps\focalpoint\excelappend.xls (APPEND
FILEDEF EXCEL DISK x.xht
-RUN
DEFINE FILE EXCEL
WANTED/A1= IF EDIT(DATALINE,'9999999') EQ '</BODY>' THEN 'N'
            ELSE IF LAST WANTED EQ 'N' THEN 'N' ELSE 'Y';
END
TABLEF FILE EXCEL
PRINT DATALINE
WHERE WANTED EQ 'Y';
ON TABLE SAVE AS Z FORMAT ALPHA
END
-RUN
FILEDEF EXCEL DISK y.xht
-RUN
DEFINE FILE EXCEL
WANTED/A1= IF EDIT(DATALINE,'9999999') EQ '<TABLE' THEN 'Y'
            ELSE IF LAST WANTED EQ 'Y' THEN 'Y' ELSE 'N';
END
TABLEF FILE EXCEL
PRINT DATALINE
WHERE WANTED EQ 'Y';
ON TABLE SAVE AS Z FORMAT ALPHA
END
-RUN
-HTMLFORM BEGIN
<html>
<head>
<title>EXCEL2K Compound </title>
</head>
<frameset rows="*">
  <frame name="EXCEL2K" src="http://192.168.1.3:9080/approot/focalpoint/excelappend.xls" scrolling="auto" >
  <noframes>
  <body>
  <p>This page uses frames, but your browser doesn't support them.</p>
  </body>
  </noframes>
</frameset>
</html>
-HTMLFORM END

As I mentioned in the original posting this requires a permanent storage location that can be accessed by the webserver and so will need management.

JG

Prarie

Virtuoso

posted

Hide Post

Thanks JG...this looks good. No problem on the permanent place.

In Focus since 1993. WebFOCUS 7.7.03 Win 2003

Posts: 1903 | Location: San Antonio | Registered: February 28, 2005

Ignored post by Prarie posted

Show Post

rlmuell

Member

posted

Hide Post

JG, I have used your example code to append around 30 excel reports. Neat trick by the way!! It seemed to work great except it also generates around 350 extra sheets in the excel file.

In your example you only append two files together and your WANTED defines are different for each report. I'm thinking there needs to be yet a different kind of define for anything after 2 reports? To get it work I ended up using the one looking for the WANTED/A1= IF EDIT(DATALINE,'9999999') EQ '' and it retrieved all the different reports, but also added a ton of extra Recovered_Sheets. How did you decide what the WANTED defined needed to look for? I would appreciate any help you can give me!!

Thanks!!

Renee

WebFOCUS 7.6.10

Posts: 5 | Registered: May 06, 2003

Ignored post by rlmuell posted

Show Post

<JG>

posted

I was very neglectful of replying to Rlmuell on this because of workload.

Fortunately he sent a private message and brought his issue to my attention so that I could try and help.

To create a compound Excel up to the Microsoft limit of 64K the following is a variation on my original code and accommodates multiple excels in the same sheet and not just two.

________________________________________________________________________

For multiple Excel documents the important thing is only selecting the table components for the reports after the first document, except for the last where you need the closing body and html tags as well.

(slightly cryptic but you will see what I mean)

I have coded up an extended example that generates a single excel containing 30 reports.
(It's just a simple loop of the same static code for the example but all you need to do is have numbered reports and -INCLUDE them)

Notice the first part of the process is outside of the loop and the define in the loop
has been extended to ensure that we only get the closing body and html tags for the last report.

  
-* File excelappend.fex
FILEDEF EXCEL DISK EXCEL.MAS
-RUN
-*
-* write your dummy master to allow Focus to read the .XHT file as input
-*
-WRITE EXCEL FILE=EXCEL          ,SUFFIX=FIX
-WRITE EXCEL SEGNAME=EXCEL       ,SEGTYPE=S0
-WRITE EXCEL FIELDNAME   =DATALINE       ,E01         ,A256    ,A256     ,$
-RUN

FILEDEF Z DISK c:\ibi\apps\focalpoint\excelappend.xls (APPEND
-RUN
TABLE FILE CAR
"Report 1"
PRINT MODEL
ON TABLE HOLD AS X FORMAT EXL2K
END
-RUN
FILEDEF EXCEL DISK x.xht
-RUN
DEFINE FILE EXCEL
WANTED/A1= IF EDIT(DATALINE,'9999999') EQ '</BODY>' THEN 'N'
            ELSE IF LAST WANTED EQ 'N' THEN 'N' ELSE 'Y';
END
TABLEF FILE EXCEL
PRINT DATALINE
WHERE WANTED EQ 'Y';
ON TABLE SAVE AS Z FORMAT ALPHA
END
-RUN
-SET &REPORT =1;
-*********************************************************************************
-* At this point you do a -INCLUDE REPORT&REPORT NOT just a loop on sataic code. The 29 is because this 
-* is what was needed for the requirement. There is no maximum Except respecting the Microsoft 64K rows limit.
-*********************************************************************************
-REPEAT ENDREPEAT 29 TIMES
-SET &REPORT =&REPORT +1;
TABLE FILE CAR
" "
"Report &REPORT"
PRINT  MODEL
BY COUNTRY
ON TABLE HOLD AS Y FORMAT EXL2K
END
-RUN

FILEDEF EXCEL DISK y.xht
-RUN
DEFINE FILE EXCEL
WANTED/A1= IF EDIT(DATALINE,'9999999') EQ '<TABLE' THEN 'Y' ELSE
            IF (&REPORT EQ 30) AND (LAST WANTED EQ 'Y') THEN 'Y'  ELSE
            IF (LAST WANTED EQ 'Y')  AND (EDIT(DATALINE,'9999999') EQ '</BODY>') THEN 'N' ELSE
             IF LAST WANTED EQ 'Y' THEN 'Y' ELSE 'N';
END
TABLEF FILE EXCEL
PRINT DATALINE
WHERE WANTED EQ 'Y';
ON TABLE SAVE AS Z FORMAT ALPHA
END
-RUN
-ENDREPEAT
-*********************************************************************************

-HTMLFORM BEGIN
<html>
<head>
<title>EXCEL2K Compound </title>
</head>
<frameset rows="*">
-***************************************************************************************************
-*     REMBER use your server address
-***************************************************************************************************
  <frame name="EXCEL2K" src="http://localhost/approot/focalpoint/excelappend.xls" scrolling="auto" >
  <noframes>
  <body>
  <p>This page uses frames, but your browser doesn't support them.</p>
  </body>
  </noframes>
</frameset>
</html>
-HTMLFORM END

Hope it helps others.

This message has been edited. Last edited by: <JG>,

rlmuell

Member

posted

Hide Post

JG,

Your solution worked great for me. I am now only generating one worksheet on the excel.

Thanks!
Renee

WebFOCUS 7.6.10

Posts: 5 | Registered: May 06, 2003

Ignored post by rlmuell posted

Show Post

gregson06

Platinum Member

posted

Hide Post

Another solution, it may already be posted, but you can also use the following script at the top of each report that is in format EXL2K. This worked extremely well for me:

First report:

SET COMPOUND = 'OPEN NOBREAK'
[REPORT TEXT]
ON TABLE PCHOLD FORMAT EXL2K
END

Second report:

SET COMPOUND = NOBREAK
[REPORT TEXT]
ON TABLE PCHOLD FORMAT EXL2K
END
.
.
.
Last report:
SET COMPOUND = CLOSE
[REPORT TEXT]
ON TABLE PCHOLD FORMAT EXL2K
END

Again this works very well for multiple report, I created a report with more than 5 reports and it all fit within the limit of the Excel rows (remember, Excek only allows 65536 rows of data!)

However, I would like to know if there is a way to create a report that outputs multiple Excel "files", or at least multiple reports in one "file" but separate "sheets". Unless all reports are in exactly the same format, I really don't see a major advantage of exporting multiple files into one Excel file on the same "sheet".

Thanks!

Posts: 87 | Registered: August 03, 2006

Ignored post by gregson06 posted

Show Post

awbradley

Member

posted

Hide Post

This worked like a charm. Thank you.

Annette Bradley
Lead Systems Analyst/Programmer

Prod: WebFOCUS 7.1.0
Test: WebFOCUS 7.1.0

Posts: 26 | Location: Little Rock, AR | Registered: October 29, 2004

Ignored post by awbradley posted

Show Post