export big tables to excel (xlsx)

[michaela]

Dear,

We are working with very large tables (more than 256 columns) that need to
be exported to excel. We were hoping version 20 of SPSS could deal with this
and would include an export option to .xlsx. As this is not the case we are
looking for another solution.

Currently we export to html,then copy and paste manually into excel. As we
are not aware of any process to automate this task and because of course the
tables are very big this seems like a waste of time and can get very
frustrating.

Anyone out there that knows of a more efficient workaround for this?

Thanks,
michaëla

=====================
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

[Muenchen, Robert A (Bob)]

I'm out of the office until November 28th. While I'm out, Mile O'Neil will be filling in for me. You can reach him at 865-974-5230 or [hidden email]. Cheers, Bob

[Maguin, Eugene]

Probably others know much more but wouldn't a slightly simpler scheme be to
save (Save Translate) the data file as comma delimited text and import that
into excel?

Gene Maguin

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of
Michaela Stubbers
Sent: Friday, November 18, 2011 9:36 AM
To: [hidden email]
Subject: export big tables to excel (xlsx)

Dear,

We are working with very large tables (more than 256 columns) that need to
be exported to excel. We were hoping version 20 of SPSS could deal with this
and would include an export option to .xlsx. As this is not the case we are
looking for another solution.

Currently we export to html,then copy and paste manually into excel. As we
are not aware of any process to automate this task and because of course the
tables are very big this seems like a waste of time and can get very
frustrating.

Anyone out there that knows of a more efficient workaround for this?

Thanks,
michaëla

=====================
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

=====================
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

[David Marso]

I was thinking along these same lines except I would use tab delimited in case there are string fields with embedded commas.
--

Gene Maguin wrote

Probably others know much more but wouldn't a slightly simpler scheme be to
save (Save Translate) the data file as comma delimited text and import that
into excel?

Gene Maguin

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of
Michaela Stubbers
Sent: Friday, November 18, 2011 9:36 AM
To: [hidden email]
Subject: export big tables to excel (xlsx)

Dear,

We are working with very large tables (more than 256 columns) that need to
be exported to excel. We were hoping version 20 of SPSS could deal with this
and would include an export option to .xlsx. As this is not the case we are
looking for another solution.

Currently we export to html,then copy and paste manually into excel. As we
are not aware of any process to automate this task and because of course the
tables are very big this seems like a waste of time and can get very
frustrating.

Anyone out there that knows of a more efficient workaround for this?

Thanks,
michaëla

=====================
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

=====================
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]

> We are working with very large tables (more than 256 columns) that need to

> be exported to excel. We were hoping version 20 of SPSS could deal with this
> and would include an export option to .xlsx. As this is not the case we are
> looking for another solution.
>
> Currently we export to html,then copy and paste manually into excel. As we
> are not aware of any process to automate this task and because of course the
> tables are very big this seems like a waste of time and can get very
> frustrating.
>
> Anyone out there that knows of a more efficient workaround for this?

if you use SAVE TRANSLATE OUTFILE = ..... / VERSION = 12 you generate an Excel 2007 file, with a limit of 16,384 columns. Please tell me this is more than enough! ;-)
https://office.microsoft.com/en-001/excel-help/excel-specifications-and-limits-HP010073849.aspx
https://publib.boulder.ibm.com/infocenter/spssstat/v20r0m0/index.jsp?topic=%2Fcom.ibm.spss.statistics.help%2Fsyn_save_translate.htm

If you generate a .csv file, you won't have any problems writing humongous files. And it's simple to open it in Excel.

=====================
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]

Statistics V21 supports xlsx format for Viewer objects.  That was new in either V21 or V20 and is supported in the Export dialog box.

If you don't have xlsx support in your version, there is an alternative.  There is a Basic script available from the SPSS Community site named ExportTablesToExcelFiles.  It uses the COM automation of the installed Excel, so it automatically uses the format of that version.

Here is a direct link to that file, or you can find it in the Utilities Collection on the SPSS Community site (www.ibm.com/developerworks/spssdevcentral)
https://www.ibm.com/developerworks/mydeveloperworks/files/app?lang=en#/person/270002VCWN/file/8e0dfcb6-aa57-4639-a20e-1780010cfe83

SAVE TRANSLATE will handle data files but not Viewer files.
Jon Peck (no "h") aka Kim
Senior Software Engineer, IBM
[hidden email]
new phone: [phone]




From:        Albert-Jan Roskam <[hidden email]>
To:        [hidden email],
Date:        03/01/2013 06:38 AM
Subject:        Re: [SPSSX-L] export big tables to excel (xlsx)
Sent by:        "SPSSX(r) Discussion" <[hidden email]>

---

> We are working with very large tables (more than 256 columns) that need to

> be exported to excel. We were hoping version 20 of SPSS could deal with this
> and would include an export option to .xlsx. As this is not the case we are
> looking for another solution.
>
> Currently we export to html,then copy and paste manually into excel. As we
> are not aware of any process to automate this task and because of course the
> tables are very big this seems like a waste of time and can get very
> frustrating.
>
> Anyone out there that knows of a more efficient workaround for this?

if you use SAVE TRANSLATE OUTFILE = ..... / VERSION = 12 you generate an Excel 2007 file, with a limit of 16,384 columns. Please tell me this is more than enough! ;-)
https://office.microsoft.com/en-001/excel-help/excel-specifications-and-limits-HP010073849.aspx
https://publib.boulder.ibm.com/infocenter/spssstat/v20r0m0/index.jsp?topic=%2Fcom.ibm.spss.statistics.help%2Fsyn_save_translate.htm

