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,

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?

Also try adding this SET command. May help.

SET EXCELSERVURL = ''

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.

What version of Excel are you using?
Also, do you have these settings on your admin console?

Redirection Setting XLSX

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.

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...

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.

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!

My guess is that the FTP step transferred the file as text not binary.

That would definitely corrupt an xlsx file.