Limit To Columns Imported from Excel 2010?

[shellp55]

Hello

I am importing a table from Excel 2010 using SAS 9.2.  This has never been a problem before but now SAS isn't importing all columns.  The table is from A1 to OQ366 and SAS only imports to IU366.  I even forced the range by using the worksheet and name in the import syntax i.e.

"ImportFile$A1:OQ366". For another table that is A1 to NB366 it only imports to MH366.

Is there a specified limit to the number of columns imported?  Is there any way I can force the import?  Thanks.

[art297]

Post the code that you used.  Column IU, which I think is column 255, makes me think that you are attempting to import using one of the older engines.  Are you running base SAS or are you using EG?  Do you license SAS/Access for pcfile formats?

[PGStats]

I believe the ACE library (a Microsoft product) which is the interface used by SAS to access Excel and Access files is still limited to 255 column on import and export.

PG

[art297]

I think the libname engine still has those limits but, with the latest version of 9.2 (M 3 I think), proc import and proc export can deal with the actual new excel capabilities.

[shellp55]

Hi

The code I used to import is:

Proc IMPORT OUT= WORK.MASTER_NEW_B1

DATAFILE= &import_path

DBMS=EXCELCS REPLACE;

Range = "B1$";

Scantext = Yes;

UseDate = Yes;

run;

Note that B1 is the name of the worksheet.  So is there any way to get around the import column limit?

Thanks.

Shelley

[art297 · Accepted solution]

I can only test it on 9.3.  The following worked for me on a 64-bit Windows 2008 server:

%let import_path=d:\art\widetest.xlsx;

Proc IMPORT OUT= WORK.MASTER_NEW_B1

  DATAFILE= "&import_path"

  DBMS=XLSX REPLACE;

  getnames=no;

run;

[shellp55]

Hi Arthur

Thanks so much....I don't know why I said I had 9.2 because I do have 9.3!!  I'm assuming the "getnames" is for the column head labels?

[art297]

Yes.  If you have column head labels then use getnames=yes;