CODEWITHSUNDEEP
pythonexcelsasxlwt
Jamil Gasimov
Jamil Gasimov

Reputation: 13

Importing xlwt generated excel files into SAS

I converted a bunch of csv files into excel files using xlwt. After that I need to import these excel files to SAS (statistical software).

On first try SAS throws an error saying that file format is invalid.

But when I just open any of generated files with MS Excel and close without doing any changes and without saving the file is imported in SAS successfully.

I found that MS Excel changes file header adding there the current username and updates file modified date.

I wonder if there is any possibility to modify excel file binary header before saving it.

Update: I am using xlwt version 0.7.4, Python 2.7.3, SAS 9.3, Excel 2010, Windows 7 32bit.

This is part of the my code where I convert CSV to Excel:

wb = xlwt.Workbook(encoding='latin-1')
ws = wb.add_sheet('Sheet1')
sourceCSV = csv.reader(open(files, 'rb'), delimiter=";")
for rowi, row in enumerate(sourceCSV):
    for coli, value in enumerate(row):
        ws.write(rowi, coli, value)
wb.save(xls_file)

Here are options being used with SAS import function:

...
DBMS=EXCEL REPLACE;
RANGE="Sheet1$";
GETNAMES=YES;
MIXED=NO;
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;
...

This is the error SAS produces:

ERROR: Connect: External table is not in expected format. 
ERROR: Error in the LIBNAME statement.

Upvotes: 1

Views: 570

Answers (1)

BellevueBob
BellevueBob

Reputation: 9618

If opening the workbook in Excel and saving it allows SAS to import it properly, that would indicate (to me) that there is some defect in xlxt (a process I know nothing about). Importing Excel files into SAS is a complicated topic because of the many different types of Excel workbooks.

It would be much easier to read the CSV file directly and skip the conversion to Excel. When you use PROC IMPORT to read a CSV file, SAS inspects to first several rows of the file to determine the column types. By default, SAS will look only at the first 20 rows in the file but you can change that with the GUESSINGROWS statement:

proc import datafile="C:\temp\test.csv"
     out=mydataset
     dbms=csv
     replace;
     getnames=yes;  /* Uses first row in CSV for column names */
     guessingrows=32767; 
run;

The above is the max value for GUESSINGROWS for version 9.2 of SAS; if you are using 9.3, the max has increased to 2,147,483,647.

Upvotes: 2

Related Questions