exporting unformatted part of CTABLES to specific Excel range

[raw]

Dear all, I would like to export some results of a table to a specific cell range in Excel.

I normally use “OUTPUT EXPORT” to export entire formatted tables.

In this case I would like to export an unformatted column with the value labels and an unformatted column with the % to Excel.

(I mean that I don’t need the whole Custom table, but only two unformatted columns).

The results should look like this in Excel, in the range A9:B16


data list free/name(a6) q1(a3) .
begin data
item_1 2%
item_2 1%
item_3 20%
item_4 53%
item_5 4%
item_6 6%
item_7 6%
item_8 8%
end data.

Is this possbile in SPSS? In SAS it’s the DDE function.

[Rick Oliver-3]

You can use OMS to export the table to a dataset in the current session. Then you use SAVE TRANSLATE to export the selected variables (columns from the original table) to Excel.

Rick Oliver
Senior Information Developer
IBM Business Analytics (SPSS)
E-mail: [hidden email]



From:        raw <[hidden email]>
To:        [hidden email]
Date:        03/15/2016 10:33 AM
Subject:        exporting unformatted part of CTABLES to specific Excel range
Sent by:        "SPSSX(r) Discussion" <[hidden email]>

---

Dear all, I would like to export some results of a table to a specific cell
range in Excel.

I normally use “OUTPUT EXPORT” to export entire formatted tables.

In this case I would like to export an unformatted column with the value
labels and an unformatted column with the % to Excel.

(I mean that I don’t need the whole Custom table, but only two unformatted
columns).

The results should look like this in Excel, in the range A9:B16


data list free/name(a6) q1(a3) .
begin data
item_1                 2%
item_2                 1%
item_3                 20%
item_4                 53%
item_5                 4%
item_6                 6%
item_7                 6%
item_8                 8%
end data.

Is this possbile in SPSS? In SAS it’s the DDE function.




--
View this message in context: http://spssx-discussion.1045642.n5.nabble.com/exporting-unformatted-part-of-CTABLES-to-specific-Excel-range-tp5731748.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




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

To do this you would need to write a small Basic automation script to get the specific cells and use the Excel VBA library to place them within the Excel sheet.  (You could also use Python scripting, but you would need to install Excel support via an extra Python library.)

On Tuesday, March 15, 2016, raw <[hidden email]> wrote:

Dear all, I would like to export some results of a table to a specific cell
range in Excel.

I normally use “OUTPUT EXPORT” to export entire formatted tables.

In this case I would like to export an unformatted column with the value
labels and an unformatted column with the % to Excel.

(I mean that I don’t need the whole Custom table, but only two unformatted
columns).

The results should look like this in Excel, in the range A9:B16


data list free/name(a6) q1(a3) .
begin data
item_1  2%
item_2  1%
item_3  20%
item_4  53%
item_5  4%
item_6  6%
item_7  6%
item_8  8%
end data.

Is this possbile in SPSS? In SAS it’s the DDE function.




--
View this message in context: http://spssx-discussion.1045642.n5.nabble.com/exporting-unformatted-part-of-CTABLES-to-specific-Excel-range-tp5731748.html
Sent from the SPSSX Discussion mailing list archive at Nabble.com.

