Date:         Thu, 4 Dec 2003 09:53:38 -0500
Reply-To:     diskin.dennis@KENDLE.COM
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Dennis Diskin <diskin.dennis@KENDLE.COM>
Subject:      Hint: one solution for reading excel files with embedded cr/lf
Content-Type: text/plain; charset="us-ascii"

A solution I came up with to read embedded cr/lf in excel cells.
This is somewhat combersome and has not been tested for all possible
conditions.

Problem: excel spreadsheets contain cr/lf in some cells.
Solution: At least in the version I'm working with, DDE returns a double
quoute at the biginning and end of the field with the embedded cr/lf, so:

1. read only one cell at a time.
2. look for the leading " and if present (and not terminated by another "
at end) read another 'record' from the cell and append it to the previous
one.
3. continue 2 until a trailing " is found.
4. get rid of the double quotes.

The below code implements this in a macro which works for my particular
data. I put a blank in place of the cr/lf - something else could be used.
For simplicity, I also trhow away any embedded double quotes. You may need
to keep them.

Regards.
Dennis Diskin



%macro getall(col,var);
/* macro reads a column and checks for a leading double quoute */
/*  if dq found, reads and appends until trailing dq found */
/* eliminates dq's */
filename xls&col dde "EXCEL|Site Contact Information!R2C&col:R9999C&col";
infile xls&col dlm='09'X notab dsd missover end=eof&col lrecl=8000;
informat save col $100.;
length save col $ 100;

input col;
if substr(col,1,1) eq '"' then
  do;
    do while (substr(reverse(trim(col)),1,1) ne '"' or length(trim(col)) eq 1);
        input save;
        col = trim(col) || ' ' || save;
    end;
  end;
&var = compress(col,'"');
%mend;

OPTIONS NOXWAIT NOXSYNC;
x """f:\sample.xls""";
filename xlscmd dde 'excel|system';

data investig(keep=siteid last first city state title);
/* make sure Excel had time to open */
if _n_ eq 1 then
  rc=system('sleep(10)');

length siteid $12 last first title city $50 state $2;

%getall(1,siteid)
%getall(2,last)
%getall(3,first)
%getall(4,title)
%getall(8,city)
%getall(9,state)


if eof1 or eof2 or eof3 or eof4 or eof8 or eof9 then
  do;
    /* close excel */
    file xlscmd;
    put '[QUIT()]';
    put '[QUIT()]';
  end;
run;