topic Re: I want to read an Excel spreadsheet without truncating values in SAS Data Management

I want to read an Excel spreadsheet without truncating values

HWSteinberg — Tue, 13 May 2014 10:14:50 GMT

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.

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

Kurt_Bremser — Tue, 13 May 2014 10:33:32 GMT

Are you talking about numbers or strings?

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

RW9 — Tue, 13 May 2014 10:48:29 GMT

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.

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

HWSteinberg — Tue, 13 May 2014 12:17:23 GMT

strings

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

HWSteinberg — Tue, 13 May 2014 12:21:01 GMT

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)

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

RW9 — Tue, 13 May 2014 12:28:16 GMT

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;

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

HWSteinberg — Tue, 13 May 2014 16:01:36 GMT

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

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

RW9 — Tue, 13 May 2014 18:11:39 GMT

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.