If you generate a .csv file, you won't have any problems writing humongous files. And it's simple to open it in Excel.

=====================
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]

> I was thinking along these same lines except I would use tab delimited in

> case there are string fields with embedded commas.

Not sure if tab-delimited files are "double-clickable". But a major plus of using tabs is that the file is pretty human-
readable, unlike e.g. semicolon. And embedded separating characters should be quoted. I prefer explicitly telling spss what separator is used, because if you don't, it uses the locale to get that info. So on a computer with English regional settings, a comma is used, and on a computer with Dutch settings a semicolon is used.

=====================
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

[Peter Løvgreen (101plg)]

Jeg er på ferie
Jeg er tilbage mandag d. 11. marts 2013.
Med venlig hilsen Peter Løvgreen

=====================
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 don't have xlsx support in your
version, there is an alternative.  There is a Basic script available
from the SPSS Community site named ExportTablesToExcelFiles.  It uses
the COM automation of the installed Excel, so it automatically uses the
format of that version.
>
>Here is a direct link to that file,
or you can find it in the Utilities Collection on the SPSS Community site
(www.ibm.com/developerworks/spssdevcentral)
>https://www.ibm.com/developerworks/mydeveloperworks/files/app?lang=en#/person/270002VCWN/file/8e0dfcb6-aa57-4639-a20e-1780010cfe83
Can these scripts also be used to export all output out from an existing .spo file? (I think the answer is "No")
It would be useful if running something like...
SCRIPT ExportTablesToExcelFiles.wwd someOutput.spo
...under SPSS v20 would generate an excel file with one output item per sheet.
I think I prefer the customoutputfunctions.excelexport method using SPSSINC MODIFY OUTPUT.

=====================
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]

The script should also work on spo files.

Running it brings up a dialog box that allows you to choose whether tables are all in one file on separate sheets or in separate files.

This script predates MODIFY TABLES and is implemented in Basic, so it can be used without programmability support but only on Windows.

It is doubtless less powerful than MODIFY OUTPUT or MODIFY TABLES but is easier to use.


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




From:        Albert-Jan Roskam <[hidden email]>
To:        [hidden email],
Date:        03/01/2013 07:34 AM
Subject:        Re: [SPSSX-L] export big tables to excel (xlsx)
Sent by:        "SPSSX(r) Discussion" <[hidden email]>

---

>If you don't have xlsx support in your
version, there is an alternative.  There is a Basic script available
from the SPSS Community site named ExportTablesToExcelFiles.  It uses
the COM automation of the installed Excel, so it automatically uses the
format of that version.
>
>Here is a direct link to that file,
or you can find it in the Utilities Collection on the SPSS Community site
(www.ibm.com/developerworks/spssdevcentral)
>https://www.ibm.com/developerworks/mydeveloperworks/files/app?lang=en#/person/270002VCWN/file/8e0dfcb6-aa57-4639-a20e-1780010cfe83
Can these scripts also be used to export all output out from an existing .spo file? (I think the answer is "No")
It would be useful if running something like...
SCRIPT ExportTablesToExcelFiles.wwd someOutput.spo
...under SPSS v20 would generate an excel file with one output item per sheet.
I think I prefer the customoutputfunctions.excelexport method using SPSSINC MODIFY OUTPUT.

=====================
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]

I just tried the program. Works nicely. However, it is designed to get info from the designated output doc. I tried replacing

Set objOutputDoc = objSpssApp.GetDesignatedOutputDoc

 

with:

arg = "d:\some_output.spo"

Set objOutputDoc = objSpssApp.OpenOutputDoc(arg)

objOutputDoc.Visible = True
 

But that didn't work. And that's where my VB knowledge ends. It seems useful to have a production facility job that takes a spo file as its argument and returns a multisheet xls file.

 

Regards,
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: Friday, March 1, 2013 3:50 PM
Subject: Re: [SPSSX-L] export big tables to excel (xlsx)

The script should also work on spo files.

Running it brings up a dialog box that allows you to choose whether tables are all in one file on separate sheets or in separate files.

This script predates MODIFY TABLES and is implemented in Basic, so it can be used without programmability support but only on Windows.

It is doubtless less powerful than MODIFY OUTPUT or MODIFY TABLES but is easier to use.


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




From:        Albert-Jan Roskam <[hidden email]>
To:        [hidden email],
Date:        03/01/2013 07:34 AM
Subject:        Re: [SPSSX-L] export big tables to excel (xlsx)
Sent by:        "SPSSX(r) Discussion" <[hidden email]>

>If you don't have xlsx support in your
version, there is an alternative.  There is a Basic script available
from the SPSS Community site named ExportTablesToExcelFiles.  It uses
the COM automation of the installed Excel, so it automatically uses the
format of that version.
>
>Here is a direct link to that file,
or you can find it in the Utilities Collection on the SPSS Community site
(www.ibm.com/developerworks/spssdevcentral)
>https://www.ibm.com/developerworks/mydeveloperworks/files/app?lang=en#/person/270002VCWN/file/8e0dfcb6-aa57-4639-a20e-1780010cfe83
Can these scripts also be used to export all output out from an existing .spo file? (I think the answer is "No")
It would be useful if running something like...
SCRIPT ExportTablesToExcelFiles.wwd someOutput.spo
...under SPSS v20 would generate an excel file with one output item per sheet.
I think I prefer the customoutputfunctions.excelexport method using SPSSINC MODIFY OUTPUT.

=====================
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