topic Re: Excel2007 in Microsoft Integration with SAS

Excel2007

Stephane — Wed, 26 Sep 2007 14:48:46 GMT

Hi,

we use SAS 9.1.3 SP4 and use Access to PC files for many times.

We've just installed Office 2007 and the MS-Jet that goes with.

Before, I created excel files with an external application and I imported the results into SAS with a proc Import Dbms=Excel2002. goes fine.

Now, when I execute the Proc Import, SAS writes "Connect: External table is not in the expected format"

=> Why ??

Re: Excel2007

deleted_user — Wed, 26 Sep 2007 16:09:36 GMT

I'm sorry to be the bearer of bad news:
http://support.sas.com/techsup/unotes/SN/019/019550.html

Re: Excel2007

Cynthia_sas — Wed, 26 Sep 2007 17:17:41 GMT

If you have the SAS Add-in for Microsoft Office and the whole Enterprise Intelligence Platform, then it is true that the SAS Add-in does not yet work with Office 2007.

PROC IMPORT and PROC EXPORT can handle things a bit differently -- but it takes a bit of work. Importing Excel 2007 format may be problematic because in Office 2007, by default, Excel 2007, saves your Excel files as XML -- the new Microsoft format for Excel and uses a file extension of .XLSX -- I do not know when PROC IMPORT and/or PROC EXPORT will be able to read this new format.

However, you can SAVE your Excel spreadsheet (in Office 2007) to a "back-level" of Excel (I believe the button says Save as Excel97-2003 format) -- if you save as that older format, then your PROC IMPORT should still work. (Just as if you EXPORT using an older format, then Excel will still open the older format file.)

For more help, with PROC IMPORT, you might with to contact SAS Tech Support.
http://support.sas.com/techsup/contact/index.html

cynthia

Re: Excel2007

Stephane — Wed, 26 Sep 2007 19:16:38 GMT

thank you.

In fact I create 235 files with an application that doesn't allow to create a back-level of Excel

But I'm surprised. In fact, when I open the xls file with notepad it appears like a binay file and not a XML file.
If the files are in XML, do i can open them with notepad or perhaps IE and see a XML document ?

By this question, I'm wondering if the MS-Jet Engine of Office 2007 can create a true Excel 2007.

Re: Excel2007

Cynthia_sas — Wed, 26 Sep 2007 22:16:39 GMT

Hi:
Honestly, this is a question for Tech Support. I don't know what happens if you create 235 files automatically -- are you using PROC EXPORT for this task? I did not think that SAS would create Office/Excel 2007 files yet with PROC EXPORT.

I thought your question was with IMPORTing the files back into SAS. At any rate, when I use Office/Excel 2007 to create 1 file, by opening Excel and typing into the application and then doing a FILE-->SAVE As Excel automatically wants to name the file with a .XLSX extension. It was my understanding that the XLSX was the "new" format and was some hybrid of Excel and XML markup format -- that was proprietary to Microsoft. It is NOT the same as Spreadsheet Markup Language XML that was the Office 2002/2003 XML.

If you cannot save your Excel files to a "back-level" then Tech Support will have to answer your question about whether PROC IMPORT (or PROC EXPORT) will be able to read and write Excel 2007 files.

cynthia

Re: Excel2007

Stephane — Thu, 27 Sep 2007 09:16:27 GMT

Sorry I was not clear.

I created 235 files with the external application, and I use SAS proc import to import them.
But on the PC, with have MSO2007 so the files generated were with the Excel 2007 format (because the application uses MS-Jet Engine installed on the PC to create them) , so the proc import can't work.
The XLSX is a XML compressed format, in reality.

The solution is to use OLE DB engine to read these files or save them in .txt format.

As always, thanks Cynthia.

Re: Excel2007

Cynthia_sas — Thu, 27 Sep 2007 15:58:30 GMT

Ah, got it! That makes sense.

You could also save your 235 files as .CSV files instead of .TXT and then you'd be able to read them with SAS.

cynthia

Re: Excel2007

advoss — Tue, 02 Oct 2007 16:34:38 GMT

Just a thought.

