issues with importing *.XLSB into sas

Reply
New Contributor
Posts: 3

issues with importing *.XLSB into sas

I want to import a file which is in .xlsb format. The file contains 200,000 rows, so I cannot save it as .xls file as the limit of xls file in 65536 rows. I used the below code

PROC IMPORT OUT= input.file1
DATAFILE= "C:\a.xlsb"
DBMS=EXCELcs REPLACE;
range='sheet1$a2:L200000'n;
server = "server-name";
port=8621

RUN;

log as below when I run the above code

ERROR: CLI error trying to establish connection: [Microsoft][ODBC Excel Driver] External table is not in the expected format.

ERROR: Error in the LIBNAME statement.

Connection Failed.  See log for details.

I also tried splitting the file into 4 files with 50,000 rows each and saving as .xls files but that too did not work and I got the below errors

ERROR: Database error. Examine fields and/or messages below.

The file specified is not an Excel 4, 5, or 7 (Excel 95) file.

ERROR: Database table must be entered first

ERROR: Database table must be entered first

ERROR: File WORK._IMEX_.DATA does not exist.

WARNING: The data set WORK.PARS2 may be incomplete.  When this step was stopped there were 0

         observations and 0 variables.

ERROR: Import unsuccessful.  See SAS Log for details.

SAS Employee
Posts: 6

Re: issues with importing *.XLSB into sas

Can you export out from Excel into CSV and run the INFILE of PROC IMPORT from CSV?

New Contributor
Posts: 3

Re: issues with importing *.XLSB into sas

That did work but my first column in the raw data contains date(YYYY/MM) .. Example as below..

Date

2013/06

2014/01

1995/05

..

..

and so on..

But the date column is not getting imported. The code I have used is as below

the informat yymmds7. also does not work.

Valued Guide
Posts: 3,208

Re: issues with importing *.XLSB into sas

It should work.SAS/ACCESS(R) 9.3 Interface to PC Files: Reference and SAS/ACCESS(R) 9.3 Interface to PC Files: Reference (SAS/ACCESS(R) 9.3 Interface to PC Files: Reference, Microsoft Excel Workbook Files)

You are trying to connect to a pc-files-server as of.

server = "server-name";  port=8621  .... Is the servername really correct?

The remote Windows machine really working correct?

---->-- ja karman --<-----
New Contributor
Posts: 3

Re: issues with importing *.XLSB into sas

Yes I used the below code to find

/* Get the Host Name and Port Number of the             */

/* Stored Process Server that executed this request.    */

data _null_;

  format obparms $char1000.;

  obparms=getoption('objectserverparms');

  x1 = index(obparms,"port=");

  port = substr(obparms,x1+5);

  x1 = index(port," ");

  port = substr(port,1,x1);

  call symput('_PORT',trim(left(put(port,6.))));

  host =sysget('COMPUTERNAME');   /* Use this statement on WINDOWS */

  /*  host =sysget('HOST'); */    /* Use this statement on UNIX    */

  call symput('_SERVER',trim(left(put(host,$80.))));

run;

data _null;

  file _webout;

  put '<HTML>';

  put '<H2>Stored Process Server Host Name and Port Number</H2>';

  put "<br>The Server Host Name is: &_SERVER";

  put "<br>The Server Port Number is: &_PORT";

  put '</HTML>';

run;

Valued Guide
Posts: 3,208

Re: issues with importing *.XLSB into sas

vivek, very confusing as you are prgaramming the defnitions for a stored processerver running on windows.

When you are running SAS on your own machine (local) would leave out serverdefnitions.

XLSB is a binary type of the spreadsheet, you could uses the xlsx version..... (still above 64K rows)

dates in Excel are numbers since 1900 not chars. It coul be you have a conversion issue somwhere binary/number/char. That is difficult to see. ... Support SAS (TLS?) or revi@sas

---->-- ja karman --<-----
Ask a Question
Discussion stats
  • 5 replies
  • 1810 views
  • 0 likes
  • 3 in conversation