topic Re: PROC IMPORT problem with XLSX import in SAS Procedures

PROC IMPORT problem with XLSX import

IgorShumeiko — Thu, 06 Nov 2014 10:30:42 GMT

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.

Re: PROC IMPORT problem with XLSX import

RW9 — Thu, 06 Nov 2014 11:12:10 GMT

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.

Re: PROC IMPORT problem with XLSX import

IgorShumeiko — Thu, 06 Nov 2014 11:21:27 GMT

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

Re: PROC IMPORT problem with XLSX import

RW9 — Thu, 06 Nov 2014 12:01:57 GMT

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

Re: PROC IMPORT problem with XLSX import

esjackso — Thu, 06 Nov 2014 12:49:48 GMT

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?

Re: PROC IMPORT problem with XLSX import

IgorShumeiko — Thu, 06 Nov 2014 13:28:50 GMT

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.

Re: PROC IMPORT problem with XLSX import

jitendra_pandey — Mon, 22 Jun 2015 18:49:09 GMT

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

Thanks and regards,

Jitendra Pandey

Re: PROC IMPORT problem with XLSX import

jitendra_pandey — Mon, 22 Jun 2015 18:49:43 GMT

*figured

Re: PROC IMPORT problem with XLSX import

Tom — Mon, 22 Jun 2015 19:32:52 GMT

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.

Re: PROC IMPORT problem with XLSX import

Kurt_Bremser — Tue, 23 Jun 2015 05:24:56 GMT

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.

Re: PROC IMPORT problem with XLSX import

Ksharp — Tue, 23 Jun 2015 12:12:09 GMT

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