SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

I want to read an Excel spreadsheet without truncating values

Reply
Occasional Contributor
Posts: 15

I want to read an Excel spreadsheet without truncating values

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.

Super User
Posts: 7,849

Re: I want to read an Excel spreadsheet without truncating values

Posted in reply to HWSteinberg

Are you talking about numbers or strings?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 15

Re: I want to read an Excel spreadsheet without truncating values

Posted in reply to KurtBremser

strings

Super User
Super User
Posts: 7,981

Re: I want to read an Excel spreadsheet without truncating values

Posted in reply to HWSteinberg

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.

Occasional Contributor
Posts: 15

Re: I want to read an Excel spreadsheet without truncating values

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)

Super User
Super User
Posts: 7,981

Re: I want to read an Excel spreadsheet without truncating values

Posted in reply to HWSteinberg

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;

Occasional Contributor
Posts: 15

Re: I want to read an Excel spreadsheet without truncating values

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

Super User
Super User
Posts: 7,981

Re: I want to read an Excel spreadsheet without truncating values

Posted in reply to HWSteinberg

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.

Ask a Question
Discussion stats
  • 7 replies
  • 978 views
  • 0 likes
  • 3 in conversation