Date: Thu, 4 Dec 2003 09:53:38 -0500
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
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
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.
/* 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;
if substr(col,1,1) eq '"' then
do while (substr(reverse(trim(col)),1,1) ne '"' or length(trim(col)) eq 1);
col = trim(col) || ' ' || save;
&var = compress(col,'"');
OPTIONS NOXWAIT NOXSYNC;
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
length siteid $12 last first title city $50 state $2;
if eof1 or eof2 or eof3 or eof4 or eof8 or eof9 then
/* close excel */