BookmarkSubscribeRSS Feed
HWSteinberg
Calcite | Level 5

I want to read an Excel spreadsheet without truncating values where the first rows contain only short values. I know the guessingrows statement for delimited files but that's not applicable to xls. To create a tab delimited file is not an option as the spreadsheet contains cells with CR characters ('0D'x) which create addtional lines when exporting from Excel.

7 REPLIES 7
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Some options:

1) File->SaveAs->CSV.  Datastep import with delimiter=",". where col1 ne "";

2) Add XXXXXXX to the length required in your spreadsheet as first row.

3) Check out libname xls, which may or may not work.

4) There is also a system option call something like guessrows in the systems registry which should be updated to a large number.

HWSteinberg
Calcite | Level 5

Sorry but neither csv, tab or other delimiter will work unless you find an option to not interprete CRs in single cells as carriage return and thus new line. And as I said I know that guessingrows works with csv, tab etc but not xls (I assume for the entry in the registry holds the same)

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Perhaps you could post the Excel file to show these carriage returns?  Do you mean you have a special symbol for carriage returns in your data or just the alt/enter within cell?

The guessing rows is applicable to proc import for Excel files, please see the guidance at: Base SAS(R) 9.3 Procedures Guide, Second Edition

I think the registry change affects both, the actual option on the import may not.

Just to add, have you tried using the libname:

libname mybook excel "....xls" mixed=yes getnames=yes;

HWSteinberg
Calcite | Level 5
Interaction:The GUESSINGROWS statement is valid only for delimited files.

Did you see this in your reference?

Unfortunately I'm using SAS 9.1.3 remote for a client, so I can't easily check all possibilities. But I see libname is worth a trial

As to CR, this is inserted by Excel when you enter a line wrap in a cell (Alt+Enter). You can see this when you copy the cell content into an Editor with optional hex display (like UltraEdit). And exporting from Excel to a delimited file, then additionally adds a CRLF ('0D0A'x) after the '0D'x

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Hi,

Yes, I posted the wrong web page.  Please see this one: SAS/ACCESS(R) 9.2 Interface to PC Files: Reference, Second Edition

Specifically the section below: MIXED=YES|NO

You can also find/replace in Excel:

Open the find/replace dialog, in the find box do Alt 010 - that zero one zero.  In replace put a space.  I have seen it stated that Cntrl + j instead of the Alt works.  You could also write a small VBA and replace CHAR(10) with a space on all cells.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 7 replies
  • 2146 views
  • 0 likes
  • 3 in conversation