How to Save Multiple Datasets to a Single XLSX File via Syntax?

[ariel barak]

Hi All,
 
This e-mail is in regards to converting syntax that writes data from an open SPSS dataset to a worksheet in Excel 2000 to Excel 2010 ( .xls to .xlsx ). I am working with 19.0.0.1 with XP 32-bit and MS Office 2010 32-bit. I did not use the GUI to create the "OLD" syntax, I found it on the SPSS listserv and it creates a new xls file if it does not yet exist and exports the dataset to the worksheet named in the TABLE subcommand. The cool thing is that I use this code to export 9 different datasets to 9 different worksheets in the same file via syntax instead of having to open 9 different files and piece them together - I find it a very handy time-saver!
 
* OLD CODE - xls.
DATASET ACTIVATE GroupHomeDaysDollarsAvgRate.
SAVE TRANSLATE
/CONNECT='DSN=Excel Files;DBQ=G:\Test\SACWIS OOH Payment Filesl\Test65.xls;DriverId=790;MaxBufferSize=2048;PageTimeout=5;'
/TABLE="GroupHomeDaysDollarsAvgRate"
/TYPE=ODBC
/REPLACE.
EXECUTE.
 
I tried modifying the syntax above to no avail, but got the "New" syntax below from the GUI. With the appropriate dataset open, I click File>Export to Database... I then select "Excel Files" - click "Browse" and find the xlsx file I want to export to. I then select the "Create a new table" radio button and name the table. I drag all of the variables to the SPSS dataset on the left to the table on the right with the User-Missing Values Export as valid values radio button checked and click Next and then click "Paste the syntax" and get the huge piece of syntax below.
 
There are several downsides to the "New" syntax vs the "Old" syntax:
1) If I add variables or change variable names or types, the command won't work. The old syntax just took all the variables in the dataset and exported them regardless of whether there were any changes or additions.
2) It seems that I need to create a xlsx file with the name in the connect subcommand prior to running it. The old syntax created the file if it did not exist.
3) In each of the 9 export commands, I have to use a new temp table i.e. SPSS_TEMP_6, SPSS_TEMP_7, etc. for some reason. I then have 9 worksheets (SPSS_TEMP_1 through SPSS_TEMP_9), that I have to delete, which isn't a big problem, but is one additional step to remember. The old syntax did not create any unnecessary worksheets in the xls file.
4) It is large amount of code.
 
If someone could let me know if there is a way to modify the "Old" syntax so I could write to an xlsx file or provide me with syntax that gets around the 4 downsides I listed above from the GUI-generated syntax, that would be great!!!
 
Thanks,
Ariel
 
* NEW CODE - xlsx.
DATASET ACTIVATE GroupHomeDaysDollarsAvgRate.
 
