Append multiple excel sheets automatically

[Jekaterina]

Hi all,

I have a data file in Excel with many sheets, where each sheet corresponds to same measurements taken on a separate parcel A, B, C, D, etc, and name of the sheet reflects the name of the parcel. The names and types of the variables are the same across all the sheets. Number of parcels and sheets can differ from file to file.

I need to automatically append all the sheets into one datafile, without having to open each sheet separately and then adding cases sheet by sheet. Is there an effective way in SPSS to do that?

Thanks,
 
Jekaterina

 

[Jon K Peck]

If you are using Statistics and have Python installed, it would be a simple program to do this as a single task.  It would require installation of the xlrd third-party package in order to get the names of all the sheets.


Jon Peck (no "h") aka Kim
Senior Software Engineer, IBM
[hidden email]
phone: [phone]




From:        [hidden email]
To:        [hidden email],
Date:        12/11/2013 07:45 AM
Subject:        [SPSSX-L] Append multiple excel sheets automatically
Sent by:        "SPSSX(r) Discussion" <[hidden email]>

---

Hi all,

I have a data file in Excel with many sheets, where each sheet corresponds to same measurements taken on a separate parcel A, B, C, D, etc, and name of the sheet reflects the name of the parcel. The names and types of the variables are the same across all the sheets. Number of parcels and sheets can differ from file to file.

I need to automatically append all the sheets into one datafile, without having to open each sheet separately and then adding cases sheet by sheet. Is there an effective way in SPSS to do that?

Thanks,

Jekaterina

 

[Jekaterina]

Thanks for pointing out a solution.
I've tried to install the xlrd package according to instructions given at http://www.spss-tutorials.com/read-and-merge-multiple-sheet-excel-workbooks/

When I run it, I get the following error at the end: "UnboundLocalError: local variable 'vNames' referenced before assignment"

What can be a problem?

Here is the code that I get:

begin program.
'''
This syntax was pasted from an SPSS custom dialog found at www.spss-tutorials.com/read-and-merge-multiple-sheet-excel-workbooks/.
Version: 0.0.
'''
def xlsToSpss(rdir,varnames=True,sheets='all'):
    import xlrd,spss,os
    fils=[fil for fil in os.listdir(rdir) if fil.endswith(".xls")]
    allData=[]
    for cnt,fil in enumerate(fils):
        wb=xlrd.open_workbook(os.path.join(rdir,fil))
        if sheets != 'all':
            rWs = [wb.sheets()[int(i)] for i in sheets.split(',')]
        else:
            rWs = wb.sheets()
        for ws in rWs:
            if varnames:
                vNames = ["source_file"]+["source_sheet"]+ws.row_values(0)
            else:
                vNames = ["source_file"]+["source_sheet"]+["column_%d"%(i + 1) for i in range(ws.ncols)]
            fRow = 1 if varnames else 0
            for row in range(fRow,ws.nrows):
                allData.append([fil]+[ws.name]+[val for val in ws.row_values(row)])
    mxLens=[0]*len(vNames)
    for line in allData:
        for cnt in range(len(line)):
            if isinstance(line[cnt],basestring) and len(line[cnt])>mxLens[cnt]:
                mxLens[cnt]=len(line[cnt])
    with spss.DataStep():
        nds = spss.Dataset('*')
        for var in zip(vNames,mxLens):
            nds.varlist.append(var[0],var[1])
        for line in allData:
            nds.cases.append([None if val=='' else val for val in line])
xlsToSpss('F:/temp',varnames=True,sheets='all')
end program.
Traceback (most recent call last):
  File "<string>", line 35, in <module> 
  File "<string>", line 24, in xlsToSpss
UnboundLocalError: local variable 'vNames' referenced before assignment