topic Re: Hi All, Thanks for your quick response. I am using EXCEL... in ODS and Base Reporting

Excel file warning - different format than file extension

saikiran_nemani — Wed, 24 Jul 2019 17:15:27 GMT

Recently I am facing an Issue with Excel when we Write an ODS Statement .When the Program is completed and when I receive an email and open that excel file I am getting the below Error:

the file you are trying to open is in a different format than specified by the file extension .

Re: Regarding SAS Issue

Tom — Wed, 24 Jul 2019 16:20:11 GMT

That means that the extension on the file does not match the content. Your system is warning you that someone might be trying to fool you into opening a file that might be dangerous.

If you use ODS EXCEL then the extension on the file should be XLSX as that is the format it generates.

If you use ODS TAGSETS=excelxp then the extension on the file should be XML as that is the format it generates.

If you use ODS CSV then extension is CSV.

If you use ODS HTML then extension is HTML.

etc.

Re: Regarding SAS Issue

VDD — Wed, 24 Jul 2019 16:20:23 GMT

show your exporting code would help.

are you exporting to xls using excelcs?

Hi All, Thanks for your quick response. I am using EXCEL...

saikiran_nemani — Wed, 24 Jul 2019 16:58:17 GMT

Hi All,

Thanks for your quick response.

I am using EXCEL 2010 but the code is saying that .xls

For 2007 above versions we need to keep .xlxs extension ?

Re: Hi All, Thanks for your quick response. I am using EXCEL...

Kurt_Bremser — Wed, 24 Jul 2019 17:02:52 GMT

You can do a simple test: if you can open the file with some kind of zip program (winzip, 7zip) and see content, then it should have the .xlsx extension.

Re: Hi All, Thanks for your quick response. I am using EXCEL...

Reeza — Wed, 24 Jul 2019 17:12:13 GMT

It depends on the code you used to export, not the version of Excel.

Re: Excel file warning - different format than file extension

ballardw — Wed, 24 Jul 2019 17:25:05 GMT

@saikiran_nemani wrote:

Recently I am facing an Issue with Excel when we Write an ODS Statement .When the Program is completed and when I receive an email and open that excel file I am getting the below Error:

the file you are trying to open is in a different format than specified by the file extension .

ODS statement code please.

Re: Hi All, Thanks for your quick response. I am using EXCEL...

Cynthia_sas — Wed, 24 Jul 2019 18:09:04 GMT

Hi:
@Reeza is correct. The file extension you use will depend on the ODS statements. For example, if you are using TAGSETS.EXCELXP, the correct file extension is .XML, NOT .XLS - otherwise, Excel will complain with exactly the message you describe.

My recommendation is that you should change the code and use the file extensions shown in Reeza's post. Or else, change the registry, as described in this Tech Support note: http://support.sas.com/kb/31/956.html .

Hope this helps,
Cynthia

Re: Excel file warning - different format than file extension

saikiran_nemani — Thu, 25 Jul 2019 08:51:19 GMT

ODS listing;

ODS HTML FILE='/sas/sai/repeat_faults2.xls;

ODS HTML CLose;

Re: Excel file warning - different format than file extension

Kurt_Bremser — Thu, 25 Jul 2019 09:00:26 GMT

You try to lie to Excel, disguising a HTML file as an Excel file, and Excel rightfully complains about this.

Use the proper extension:

ODS HTML FILE='/sas/sai/repeat_faults2.html';

and open the file from within Excel (do not double-click the file in Explorer).

Re: Excel file warning - different format than file extension

saikiran_nemani — Thu, 25 Jul 2019 09:06:42 GMT

@Kurt Bremser :

I need to export the file in excel.

Please give any suggestions.

Re: Excel file warning - different format than file extension

Kurt_Bremser — Thu, 25 Jul 2019 09:11:43 GMT

Excel can read html files with tables.

Otherwise, use the correct ODS destination:

ODS EXCEL FILE='/sas/sai/repeat_faults2.xlsx';

as @Reeza already told you.

Re: Excel file warning - different format than file extension

Reeza — Thu, 25 Jul 2019 15:16:44 GMT

What version of SAS are you using?

Re: Excel file warning - different format than file extension

saikiran_nemani — Thu, 25 Jul 2019 15:45:22 GMT

SAS 9.3 and excel oss is not working

Re: Excel file warning - different format than file extension

Reeza — Thu, 25 Jul 2019 15:46:52 GMT

No, ODS EXCEL isn't available in 9.3.
ODS TAGSETS.EXCELXP is as close as you can get and you need to use the XML extension. It does most of what ODS EXCEL does, just doesn't give a native Excel file. You could convert it after the fact, there's a vbs macro that does that on sas support website.

Re: Excel file warning - different format than file extension

Kurt_Bremser — Thu, 25 Jul 2019 15:46:56 GMT

@saikiran_nemani wrote:
SAS 9.3 and excel oss is not working

Then you should use tagsets.excelxp and a .xml extension.

Re: Excel file warning - different format than file extension

Tom — Thu, 25 Jul 2019 15:51:43 GMT

You should also ask yourself (or your company) why you are still using a version of SAS that was last updated 7 years ago?
https://blogs.sas.com/content/iml/2013/08/02/how-old-is-your-version-of-sas-release-dates-for-sas-software.html

Re: Excel file warning - different format than file extension

saikiran_nemani — Thu, 25 Jul 2019 16:15:13 GMT

ODS LISTING;

ODS TAGSETS.EXCELXP FILE='/SAS/SAI/REPEAT_SUMMARY.XLS'

ODS TAGSETS.EXCELXP CLOSE;

Re: Excel file warning - different format than file extension

Reeza — Thu, 25 Jul 2019 16:16:50 GMT

The extension must be xml., Excel will still open the file, though it may not be the default application.

ODS TAGSETS.EXCELXP FILE='/SAS/SAI/REPEAT_SUMMARY.XML'

@saikiran_nemani wrote:
ODS LISTING;

ODS TAGSETS.EXCELXP FILE='/SAS/SAI/REPEAT_SUMMARY.XLS'

ODS TAGSETS.EXCELXP CLOSE;

Re: Excel file warning - different format than file extension

Cynthia_sas — Thu, 25 Jul 2019 16:35:05 GMT

If you persist in using .XLS as the file extension for TAGSETS.EXCELXP steps, you will continue to get the Microsoft warning. This is a MICROSOFT requirement that the file extension match the contents of the file.

Again, the correct file extension to use with TAGSETS.EXCELXP is .XML -- NOT .XLS because TAGSETS.EXCELXP is creating Spreadsheet Markup Language XML and Microsoft wants the output file to have the correct extension. Otherwise, change the registry, as I explained earlier based on the Tech Support note.

Cynthia