SAVE TRANSLATE /TYPE=ODBC
  /CONNECT='DSN=Excel Files;DBQ=G:\Test\SACWIS OOH Payment Files\SACWIS Payments - '+
    '2011.08 (Test66).xlsx;DriverId=1046;FIL=excel 12.0;MaxBufferSize=2048;PageTimeout=5;'
  /ENCRYPTED
  /MISSING=IGNORE
  /SQL='CREATE TABLE GroupHomeDaysDollarsAvgRate(TX_PRVD_NM varchar(43), CD_RPTG_CAT varchar(2), '+
    'Type varchar(7), v200501 number, v200502 number, v200503 number, v200504 number, v200505 '+
    'number, v200506 number, v200507 number, v200508 number, v200509 number, v200510 number, '+
    'v200511 number, v200512 number, v200601 number, v200602 number, v200603 number, v200604 '+
    'number, v200605 number, v200606 number, v200607 number, v200608 number, v200609 number, '+
    'v200610 number, v200611 number, v200612 number, v200701 number, v200702 number, v200703 '+
    'number, v200704 number, v200705 number, v200706 number, v200707 number, v200708 number, '+
    'v200709 number, v200710 number, v200711 number, v200712 number, v200801 number, v200802 '+
    'number, v200803 number, v200804 number, v200805 number, v200806 number, v200807 number, '+
    'v200808 number, v200809 number, v200810 number, v200811 number, v200812 number, v200901 '+
    'number, v200902 number, v200903 number, v200904 number, v200905 number, v200906 number, '+
    'v200907 number, v200908 number, v200909 number, v200910 number, v200911 number, v200912 '+
    'number, v201001 number, v201002 number, v201003 number, v201004 number, v201005 number, '+
    'v201006 number, v201007 number, v201008 number, v201009 number, v201010 number, v201011 '+
    'number, v201012 number, v201101 number, v201102 number, v201103 number, v201104 number, '+
    'v201105 number, v201106 number, v201107 number, v201108 number)'
  /REPLACE
  /TABLE='SPSS_TEMP_6'
  /KEEP=TX_PRVD_NM, CD_RPTG_CAT, Type, v200501, v200502, v200503, v200504, v200505, v200506,
    v200507, v200508, v200509, v200510, v200511, v200512, v200601, v200602, v200603, v200604, v200605,
    v200606, v200607, v200608, v200609, v200610, v200611, v200612, v200701, v200702, v200703, v200704,
    v200705, v200706, v200707, v200708, v200709, v200710, v200711, v200712, v200801, v200802, v200803,
    v200804, v200805, v200806, v200807, v200808, v200809, v200810, v200811, v200812, v200901, v200902,
    v200903, v200904, v200905, v200906, v200907, v200908, v200909, v200910, v200911, v200912, v201001,
    v201002, v201003, v201004, v201005, v201006, v201007, v201008, v201009, v201010, v201011, v201012,
    v201101, v201102, v201103, v201104, v201105, v201106, v201107, v201108
  /SQL='INSERT INTO GroupHomeDaysDollarsAvgRate(TX_PRVD_NM, CD_RPTG_CAT, Type, v200501, v200502, '+
    'v200503, v200504, v200505, v200506, v200507, v200508, v200509, v200510, v200511, v200512, '+
    'v200601, v200602, v200603, v200604, v200605, v200606, v200607, v200608, v200609, v200610, '+
    'v200611, v200612, v200701, v200702, v200703, v200704, v200705, v200706, v200707, v200708, '+
    'v200709, v200710, v200711, v200712, v200801, v200802, v200803, v200804, v200805, v200806, '+
    'v200807, v200808, v200809, v200810, v200811, v200812, v200901, v200902, v200903, v200904, '+
    'v200905, v200906, v200907, v200908, v200909, v200910, v200911, v200912, v201001, v201002, '+
    'v201003, v201004, v201005, v201006, v201007, v201008, v201009, v201010, v201011, v201012, '+
    'v201101, v201102, v201103, v201104, v201105, v201106, v201107, v201108) SELECT TX_PRVD_NM, '+
    'CD_RPTG_CAT, Type, v200501, v200502, v200503, v200504, v200505, v200506, v200507, v200508, '+
    'v200509, v200510, v200511, v200512, v200601, v200602, v200603, v200604, v200605, v200606, '+
    'v200607, v200608, v200609, v200610, v200611, v200612, v200701, v200702, v200703, v200704, '+
    'v200705, v200706, v200707, v200708, v200709, v200710, v200711, v200712, v200801, v200802, '+
    'v200803, v200804, v200805, v200806, v200807, v200808, v200809, v200810, v200811, v200812, '+
    'v200901, v200902, v200903, v200904, v200905, v200906, v200907, v200908, v200909, v200910, '+
    'v200911, v200912, v201001, v201002, v201003, v201004, v201005, v201006, v201007, v201008, '+
    'v201009, v201010, v201011, v201012, v201101, v201102, v201103, v201104, v201105, v201106, '+
    'v201107, v201108 FROM SPSS_TEMP_6'
  /SQL='DROP TABLE SPSS_TEMP_6'.

[David Marso]