Could you programmatically loop through the spreadsheets using SAS and DDE (or an Excel macro) to open the spreadsheets and "re-save" them as XLS 97-2003 spreadsheets?

Re: Excel2007

Florent — Mon, 03 Mar 2008 11:27:31 GMT

Hi,

I face the same problem and I was wondering if it's now (5 months later) possible to use the proc import with an .XLSX file ?

Thanks in advance,
Florent

Re: Excel2007

Cynthia_sas — Mon, 03 Mar 2008 16:18:09 GMT

Hi, Florent:
To learn what an XLSX file really is, create a file with Excel 2007 and save it with the default .XLSX name.

Then, go to Windows Explorer, find the file where you saved it and RENAME the file extension to .ZIP -- then open the zip file with WinZip. Or, open WinZip first then then choose open from inside WinZip and navigate to where you saved the .XLSX file -- WinZip opened it very nicely and then you can view the XML files inside the archive using NOTEPAD. Anyway, you will see that there's not just 1 file, but a group of related files -- mostly XML.

Microsoft is calling this the Office Open XML format. You can see a picture of what an XLSX file really contains here:
http://blogs.msdn.com/excel/archive/2006/07/20/671995.aspx

I don't actually know whether PROC IMPORT can read an XLSX file yet -- that's still a question for Tech Support. The only thing I found on searching support.sas.com on .XLSX and IMPORT was this note about EG 4.1: http://support.sas.com/kb/4/484.html

cynthia

Re: Excel2007

Florent — Mon, 03 Mar 2008 16:29:19 GMT

Thank you for the explanations.

Regards,
Florent

Re: Excel2007

advoss — Mon, 03 Mar 2008 17:18:18 GMT

Have you tried using ODBC to read the spreadsheet?

Re: Excel2007

Florent — Tue, 04 Mar 2008 13:07:01 GMT

Yes, it works fine but the problems are that we have to update every SAS programs and we have to buy ODBC licences for every computer using these programs.

Re: Excel2007

deleted_user — Tue, 17 Jun 2008 16:45:27 GMT

I assume SAS also realizes that XLSM is a format that needs read as well? Macro enabled file extensions must be XLSM.

Re: Excel2007

sbb — Fri, 13 Mar 2009 07:47:55 GMT

SAS 9.2 provides native support for Excel 2007 as well as other Office 2007 file formats.

Scott Barry
SBBWorks, Inc.

http://support.sas.com/documentation/cdl/en/acpcref/61891/HTML/default/acpcrefwhatsnew902.htm

Re: Excel2007

polingjw — Wed, 21 Jul 2010 16:59:05 GMT

I am using SAS 9.2 Phase 2 and I am still not able to import xlsm files, even though I can import xlsx files without any problems. This is the error message that occurs whenever I try to import an xlsm file:

ERROR: Connect: External table is not in the expected format.
ERROR: Error in the LIBNAME statement.
Connection Failed. See log for details.

Any thoughts on this problem would be greatly appreciated.

Re: Excel2007

Cynthia_sas — Wed, 21 Jul 2010 23:39:12 GMT

Hi:
The Tech Support note that Scott referenced in an earlier post in this thread talks about how you can import .XLSX files using SAS 9.2, Phase 2.

If you think about it, an Excel "macro-enabled" spreadsheet of type .XLSM, has something above and beyond the data added to it (Excel macro instructions). I don't know whether there are any plans to be able to read .XLSM files with the LIBNAME engine or PROC IMPORT. This would be a question for Tech Support.

If you save the .XLSM file as a .XLSX or .XLS (Excel 97-2003 file), can you then read the spreadsheet with SAS??? I would expect so.

cynthia

Re: Excel2007

polingjw — Thu, 22 Jul 2010 16:35:34 GMT

Yes, I can read the file when saved as a .XLS document. The author of the file, however, wants to keep the file in the macro-enabled Excel 2007 format. As an alternative, I was able to read the data using DDE.

Thanks.

Re: Excel2007

GeoffNess — Thu, 22 Jul 2010 22:07:02 GMT

When I've encountered similar situations in the past, I've used a separate VBA process to save a new copy of the file in .xls format, before running the SAS import process. The macro-enabled copy is preserved but not read in. The issue of course is the duplication of the files.