Output Export to Excel - problem exporting large table

[Brad Kirby]

Hi,

I'm having problems exporting a summary table from an SPSS v17 Outpur viewer
to Excel 2003.

The summary table is created like this:

temp.
sel if admitotherhosp=1.
SUMMARIZE
  /TABLES=Age AdmitType ApDiag ReasonUnitAdmit LOS UnitOutcome LOShosp
OutcomeHosp  BY Unit BY Year
  /FORMAT=VALIDLIST NOCASENUM TOTAL
  /MISSING=VARIABLE
  /CELLS=COUNT
/TITLE='Data on All Transferred Admissions by Unit and Year (ICU and
Combined Units, 2004-2008)'.

This produces a fairly large table about 9 Columns by 16,000 or so rows.

When I right click -> Export the selected table to *.xls, the destination
file is created (circa 600KB in size) but only table and colum names are
visible, i.e. the 16,000 rows of data disappear. Also, when I right click ->
copy & paste the data into a blank Excel workbook, the same thing happens -
headings but no data.

Smaller summary tables export fine.

I can export the data to tab delimited text file, and then read that into
excel, but this spreads data over rows and requires too much manual formatting.

Is there a known problem exporting large tables to excel?

Thanks
Brad

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

[SPSS Support]

Hi Brad,
  Which version of Statistics 17 are you running? (See Help->About). There was a bug reported in regard to exporting large tables to Excel from 17.0.1. In this bug report, the file was created but the table was not there. There is no mention of the headings being present. This bug was reported fixed in 17.0.2. Have you downloaded the 17.0.2 patch yet from http://support.spss.com ?

David Matheson
SPSS Statistical Support

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Brad Kirby
Sent: Friday, April 17, 2009 10:42 AM
To: [hidden email]
Subject: Output Export to Excel - problem exporting large table

Hi,

I'm having problems exporting a summary table from an SPSS v17 Outpur viewer
to Excel 2003.

The summary table is created like this:

temp.
sel if admitotherhosp=1.
SUMMARIZE
  /TABLES=Age AdmitType ApDiag ReasonUnitAdmit LOS UnitOutcome LOShosp
OutcomeHosp  BY Unit BY Year
  /FORMAT=VALIDLIST NOCASENUM TOTAL
  /MISSING=VARIABLE
  /CELLS=COUNT
/TITLE='Data on All Transferred Admissions by Unit and Year (ICU and
Combined Units, 2004-2008)'.

This produces a fairly large table about 9 Columns by 16,000 or so rows.

When I right click -> Export the selected table to *.xls, the destination
file is created (circa 600KB in size) but only table and colum names are
visible, i.e. the 16,000 rows of data disappear. Also, when I right click ->
copy & paste the data into a blank Excel workbook, the same thing happens -
headings but no data.

Smaller summary tables export fine.

I can export the data to tab delimited text file, and then read that into
excel, but this spreads data over rows and requires too much manual formatting.

Is there a known problem exporting large tables to excel?

Thanks
Brad

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

[Bob Schacht-3]

At 09:41 AM 4/17/2009, SPSS Support wrote:

>Hi Brad,
>   Which version of Statistics 17 are you running? (See Help->About).
> There was a bug reported in regard to exporting large tables to Excel
> from 17.0.1. In this bug report, the file was created but the table was
> not there. There is no mention of the headings being present. This bug
> was reported fixed in 17.0.2. Have you downloaded the 17.0.2 patch yet
> from http://support.spss.com ?
>
>David Matheson
>SPSS Statistical Support

Is there a place where known problems in exporting/importing between SPSS
and Excel have been summarized? We do a lot of that, and I want to know
where the problems occur so that I can deal with them.

Thanks,
Bob Schacht

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

[necia]

The whole thing is. I'd like to use the same command file for all the
processing of 40 files, having to change only two file names on FILE
HANDLEs at the beginning of a syntax file, when I am analysing a new
month's data.

I read in a text file, capturing the syntax in the process, and have used
the generated code to read in the data.  Then the file has to go some
additions, renaming, and dropping of variables, aggregating, merging etc,
and ultimately gets saved as system file, for that month and data source.

There are some interim files created because of aggregate commands etc.
There will be 40 input files altogether that must go thru these girations.
It would be nice to make the process of changing to a new file (and
file names) as fool proof as possible.

I would like to use FILE HANDLES for the INPUT and OUTPUT files, which
I'd put at the beginning of the syntax file for use later.

