Reading excel files starting with blank rows

[mathzhang]

I have over 10000 excel files with only one column. Each file starts with:



{\rtf1\ansi {\fonttbl
{\f0\fcharset1 \fnil Times New Roman;}
{\f1\fcharset1 Tahoma;}
}


The first and second row are blank. I am having trouble with "proc import" to read them into SAS. The command I use is:

PROC IMPORT OUT= WORK.step2
DATAFILE= "c:\file1.xls"
DBMS=EXCEL REPLACE;
SHEET="Sheet1$";
GETNAMES=NO;
MIXED=NO;
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;
RUN;

I always got missing values. I also tried "libname" and it didn't work. Any suggestions are really appreciated.

[mathzhang]

When I delete the first two rows, everything works fine. So the problem must be caused by those blank rows.

[SASPhile]

try using GUESSINGROWS=50 in your proc import syntax.This works for version 9.1

[deleted_user]

but will that work with importing an excel file?
I tried it but it says that the statement is not valid.

[Cynthia_sas]

Hi:
PROC IMPORT (or the SAS Excel LIBNAME engine) will be able to import the following file types: XLS, XLSB, XLSX (depending on your version of SAS), delimited files, such as comma-delimited or tab-delimited files and even, Lotus 123 files. But, about Excel files specifically, as long as the file is truly an EXCEL file, PROC IMPORT should be able to import the file.

If you are using SAS Enterprise Guide, then EG uses the Microsoft JET engine for importing/exporting. If you are using the SAS Windowing environment, then you ened SAS/ACCESS for PC File Formats or the PC File Server in order to do your import, as described here:
http://support.sas.com/documentation/cdl/en/acpcref/63184/HTML/default/viewer.htm#a003103761.htm
and
http://support.sas.com/kb/32/455.html
and
http://support.sas.com/documentation/cdl/en/acpcref/63184/HTML/default/viewer.htm#a002107496.htm

The original poster implied, however, that an RTF file was somehow involved in the IMPORT process. I would never expect PROC IMPORT to work with an RTF file.

You say you tried "it" -- but what code did you try??? Can you post your code and/or LOG messages??? Also, generally, it is a good idea to start a new thread rather than piggyback onto an existing thread -- because some folks might think they'd already seen this thread before and would not bother to read it.

cynthia

[Cynthia_sas]

Hi:
I am very confused...as far as I know, Excel will NOT render an RTF file and those control strings you posted
[pre]
{\rtf1\ansi {\fonttbl{\f0\fcharset1 \fnil Times New Roman;}{\f1\fcharset1 Tahoma;}}
[/pre]

...usually represent the opening file setup control string instructions for an RTF file (the font setup). If I create an RTF file with Microsoft Word (save the file as .rtf) and then try to open the RTF file with Excel, Excel just shows me the "raw" RTF control strings.

Are you sure that this file with PROC IMPORT is a "true, binary" Excel file. If you open the file with Notepad and see the RTF control strings, that would indicate to me that your file may have an XLS extension, but it is really an RTF file.

cynthia

[Ksharp]

How about this


[pre]
PROC IMPORT OUT= WORK.test
DATAFILE= "c:\test.xls"
DBMS=xls REPLACE;
GETNAMES=NO;
MIXED=NO;

startrow=3;
RUN;
[/pre]


Ksharp