=====================
To manage your subscription to SPSSX-L, send a message to
<a href="javascript:;" onclick="_e(event, &#39;cvml&#39;, &#39;[e-mail]&#39;)">LISTSERV@... (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
[hidden email]

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

[raw]

I've been checking the save translate sintax but it does not let me choosing an Excel sheet name or a cell range, which is my aim.

So I guess the solution is what Jon said, or did I miss something?

[Rick Oliver-3]

As of release 24, SAVE TRANSLATE can specify a sheet name, and you can append or replace a sheet -- but it does not support cell ranges.

However, OUTPUT EXPORT supports both. So you could theoretically use SUMMARIZE to create a simple table of the data and export that to Excel.

But the Basic script solution would probably be simpler.

Rick Oliver
Senior Information Developer
IBM Business Analytics (SPSS)
E-mail: [hidden email]



From:        raw <[hidden email]>
To:        [hidden email]
Date:        03/17/2016 01:57 PM
Subject:        Re: exporting unformatted part of CTABLES to specific Excel range
Sent by:        "SPSSX(r) Discussion" <[hidden email]>

---

I've been checking the save translate sintax but it does not let me choosing
an Excel sheet name or a cell range, which is my aim.

So I guess the solution is what Jon said, or did I miss something?



--
View this message in context: http://spssx-discussion.1045642.n5.nabble.com/exporting-unformatted-part-of-CTABLES-to-specific-Excel-range-tp5731748p5731761.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




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

[raw]

I tried to use the xslxwriter module, but I have an error:

  File "<string>", line 2, in <module> 
ImportError: No module named xlsxwriter

do you think is it impossible to use this module in SPSS?


begin program.
import xlsxwriter

# Create a workbook and add a worksheet.
workbook = xlsxwriter.Workbook('C:/Users/Desktop/Expenses01.xlsx')
worksheet = workbook.add_worksheet('myData')  

# Some data we want to write to the worksheet.
expenses = (
    ['Rent', 1000],
    ['Gas',   100],
    ['Food',  300],
    ['Gym',    50],
)

# Start from the first cell. Rows and columns are zero indexed.
row = 5
col = 10

# Iterate over the data and write it out row by row.
for item, cost in (expenses):
    worksheet.write(row, col,     item)
    worksheet.write(row, col + 1, cost)
    row += 1

# Write a total using a formula.
worksheet.write(row, 0, 'Total')
worksheet.write(row, 1, '=SUM(B1:B4)')

workbook.close()
end program.

[Jon Peck]

You need to install the package as it is not part of the Python standard library.  Look here for details

https://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=2&cad=rja&uact=8&ved=0ahUKEwjzw-7Ot-bLAhUskoMKHWeqC7YQFggmMAE&url=http%3A%2F%2Fxlsxwriter.readthedocs.org%2F&usg=AFQjCNHWYZSAWBrSEgyJ_6lgbxrAkjCT6A&sig2=PVpBE-5PRq1x1IhWTHzCEw&bvm=bv.118353311,d.amc

On Tue, Mar 29, 2016 at 4:01 AM, raw <[hidden email]> wrote:

I tried to use the xslxwriter module, but I have an error:

  File "<string>", line 2, in <module>
ImportError: No module named xlsxwriter

do you think is it impossible to use this module in SPSS?


begin program.
import xlsxwriter

# Create a workbook and add a worksheet.
workbook = xlsxwriter.Workbook('C:/Users/Desktop/Expenses01.xlsx')
worksheet = workbook.add_worksheet('myData')

# Some data we want to write to the worksheet.
expenses = (
    ['Rent', 1000],
    ['Gas',   100],
    ['Food',  300],
    ['Gym',    50],
)

# Start from the first cell. Rows and columns are zero indexed.
row = 5
col = 10

# Iterate over the data and write it out row by row.
for item, cost in (expenses):
    worksheet.write(row, col,     item)
    worksheet.write(row, col + 1, cost)
    row += 1

# Write a total using a formula.
worksheet.write(row, 0, 'Total')
worksheet.write(row, 1, '=SUM(B1:B4)')

workbook.close()
end program.



--
View this message in context: http://spssx-discussion.1045642.n5.nabble.com/exporting-unformatted-part-of-CTABLES-to-specific-Excel-range-tp5731748p5731817.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

--

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

[Art Kendall]

If this is a one off just - cut and paste?

[raw]

hi Art, what do you mean exactly?

My final aim is not manging only one table, but several to generate dashboards, something that in SAS or R it's very common and I was wandering if it was possible with SPSS - Python

[Art Kendall]

I was asking whether you were doing this one or even just a few times.
Evidently not.

[raw]

I can see the xlsxwriter-0.8.4-py27_0.tar file in  C:\Anaconda\pkgs, but again launching the code I posted above, I have as a result :


ImportError: No module named xlsxwriter

Am I missing something?

If I use  the code in JetBrains PyCharm  it works, but not in SPSS

[Jon Peck]

That suggests that you don't have the Anaconda site-packages directory on your Python search path for Statistics.  If you are using the Python distribution that is installed by Statistics, you can add that Anaconda directory by creating a sitecustomize.py file in the Statistics Python site-packages directory with contents like this.

import sys

sys.path.append("c:/Anaconda/lib/site-packages")

On Thu, Apr 7, 2016 at 7:34 AM, raw <[hidden email]> wrote:

I can see the xlsxwriter-0.8.4-py27_0.tar file in  C:\Anaconda\pkgs, but
again launching the code I posted above, I have as a result :


ImportError: No module named xlsxwriter

Am I missing something?

If I use  the code in JetBrains PyCharm  it works, but not in SPSS



--
View this message in context: http://spssx-discussion.1045642.n5.nabble.com/exporting-unformatted-part-of-CTABLES-to-specific-Excel-range-tp5731748p5731892.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

--

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