merge multiple excel worksheets

[Scott Roesch]

Hi all:

Is there a quick way to convert multiple worksheets in Excel into a
merged SPSS/PASW data file?

Scott

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

[statisticsdoc]

Scott,
In a nutshell, open each sheet in spss, sort by the key variable(s) that you want to merge on, save each sheet in an sav fiile, merge the sav files by the key variables that you want to merge the files on.  To get the syntax for opening a specific excel sheet, use open file, choose xls, and then choose the paste rather than the open option.
Best,
Stephen Brand
------Original Message------
From: Scott Roesch
Sender: SPSSX(r) Discussion
To: [hidden email]
ReplyTo: Scott Roesch
Subject: merge multiple excel worksheets
Sent: Dec 29, 2011 11:42 AM

Hi all:

Is there a quick way to convert multiple worksheets in Excel into a
merged SPSS/PASW data file?

Scott

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

www.StatisticsDoc.com

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

[Jon K Peck]

One shortcut: you can just open each Excel file in Statistics and then use it in the merge step (after sorting if needed).  There is no need to save each Excel file as a sav file first.

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




From:        Statisticsdoc Consulting <[hidden email]>
To:        [hidden email]
Date:        12/29/2011 11:26 AM
Subject:        Re: [SPSSX-L] merge multiple excel worksheets
Sent by:        "SPSSX(r) Discussion" <[hidden email]>

---

Scott,
In a nutshell, open each sheet in spss, sort by the key variable(s) that you want to merge on, save each sheet in an sav fiile, merge the sav files by the key variables that you want to merge the files on.  To get the syntax for opening a specific excel sheet, use open file, choose xls, and then choose the paste rather than the open option.
Best,
Stephen Brand
------Original Message------
From: Scott Roesch
Sender: SPSSX(r) Discussion
To: [hidden email]
ReplyTo: Scott Roesch
Subject: merge multiple excel worksheets
Sent: Dec 29, 2011 11:42 AM

Hi all:

Is there a quick way to convert multiple worksheets in Excel into a
merged SPSS/PASW data file?

Scott

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

www.StatisticsDoc.com

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

[Albert-Jan Roskam]

If you have a lot of different excel files with different sheetnames, you could use xlrd (unless the xls are of the new, xml-based, format).

import spss, xlrd

sps = "<paste spss syntax here, replacing the sheetname with '%s'"

for sheet in xlrd.open_workbook('myworkbook.xls').sheet_names():
  spss.Submit(sps % (sheet))

 Of course you can also loop over the files with SPSSINC PROCESS FILES or with glob.glob, or os.listdir, or with os.walk.

Happy newyear!

Cheers!!
Albert-Jan

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
All right, but apart from the sanitation, the medicine, education, wine, public order, irrigation, roads, a fresh water system, and public health, what have the Romans ever done for us?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

---

From: Jon K Peck <[hidden email]>
To: [hidden email]
Sent: Thursday, December 29, 2011 7:38 PM
Subject: Re: [SPSSX-L] merge multiple excel worksheets

One shortcut: you can just open each Excel file in Statistics and then use it in the merge step (after sorting if needed).  There is no need to save each Excel file as a sav file first.

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




From:        Statisticsdoc Consulting <[hidden email]>
To:        [hidden email]
Date:        12/29/2011 11:26 AM
Subject:        Re: [SPSSX-L] merge multiple excel worksheets
Sent by:        "SPSSX(r) Discussion" <[hidden email]>

---

Scott,
In a nutshell, open each sheet in spss, sort by the key variable(s) that you want to merge on, save each sheet in an sav fiile, merge the sav files by the key variables that you want to merge the files on.  To get the syntax for opening a specific excel sheet, use open file, choose xls, and then choose the paste rather than the open option.
Best,
Stephen Brand
------Original Message------
From: Scott Roesch
Sender: SPSSX(r) Discussion
To: [hidden email]
ReplyTo: Scott Roesch
Subject: merge multiple excel worksheets
Sent: Dec 29, 2011 11:42 AM

Hi all:

Is there a quick way to convert multiple worksheets in Excel into a
merged SPSS/PASW data file?

Scott

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

www.StatisticsDoc.com

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD