11-06-2014 05:30 AM
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.
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)
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.
11-06-2014 06:12 AM
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.
11-06-2014 07:49 AM
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?
11-06-2014 08:28 AM
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.
06-23-2015 01:24 AM
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.
06-22-2015 03:32 PM
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.