Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] EXL2K BYTOC

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] EXL2K BYTOC
 Login/Join
 
Alan
Gold member
posted
I copied the following code directly from the manual. Please note that I am running on MVS using FOCUS 7.6.4 and want to generate multiple worksheets. Also notice that I'm using HOLD instead of PCHOLD and transferring the file later. If I run without BYTOC I get a single worksheet (as expected); when I add BYTOC and download the file, Excel says "Unable to read file." I'm using Excel 2002. Any suggestions?

SET COMPOUND=BYTOC
TABLE FILE CAR
PRINT SALES BY COUNTRY NOPRINT BY CAR
ON TABLE HOLD AS EXCEL FORMAT EXL2K
END

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


WF 7.6.6, FOCUS 7.6.4, IBM MVS/TSO, Windows 2003 Server, DB2, MSSQL
 
Posts: 65 | Location: Chicago, IL | Registered: July 26, 2007Report This Post
njsden
Virtuoso
posted Hide Post
As far as I know, that functionality is only available when using Excel 2003.

I'll try to look for the exact piece of documentation and will post it later.

- Neftali.


Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
Alan
Gold member
posted Hide Post
Note: This feature can be used only with Excel 2002 or higher releases because it
requires the Web Archive file format, which was not available in Excel 2000 and earlier
releases.


WF 7.6.6, FOCUS 7.6.4, IBM MVS/TSO, Windows 2003 Server, DB2, MSSQL
 
Posts: 65 | Location: Chicago, IL | Registered: July 26, 2007Report This Post
njsden
Virtuoso
posted Hide Post
Sorry! My memory is not serving me well Red Face . Excel 2002 should be supported!

quote:

Note: This feature can be used only with EXCEL 2002 or higher releases because it requires the Web Archive file format, which was not available in Excel 2000 and earlier releases.



I don't have access to Excel 2002 so I am not able to replicate you case Frowner


Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
njsden
Virtuoso
posted Hide Post
Hey!! Did we just find the same piece of documentation? Smiler


Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
Alan
Gold member
posted Hide Post
Neftali,
This is such a simple technique for generating multiple worksheets, and there's not a lot of documentation to read. Everything says it should just work.

I also read thru all the FocalPoint stuff but most of that deals with using PCHOLD -- not quite the same thing.

Thanks for the feedback!


WF 7.6.6, FOCUS 7.6.4, IBM MVS/TSO, Windows 2003 Server, DB2, MSSQL
 
Posts: 65 | Location: Chicago, IL | Registered: July 26, 2007Report This Post
njsden
Virtuoso
posted Hide Post
Weird!

I'm seeing something similar in my environment.

This is the code I'm running against by WF 5.3.3 Server (on Unix):

SET COMPOUND=BYTOC
TABLE FILE CAR
PRINT SALES BY COUNTRY NOPRINT BY CAR
ON TABLE HOLD AS EXCEL FORMAT EXL2K
END
-RUN
-UNIX excel.xht /tmp/.


I then transferred "excel.xht" to my local Windows XP desktop. When attempting to open it in Excel 2003 I get:

quote:

XML Parsing Error: syntax error
Location: file:///H:/excel.xht
Line Number 1, Column 1:MIME-Version: 1.0


I tried copying the file in both Text and Binary formats from my FTP client but found no difference whatsoever. Confused


Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
njsden
Virtuoso
posted Hide Post
Hmmm, there seems to be much more to this. After the HOLD file is created, there are a set of *.xht files in the temporary location created for my session.

Those are:
19216 Jan 26 15:08 excel.xht
 3503 Jan 26 15:08 ibit0001.xht
 3310 Jan 26 15:08 ibit0002.xht
 3497 Jan 26 15:08 ibit0003.xht
 3386 Jan 26 15:08 ibit0004.xht
 3653 Jan 26 15:08 ibit0005.xht


If I peek into "excel.xht" I can see the following at the very beginning of the file:

MIME-Version: 1.0
X-Document-Type: Workbook
Content-Type: multipart/related; boundary="====Boundary===="

--====Boundary====
Content-Location: file:///C:/EXCEL.XHT
Content-Transfer-Encoding: 8bit
Content-Type: text/html; charset="us-ascii"


<HTML xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns="-//W3C//DTD HTML 4.0//EN">
 
<HEAD>
<meta name="Excel Workbook Frameset">
 
