SPSS Tutorials

BASICS REGRESSION T-TEST ANOVA CORRELATION

Read and Merge Multiple Sheet Excel Workbooks

Summary

Reading multiple sheet Excel workbooks into SPSS is easily done with this Custom Dialog. This tutorial demonstrates how to use it.

Before You Start

SPSS Read Excel Files Tool SPSS Read and Merge Excel Files Tool

SPSS Syntax Example for Generating Test Data

* Create some small Excel workbooks for testing.

begin program.
rdir=r'd:\temp' # Specify folder for writing test files.
import xlwt,random,datetime,os
fmt = xlwt.easyxf(num_format_str='M/D/YY')
wBooks = ["book_" + str(cnt) for cnt in range(1,5)]
for noSheets,wBook in enumerate(wBooks):
    wb=xlwt.Workbook()
    for sheetNo in range(noSheets + 1):
        ws=wb.add_sheet("sheet_%d"%(sheetNo + 1))
        for col,cont in enumerate(['date','ID','JobTitle','Revenue']):
            ws.write(0,col,cont)
        for row in range(1,6):
            ws.write(row,0,datetime.datetime(2008 + sheetNo,1,1) + datetime.timedelta(days=random.randrange(1,365)),fmt)
            ws.write(row,1,random.choice([None,104,21,60,2,1030]))
            ws.write(row,2,random.choice([None,'Developer','Tester','Manager']))
            ws.write(row,3,random.randrange(40,80)*1000)
    wb.save(os.path.join(rdir,wBook + '.xls'))
end program.

Reading All Data Into SPSS

Since we created our test data in d:\temp, this folder will hold the Excel files. We can simply copy-paste this into the dialog. Other than that, we don't have to change anything. The first row holds the variable names and we'd like all sheets from all workbooks to be read.

Description

Converting Date Variables

Date variables in the Excel files are not automatically converted to SPSS date variables. After reading in the data, they can be converted with the syntax below.
* Convert "date" to date format.

compute date=datesum(date.dmy(30,12,1899),date,"days").
format date(edate10).
exe.

What if I Don't Want All Sheets to be Read?

What if I Don't Want All Workbooks to be Read?

This default can not be overridden. A workaround may be to move irrelevant workbooks to a different folder.

Previous tutorial: Compare Dictionaries over Files Before Merging

Next tutorial: SPSS – Set Chart Sizes Tool

Let me know what you think!

*Required field. Your comment will show up after approval from a moderator.

This tutorial has 8 comments