topic Re: importing from Excel in Microsoft Integration with SAS

importing from Excel

vonda12 — Wed, 27 Jun 2018 14:46:59 GMT

Hello!

I'm having difficulties importing an xlsx file...

In addition to the Import Wizard (which gives "ERROR: Connect: Class not registered ERROR: Error in the LIBNAME statement.), I've tried also the following:

---> LIBNAME data 'C:\Users\User\Documents\VacCov\VacCovData.xlsx';

ERROR: Connect: Class not registered
ERROR: Error in the LIBNAME statement

---> LIBNAME data PCFILES PATH='C:\Users\User\Documents\VacCov\VacCovData.xlsx';

seems to import all tabs, but when I try to open one it says it doesn't exist or cannot be displayed

---> PROC IMPORT DATAFILE="C:\Users\User\Documents\VacCov\VacCovData.xlsx"

DBMS=EXCELCS
OUT=WORK.FFF REPLACE;
SHEET='data';
RUN;

ERROR: Unable to transcode data to/from UCS-2 encoding.
ERROR: Import unsuccessful. See SAS Log for details.

To solve the errors in the first 2 attempts, I installed the SAS PC Files Server following an older thread on the board, but that didn't really help. (During the deployment, SAS Private Java Runtime Environment (64-bit) failed to install and Install Post Processing was completed with errors... not sure that has something to do with it.)

Any help is much appreciated!

Re: importing from Excel

RW9 — Wed, 27 Jun 2018 14:53:15 GMT

There was more or less exactly the same question earlier today:

https://communities.sas.com/t5/Base-SAS-Programming/ERROR-when-using-SAS-Enterprise-Guide-64bit-9-04-Base-to-load/m-p/473652#M121569

You just need to use dbms=xlsx. Note in my answer there you need to change to:

libname tmp xlsx 'C:\workspace\test.xlsx';

Re: importing from Excel

vonda12 — Wed, 27 Jun 2018 15:36:10 GMT

Thank you!

The LIBNAME statement still doesn't work for me, but PROC IMPORT is OK.

(In my original post I forgot to mention that I did try DBMS=xlsx as it was suggested somewhere else and it didn't work then, but somehow now it does :))

Thanks again for the help!