Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED]FILEDEF and corrupted XLSX
Go
New
Search
Notify
Tools
Reply
  
[SOLVED]FILEDEF and corrupted XLSX
 Login/Join
 
Silver Member
posted
Hey everyone, I am using the FILEDEF command to send a report to a specified location, but each time I open the file in excel, Excel tells me it is corrupted.

If I specify the file as something other than xlsx it works properly. The problem is that I really need it as xlsx. Can anyone help?

So this doesn't work:
 APP PATH IBISAMP

-UNIX rm /filepath/cartest.xlsx
FILEDEF CARSEND DISK /filepath/cartest.xlsx
-RUN

TABLE FILE CAR
PRINT *
WHERE RECORDLIMIT EQ 20
ON TABLE HOLD AS CARSEND FORMAT XLSX
END 


But this does:
 APP PATH IBISAMP

-UNIX rm /filepath/cartest.csv
FILEDEF CARSEND DISK /filepath/cartest.csv
-RUN

TABLE FILE CAR
PRINT *
WHERE RECORDLIMIT EQ 20
ON TABLE HOLD AS CARSEND FORMAT COMT
END 


Any ideas why the FILEDEF would have problems with XLSX and not COMT?

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


WebFOCUS 8.105M, Windows 10, App Studio
 
Posts: 42 | Registered: October 22, 2014Reply With QuoteReport This Post
Virtuoso
posted Hide Post
This worked fine for me:
  
-SET &ECHO=ALL;
-* File rray01.fex
FILEDEF CARSEND DISK BASEAPP/CARSEND.XLSX
TABLE FILE CAR
SUM SALES RCOST DCOST
BY COUNTRY BY CAR BY BODYTYPE
ON TABLE HOLD AS CARSEND FORMAT XLSX
END 


I see you are on a UNIX box.
Could this be the reason?
Did you try using FORMAT EXL2K or EXL07?


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1936 | Location: Tel Aviv, Israel | Registered: March 23, 2006Reply With QuoteReport This Post
Expert
posted Hide Post
Also try adding this SET command. May help.

SET EXCELSERVURL = ''


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.06OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6131 | Location: 33.8688° S, 151.2093° E | Registered: October 31, 2006Reply With QuoteReport This Post
Silver Member
posted Hide Post
quote:
Originally posted by Danny-SRL:
This worked fine for me:
  
-SET &ECHO=ALL;
-* File rray01.fex
FILEDEF CARSEND DISK BASEAPP/CARSEND.XLSX
TABLE FILE CAR
SUM SALES RCOST DCOST
BY COUNTRY BY CAR BY BODYTYPE
ON TABLE HOLD AS CARSEND FORMAT XLSX
END 


I see you are on a UNIX box.
Could this be the reason?
Did you try using FORMAT EXL2K or EXL07?

I tried EXL2k and EXL07 with the file extension of .xls in the FILEDEF path, but got the same error on opening in Excel:

The file format and extension of 'carsend.xls' don't match. The file could be corrupted or unsafe. Unless you trust its source, don't open it. Do you want to open it anyways?

What it looks like when you open it.

quote:
Originally posted by Waz:
Also try adding this SET command. May help.

SET EXCELSERVURL = ''


Included this in the code, but it didn't make a difference.


WebFOCUS 8.105M, Windows 10, App Studio
 
Posts: 42 | Registered: October 22, 2014Reply With QuoteReport This Post
Expert
posted Hide Post
What version of Excel are you using?
Also, do you have these settings on your admin console?

Redirection Setting XLSX


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1958 | Location: Centennial, CO | Registered: January 31, 2006Reply With QuoteReport This Post
Silver Member
posted Hide Post
quote:
Originally posted by Tom Flynn:
What version of Excel are you using?
Also, do you have these settings on your admin console?

Redirection Setting XLSX


 APP PATH IBISAMP

SET EXCELSERVURL = ''

-UNIX rm filepath/cartestxlsx.xlsx
FILEDEF CARSEND DISK   filepath/cartestxlsx.xlsx
-RUN

TABLE FILE CAR
PRINT *
WHERE RECORDLIMIT EQ 20
ON TABLE HOLD AS CARSEND FORMAT XLSX
END


Redirection settings for XLSX

I have Excel 2016 as my version.


WebFOCUS 8.105M, Windows 10, App Studio
 
Posts: 42 | Registered: October 22, 2014Reply With QuoteReport This Post
Expert
posted Hide Post
Are you trying to open this via Dev Studio? You have to right-click and Edit in Excel.

OR

Use the example that Danny-SRL gave, go to your C drive/IBI/APPS/BASEAPP and double-click from there; bet it opens fine...


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1958 | Location: Centennial, CO | Registered: January 31, 2006Reply With QuoteReport This Post
Silver Member
posted Hide Post
I'm working with our IT staff after reading this. WebFOCUS is hosted offsite, and the filepath is actually a mount point. From the mount point, the file is FTP-ed over to a network location on my local network. The file I am trying to open is the one that is FTP-ed over. I'm going to see if the IT staff at the location can open the file at the mount point to find out where the corruption is happening.

It sounds like something may be broken with the file transfer. I don't know why it would handle CSV and TXT files perfectly, but have issues with XLSX. I will update this thread with whatever information I get from the IT staff.


WebFOCUS 8.105M, Windows 10, App Studio
 
Posts: 42 | Registered: October 22, 2014Reply With QuoteReport This Post
Silver Member
posted Hide Post
We got the issue resolved. I asked the IT staff what the problem was and they indicated that it related to "Ascii/Binary" detection. Not sure what that means, but I'm leaving it here just in case it can help any future person who encounters this weird issue.

Thank you all for your help!


WebFOCUS 8.105M, Windows 10, App Studio
 
Posts: 42 | Registered: October 22, 2014Reply With QuoteReport This Post
Expert
posted Hide Post
My guess is that the FTP step transferred the file as text not binary.

That would definitely corrupt an xlsx file.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.06OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6131 | Location: 33.8688° S, 151.2093° E | Registered: October 31, 2006Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED]FILEDEF and corrupted XLSX

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.