BookmarkSubscribeRSS Feed
Stephane
Quartz | Level 8
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 ??
18 REPLIES 18
deleted_user
Not applicable
I'm sorry to be the bearer of bad news:
http://support.sas.com/techsup/unotes/SN/019/019550.html
Cynthia_sas
SAS Super FREQ
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
Stephane
Quartz | Level 8
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.
Cynthia_sas
SAS Super FREQ
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
Stephane
Quartz | Level 8
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.
Cynthia_sas
SAS Super FREQ
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
Florent
Quartz | Level 8
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
Cynthia_sas
SAS Super FREQ
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
Florent
Quartz | Level 8
Thank you for the explanations.

Regards,
Florent
advoss
Quartz | Level 8
Have you tried using ODBC to read the spreadsheet?
Florent
Quartz | Level 8
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.
deleted_user
Not applicable
I assume SAS also realizes that XLSM is a format that needs read as well? Macro enabled file extensions must be XLSM.
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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
polingjw
Quartz | Level 8
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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

Discussion stats
  • 18 replies
  • 2512 views
  • 1 like
  • 8 in conversation