<xml>
 <x:ExcelWorkbook>
  <x:ExcelWorksheets>
   <x:ExcelWorksheet>
    <x:Name>ENGLAND</x:Name>
    <x:WorksheetSource HRef="./IBIT0001.xht"/>
   </x:ExcelWorksheet>
   <x:ExcelWorksheet>
    <x:Name>FRANCE</x:Name>
    <x:WorksheetSource HRef="./IBIT0002.xht"/>
   </x:ExcelWorksheet>
   <x:ExcelWorksheet>
    <x:Name>ITALY</x:Name>
    <x:WorksheetSource HRef="./IBIT0003.xht"/>
   </x:ExcelWorksheet>
   <x:ExcelWorksheet>
    <x:Name>JAPAN</x:Name>
    <x:WorksheetSource HRef="./IBIT0004.xht"/>
   </x:ExcelWorksheet>
   <x:ExcelWorksheet>
    <x:Name>W GERMANY</x:Name>
    <x:WorksheetSource HRef="./IBIT0005.xht"/>
   </x:ExcelWorksheet>
  </x:ExcelWorksheets>
 </x:ExcelWorkbook>
</xml>
</HEAD>
</HTML>


There we can notice the physical reference to C:/EXCEL.XHT in addition to each of the additional files.

I'll transfer them all to my local PC and will put them in C:\ to see what difference that makes.

- Neftali.


Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
njsden
Virtuoso
posted Hide Post
Well, this will sound kind of silly but hey, we all have to learn somehow.

Those "ibi*.xht" files are not really required. Whenever I attempted to open "excel.xht" I was getting the weird XML Parser error; but then I did things in a different order ... I started Excel first and from within I asked it to open "excel.xht" and guess what? I does work!

I think what I'll need to do is to just create an association between .XHT and Excel in my Windows desktop so I can open it directly.

Could that be your case?

- Neftali.


Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
njsden
Virtuoso
posted Hide Post
Alan,

Please take a look at this: http://techsupport.information...om/sps/60132012.html

It might be what we were missing.

Regards,
Neftali.


Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
Alan
Gold member
posted Hide Post
Neftali,
You must be running WebFOCUS instead of FOCUS. I'm on MVS running FOCUS and pre-allocating the HOLD file. I don't think MVS FOCUS creates more than one xls file (if it does, I'm not aware of it).

Of course, that just may be the problem...

Alan


WF 7.6.6, FOCUS 7.6.4, IBM MVS/TSO, Windows 2003 Server, DB2, MSSQL
 
Posts: 65 | Location: Chicago, IL | Registered: July 26, 2007Report This Post
Alan
Gold member
posted Hide Post
However, I'm looking at the FOCUS for Mainframe manual...


WF 7.6.6, FOCUS 7.6.4, IBM MVS/TSO, Windows 2003 Server, DB2, MSSQL
 
Posts: 65 | Location: Chicago, IL | Registered: July 26, 2007Report This Post
njsden
Virtuoso
posted Hide Post
I have never worked with FOCUS on MVS so I don't know which "particularities" should be considered.

However, based on your comment:

quote:

I don't think MVS FOCUS creates more than one xls file ..


I think I may know what the issue might be. When you create a regular HOLD file in EXL2K the engine creates a file with ".xls" extension. However, when using BYTOC, [Web]FOCUS no longer produces an Excel file but an XML file instead and it now uses the ".xht" extension.

If you are pre-allocating your HOLD file and are explicitly using .xls that could be the reason why Excel cannot understand the file. It sees ".xls" and therefore expects a file in native Excel format, not in XML.

To make matters simple and since I pressume you already have a local copy of the file in your PC, can you try renaming the file replacing its extension by ".xht" and later attempting to open it from with Excel and see what happens?


Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
Alan
Gold member
posted Hide Post
Neftali,
You are absolutely correct! MVS FOCUS generates an xml files instead of the Web Archive format described in the manual (or is that what Web Archive means?). I had pre-allocated the file with a name of .xls and transferred it to my PC. I changed the name to .xht and ran again, then transferred to my PC and got the same results.

Turns out that by initiating FTP from the mainframe some hidden characters are embedded into the .xht file that cause a problem. When I use FTP client from my PC the problem goes away. Now all I have to do is capture the FTP commands to find out what option it's using.

Thanks for your help with this!

Alan


WF 7.6.6, FOCUS 7.6.4, IBM MVS/TSO, Windows 2003 Server, DB2, MSSQL
 
Posts: 65 | Location: Chicago, IL | Registered: July 26, 2007Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] EXL2K BYTOC

Copyright © 1996-2020 Information Builders