My thought is: for each month's data, a person would only have to change
the file names on the initial FILE HANDLEs,  for the next month's data.
Less chance for error than changing several complete file names, buried in
the long program.

OK so I set up FILE HANDLES for the beginning and ending files; change
those with each month's data change, and run the whole syntax file.
Sounds like a plan.

Got foxed on the very first FILE HANDLE!  Apparently you cannot use a file
handle to represent a text file.  Lots of other kinds of files, but not
text files.  True?  Any way around this?

There are GET commands in SPSS for SAS, OSIRIUS, BMDP, and more, but not
text, (.txt).

So, do I have to write out the whole file name for a text file I need to
read in (which I have done so far, but shorter process is desired, in case
other people need to run these files, and aren't as familiar as I am with
SPSS, (30+ years and counting).  BTW it is going to be hard to call it
PASW.

If you read all the way through, thank you! Any help is appreciated. TGIF.
Cheers,
Necia

Necia A. Black, Ph.D.           e-mail:  [hidden email]
246 Computing Center            Web URL:http://www.acsu.buffalo.edu/~black
SUNY-Buffalo
Buffalo, New York 14260

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

[Oliver, Richard]

There are two commands for reading text data, GET DATA (with /TYPE=TXT) and DATA LIST. AFAIK, file handles can be used on both command to represent the text file to read, as in:

 

FILE HANDLE fred /NAME='c:\mypath\myfile.txt'.

GET DATA
  /TYPE=TXT
  /FILE='fred'

[remaining GET DATA specifications]

---

From: SPSSX(r) Discussion on behalf of necia
Sent: Fri 4/17/2009 4:02 PM
To: [hidden email]
Subject: Can you read in a .txt data file using a preceding FILE HANDLE?

The whole thing is. I'd like to use the same command file for all the
processing of 40 files, having to change only two file names on FILE
HANDLEs at the beginning of a syntax file, when I am analysing a new
month's data.

I read in a text file, capturing the syntax in the process, and have used
the generated code to read in the data.  Then the file has to go some
additions, renaming, and dropping of variables, aggregating, merging etc,
and ultimately gets saved as system file, for that month and data source.

There are some interim files created because of aggregate commands etc.
There will be 40 input files altogether that must go thru these girations.
It would be nice to make the process of changing to a new file (and
file names) as fool proof as possible.

I would like to use FILE HANDLES for the INPUT and OUTPUT files, which
I'd put at the beginning of the syntax file for use later.

My thought is: for each month's data, a person would only have to change
the file names on the initial FILE HANDLEs,  for the next month's data.
Less chance for error than changing several complete file names, buried in
the long program.

OK so I set up FILE HANDLES for the beginning and ending files; change
those with each month's data change, and run the whole syntax file.
Sounds like a plan.

Got foxed on the very first FILE HANDLE!  Apparently you cannot use a file
handle to represent a text file.  Lots of other kinds of files, but not
text files.  True?  Any way around this?

There are GET commands in SPSS for SAS, OSIRIUS, BMDP, and more, but not
text, (.txt).

So, do I have to write out the whole file name for a text file I need to
read in (which I have done so far, but shorter process is desired, in case
other people need to run these files, and aren't as familiar as I am with
SPSS, (30+ years and counting).  BTW it is going to be hard to call it
PASW.

If you read all the way through, thank you! Any help is appreciated. TGIF.
Cheers,
Necia

Necia A. Black, Ph.D.           e-mail:  [hidden email]
246 Computing Center            Web URL:http://www.acsu.buffalo.edu/~black
SUNY-Buffalo
Buffalo, New York 14260

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

Hi,

FILE HANDLE can be used with GET DATA; I just did that. You don't always need to quote the handles. Note that you can also use FILE HANDLE to specify a dir, and then nest that handle into another handle:
file handle mydir /name = '\\server\share\folder1\folder2'.
file handle myfile /name = 'mydir/somefile.sav'.

I generally prefer to use identical names across months/years, but store them in separate folders. I'm also trying to find a way to have a sp file that needs as little changes as possible. Maybe this would also work:
file handle myfile2 /name = 'somefile.sav'.
define !month "12" !enddefine.
...
get file = 'mydir/' + !month + '/myfile2.sav'.

Btw, for AGGREGATE you don't need intermediate files:
DATASET DECLARE blah.
AGGR OUT = blah / BREAK = id / n = n.
...

Cheers!!
Albert-Jan


--- On Fri, 4/17/09, Oliver, Richard <[hidden email]> wrote:

> From: Oliver, Richard <[hidden email]>
> Subject: Re: Can you read in a .txt data file using a preceding FILE              HANDLE?
> To: [hidden email]
> Date: Friday, April 17, 2009, 11:11 PM
> Can you
> read in a .txt data file using a preceding FILE
> HANDLE?
>
>
>
>
> There are two commands for reading text data, GET
> DATA (with /TYPE=TXT) and DATA LIST. AFAIK, file handles can
> be used on both command to represent the text file to read,
> as in:
>
> FILE HANDLE fred
> /NAME='c:\mypath\myfile.txt'.
> GET DATA
>   /TYPE=TXT
>   /FILE='fred'
> [remaining GET
> DATA specifications]
>
>
>
> From: SPSSX(r)
> Discussion on behalf of necia
> Sent: Fri 4/17/2009 4:02 PM
> To: [hidden email]
> Subject: Can you read in a .txt data file using a
> preceding FILE HANDLE?
>
>
>
> The whole thing is. I'd like to use
> the same command file for all the
> processing of 40 files, having to change only two file
> names on FILE
> HANDLEs at the beginning of a syntax file, when I am
> analysing a new
> month's data.
>
> I read in a text file, capturing the syntax in the process,
> and have used
> the generated code to read in the data.  Then the file
> has to go some
> additions, renaming, and dropping of variables,
> aggregating, merging etc,
> and ultimately gets saved as system file, for that month
> and data source.
>
> There are some interim files created because of aggregate
> commands etc.
> There will be 40 input files altogether that must go thru
> these girations.
> It would be nice to make the process of changing to a new
> file (and
> file names) as fool proof as possible.
>
> I would like to use FILE HANDLES for the INPUT and OUTPUT
> files, which
> I'd put at the beginning of the syntax file for use
> later.
>
> My thought is: for each month's data, a person would
> only have to change
> the file names on the initial FILE HANDLEs,  for the
> next month's data.
> Less chance for error than changing several complete file
> names, buried in
> the long program.
>
> OK so I set up FILE HANDLES for the beginning and ending
> files; change
> those with each month's data change, and run the whole
> syntax file.
> Sounds like a plan.
>
> Got foxed on the very first FILE HANDLE!  Apparently
> you cannot use a file
> handle to represent a text file.  Lots of other kinds
> of files, but not
> text files.  True?  Any way around this?
>
> There are GET commands in SPSS for SAS, OSIRIUS, BMDP, and
> more, but not
> text, (.txt).
>
> So, do I have to write out the whole file name for a text
> file I need to
> read in (which I have done so far, but shorter process is
> desired, in case
> other people need to run these files, and aren't as
> familiar as I am with
> SPSS, (30+ years and counting).  BTW it is going to be
> hard to call it
> PASW.
>
> If you read all the way through, thank you! Any help is
> appreciated. TGIF.
> Cheers,
> Necia
>
> Necia A. Black,
> Ph.D.
> e-mail:  [hidden email]
> 246 Computing
> Center
> Web URL:http://www.acsu.buffalo.edu/~black
> SUNY-Buffalo
> Buffalo, New York 14260
>
> =====================
> 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

[Eric Langston]

I agree.  I export tables from SPSS to Excel on a daily basis.

Also, is there a list of "Known Issues" for SPSS, pardon me, PASW Stats
17.0.2?

Thanks,
-Eric

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of
Bob Schacht
Sent: Friday, April 17, 2009 3:30 PM
To: [hidden email]
Subject: Re: SPSS/EXCEL problems

At 09:41 AM 4/17/2009, SPSS Support wrote:
>Hi Brad,
>   Which version of Statistics 17 are you running? (See Help->About).
> There was a bug reported in regard to exporting large tables to Excel
> from 17.0.1. In this bug report, the file was created but the table
was
> not there. There is no mention of the headings being present. This bug
> was reported fixed in 17.0.2. Have you downloaded the 17.0.2 patch yet
> from http://support.spss.com ?
>
>David Matheson
>SPSS Statistical Support

Is there a place where known problems in exporting/importing between
SPSS
and Excel have been summarized? We do a lot of that, and I want to know
where the problems occur so that I can deal with them.

Thanks,
Bob Schacht

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