PROC IMPORT problem with XLSX import

[IgorShumeiko]

Hello!

I am using Enterprise Guide version 6.1 M1,  SAS 9.4 TS1M1, the operating system is Windows 7 x64 Professional.

My program tries to import a relatively big .xlxs file with 177000 rows.

PROC IMPORT

OUT= WORK.data

DATAFILE="C:\data.xlsx"

DBMS=XLSX;

SHEET="data";

GETNAMES=YES;

RUN;

Each time I run it SAS gives me the following error:

Could not find sheet (xl//xl/worksheets/Sheet1.xml) in xlsx file.  rc=8014900E (-2146136050)

Requested Input File Is Invalid

ERROR: Import unsuccessful.  See SAS Log for details.

There is no worksheet with the name Sheet1 in that file. Meanwhile if I try to import it via the import wizard (file>Import Data) everything works fine. Also if I save the file in excel 2007 it is possible to import it using the program. But after saving it has already 65536 rows the other data is somehow lost.

First I tried to perform this import using SAS 9.3 and it gave me an exception message:

ERROR:  An exception has been encountered.

Please contact technical support and provide them with the following traceback information:

The SAS task name is [IMPORT (2)]

ERROR:  Read Access Violation IMPORT (2)

Exception occurred at (088902A9)

Task Traceback

Address   Frame     (DBGHELP API Version 4.0 rev 5)

00000000088902A9  00000000090A3CB0  sasimxlx:tkvercn1+0xF269

000000000888D36A  00000000090CD490  sasimxlx:tkvercn1+0xC32A

00000000088892EB  00000000090CE660  sasimxlx:tkvercn1+0x82AB

00000000088817E3  00000000090CE6A0  sasimxlx:tkvercn1+0x7A3

0000000008852E41  00000000090CE6A8  sasimctr:tkvercn1+0x1E01

00000000088517C4  00000000090CF770  sasimctr:tkvercn1+0x784

0000000008831277  00000000090CF778  sasimpor:tkvercn1+0x237

00000000033F8FD7  00000000090CFCB8  sashost:Main+0xF917

00000000033FD5FD  00000000090CFF50  sashost:Main+0x13F3D

0000000076AA59ED  00000000090CFF58  kernel32:BaseThreadInitThunk+0xD

0000000076CDC541  00000000090CFF88  ntdll:RtlUserThreadStart+0x21

Probably it is worth to mention that I have no MS Office installed on the PC.

So I have a question whether it is possible to import such a file using a program? I would suggest that it must be possible since everything works fine with the import wizard for both SAS versions. Saving files once more is not an option either.

Every advise or hint is very appreciated. Thanks in advance.

[RW9]

Try taking the DBMS=XLSX out.  Also you may find it will not work if you don't have office installed, cant check at the moment, but I believe proc import uses the jet engine.

[IgorShumeiko]

Thanks for your replay! But it works on the same PC via the import wizard. Do they use different features?

[RW9]

Quite possibly, I will let the EG experts however cover that.

[esjackso]

Im pretty sure the wizard from the file menu in EG gives you the code it uses to read in the data (as a tab next to the log and output data tabs). I also think it defaults to using data step with an infile statement which would not require Office. The reason you lose data when changes the file extension is the row limitation that older excel files had of 65K rows.

Of course in EG if the wizard is part of your process flow it should work fine each time it is ran. Is there a reason that you are trying to move to proc import?

EJ

[IgorShumeiko]

Thanks for your replay!

Yes, you are right the wizard gives a possibility to look into the code of the importing process. Inside the text of the code we can read this:

Of course there is no explanation on how that temporary file was created.

   This DATA step reads the data values from a temporary text file

   created by the Import Data wizard. The values within the temporary

   text file were extracted from the Excel source file.

Unfortunately I cannot dynamically pass a parameter to the wizard which would content a file name of a file I am going to import. I assume that it is not possible. Correct me please if I am wrong. I would like to avoid letting the user change the code. That's why I have to do it the way shown in the first message.

[Kurt_Bremser]

The code from the EG import step won't help, as EG uses the MS-supplied .dll to convert the Office file(s) to text which is the sent to the WORK location and read from there, so the code is not Office(Excel) specific.

Preferred solution: export the sheet in question to a reasonable format (.csv!) and import that.

PS an Excel file with 177000 rows does not constitute a reliable data source anyway.

[jitendra_pandey]

Has anyone figure out the cause of this problem and the solution?

Thanks and regards,

Jitendra Pandey

[jitendra_pandey]

*figured

[Tom]

Sounds like you just changed the extension on the file and not the actual content of the tile.  Excel might be smart enough to figure that out, but the SAS XLSX engine is going to expect that it has a validly formatted XLSX file to work on.  An XLSX file is just ZIP file with specific content structure.  You can look at it with any ZIP file utility to see what files are there and whether the list of work books that SAS is complaining about is there or not.

[Ksharp]

What information in LOG ,when you are using LIBNAME statement ?