I have *NO* idea, but this looks like one of those take one step forward and one leap backwards and fall on your head types of "enhancements" ;-(

[ariel barak]

Hello All,

I submitted this question to SPSS Tech Support and got the following reply (solution with syntax example at the bottom):

1) I really doubt his "old" syntax command was simply pasted from the Export To Database wizard, because all of the SQL subcommands are created by default.  The good news is that they aren't necessary if all you want to do is grab -everything- in the dataset.  His "old" command should work perfectly well in 19 -- all he needs to do is copy the /CONNECT subcommand over from the "new" one that uses the new driver for .xlsx files.

2) This one puzzles me.  My original knee-jerk reaction was "impossible.  by definition, ODBC communication requires an existing database to 'talk' to -- even if that database is an Excel file."   But...

It turns out the stripped-down command -will- create an Excel file if it doesn't already exist -- but with this weirdness:  if it's an .xlsx file, I can't get it to open at all, and if it's an .xls file, it says it's a format that doesn't match the file extension, but opens anyway -- but I think in more of a Excel 4.0 format.  It may be that that latter behavior is what the user is talking about, or that either the earlier Excel driver (i.e., the non-xlsx one) or an earlier version of Stats created the file from scratch...  but I would have to take this one to development.  It's not expected behavior, from the way I understand ODBC, and may well be accidental functionality that we never intended to be present.

In any event, the current best practice will still be to have the file created (but empty) to start with.

3) see 1) -- with no /SQL subcommands, these temp "holding" worksheets are no logner necessary, and won't be present.

4) again, see 1).  If you're grabbing all the variables, you don't need all the SQL.


So, to illustrate, I created a blank Excel file called Book1.xlsx, and used the Export to Database Wizard to export all the variables from Employee Data into a new sheet called 'empdata1'  -- there were a couple of extra variables present I'd inadvertently saved to my copy of Employee Data....    The pasted syntax looked like this:

SAVE TRANSLATE /TYPE=ODBC
  /CONNECT='DSN=Excel Files;DBQ=C:\work\pmr71053\Book1.xlsx;DriverId=1046;MaxBufferSize='+
    '2048;PageTimeout=5;'
  /ENCRYPTED
  /MISSING=IGNORE
  /SQL='CREATE TABLE empdata1(id number, gender varchar(1), bdate datetime, educ number, jobcat '+
    'number, salary currency, salbegin currency, jobtime number, prevexp number, minority number, '+
    'jobcat_topbox number, min_filter number)'
  /REPLACE
  /TABLE='SPSS_TEMP'
  /KEEP=id, gender, bdate, educ, jobcat, salary, salbegin, jobtime, prevexp, minority,
    jobcat_topbox, min_filter
  /SQL='INSERT INTO empdata1(id, gender, bdate, educ, jobcat, salary, salbegin, jobtime, '+
    'prevexp, minority, jobcat_topbox, min_filter) SELECT id, gender, bdate, educ, jobcat, salary, '+
    'salbegin, jobtime, prevexp, minority, jobcat_topbox, min_filter FROM SPSS_TEMP'
  /SQL='DROP TABLE SPSS_TEMP'.


But, after a little trimming, the following worked just as well:

SAVE TRANSLATE /TYPE=ODBC
  /CONNECT='DSN=Excel Files;DBQ=C:\work\pmr71053\Book1.xlsx;DriverId=1046;MaxBufferSize='+
    '2048;PageTimeout=5;'
  /ENCRYPTED
  /MISSING=IGNORE
  /REPLACE
  /TABLE='empdata1'.

On Fri, Sep 23, 2011 at 4:20 PM, David Marso <[hidden email]> wrote:

I have *NO* idea, but this looks like one of those take one step forward and
one leap backwards and fall on your head types of "enhancements" ;-(

--
View this message in context: http://spssx-discussion.1045642.n5.nabble.com/How-to-Save-Multiple-Datasets-to-a-Single-XLSX-File-via-Syntax-tp4834706p4835259.html
Sent from the SPSSX Discussion mailing list archive at Nabble.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