SPSS to Excel: file full warning
Locked
 
|
I’m helping someone to prepare a presentation for the University of the Third Age (U3A) on 18th October, dealing with age differences in self-reported well-being. She has no access to SPSS (and would be a complete newbie anyway) so has to work instead with Excel, mainly for charts. Her specific interest is in investigating the apparent U-shaped distributions across age groups, but controlling for various demographic groupings and combinations, on the following [variables] (all measured on 0 – 10 scales): 1: [satis] Overall how satisfied are you with your life nowadays? (Where nought is not at all satisfied and 10 is completely satisfied) 2: [happy] Overall how happy did you feel yesterday? (Where nought is not at all happy and 10 is completely happy) 3: [worth] Overall to what extent do you feel that the things you do in your life are worthwhile? (Where nought is not at all worthwhile and 10 is completely worthwhile) 4: [anxious] On a scale where nought is not at all anxious and 10 is completely anxious, overall how anxious did you feel yesterday? I have a cumulative file with 307,894 cases and 314 (selected) variables from the (UK) Office of National Statistics (ONS) Annual Population Survey (APS) 2012 which I have reduced to a working data set with 26 variables of interest, planning to to export the data to Excel to send to her. When I saved the reduced file to Excel I got a warning message: SAVE TRANSLATE OUTFILE='C:\Users\John\Desktop\Aileen Hingston\ons7091jfh.xls' /TYPE=XLS /VERSION=2 /MAP /REPLACE /FIELDNAMES. Warning # 6451 The output file is full - no more cases can be written. Data written to C:\Users\John\Desktop\Aileen Hingston\ons7091jfh.xls. 26 variables and 16383 cases written to range: SPSS. The resulting Excel file now only contains 16,384 cases: is that an Excel file size limit, or can I change it in SPSS? For now I can continue exporting the spv file to Word rtf, editing it and sending her that so that she can copy or transcribe anything she needs for her talk. Incidentally, it’s the first time I’ve ever tried File >> Export >> Word rtf: a bit fiddly to edit afterwards, but a fantastic facility. Must play with the ppt option as this could save Aileen a lot of work. Thanks in advance for all comments and suggestions. John F Hall (Mr) Email: [hidden email] Website: www.surveyresearch.weebly.com |
Re: SPSS to Excel: file full warning
|
Hi John,
That's the limit of that excel version (excel 2.1). If you use biff version 8 ("/version=8"), the limit is much higher. And the excel sheets looks far less crappy (no courier font). But personally I'd export it to .csv. 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? ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|
Automatic reply: SPSS to Excel: file full warning
|
|
I am PTOB 2-5 October - I will respond to email upon my return Monday 8 October. Please contact:
·
[hidden email] for questions about or assistance with MIP ProjectPages
(page functionality, content on the pages, i.e. PI name, proejct title, etc.)
·
[hidden email] for questions about charge
numbers
·
[hidden email]
for quesitons or assiatcne with MIP processes or the webiste
·
[hidden email] for assistance with
other Innovation Zone sites, such as CI&T InZone Regards, Mary Lou |
Re: SPSS to Excel: file full warning
Administrator
|
In reply to this post by John F Hall
I have no problem writing 70,000 cases to Excel.
OTOH if you insist on writing to version 2 excel then repeat after me... "Dr. Dr. it hurts when I do this!!!" input program. loop caseid=1 to 70000. do repeat v=v1 to v23. compute v=normal(1). end repeat. end case. end loop. end file. end input program. exe. SAVE TRANSLATE OUTFILE='G:\temp\xlstest.xls' /TYPE=XLS /VERSION=8 /MAP /REPLACE /FIELDNAMES /CELLS=VALUES . Data written to G:\temp\xlstest.xls. 24 variables and 70000 cases written to range: SPSS. Variable: CASEID Type: Number Width: 8 Dec: 6 Variable: V1 Type: Number Width: 8 Dec: 6 Variable: V2 Type: Number Width: 8 Dec: 6 Variable: V3 Type: Number Width: 8 Dec: 6 Variable: V4 Type: Number Width: 8 Dec: 6 Variable: V5 Type: Number Width: 8 Dec: 6 Variable: V6 Type: Number Width: 8 Dec: 6 Variable: V7 Type: Number Width: 8 Dec: 6 Variable: V8 Type: Number Width: 8 Dec: 6 Variable: V9 Type: Number Width: 8 Dec: 6 Variable: V10 Type: Number Width: 8 Dec: 6 Variable: V11 Type: Number Width: 8 Dec: 6 Variable: V12 Type: Number Width: 8 Dec: 6 Variable: V13 Type: Number Width: 8 Dec: 6 Variable: V14 Type: Number Width: 8 Dec: 6 Variable: V15 Type: Number Width: 8 Dec: 6 Variable: V16 Type: Number Width: 8 Dec: 6 Variable: V17 Type: Number Width: 8 Dec: 6 Variable: V18 Type: Number Width: 8 Dec: 6 Variable: V19 Type: Number Width: 8 Dec: 6 Variable: V20 Type: Number Width: 8 Dec: 6 Variable: V21 Type: Number Width: 8 Dec: 6 Variable: V22 Type: Number Width: 8 Dec: 6 Variable: V23 Type: Number Width: 8 Dec: 6
Please reply to the list and not to my personal email.
Those desiring my consulting or training services please feel free to email me. --- "Nolite dare sanctum canibus neque mittatis margaritas vestras ante porcos ne forte conculcent eas pedibus suis." Cum es damnatorum possederunt porcos iens ut salire off sanguinum cliff in abyssum?" |
Re: SPSS to Excel: file full warning
|
|
In reply to this post by John F Hall
You are using a very old Excel format that
is limited to 16K rows. Newer Excel formats have much bigger limits,
but you may still have trouble with 300,00+ cases.
Jon Peck (no "h") aka Kim Senior Software Engineer, IBM [hidden email] new phone: 720-342-5621 From: John F Hall <[hidden email]> To: [hidden email] Date: 10/02/2012 03:39 AM Subject: [SPSSX-L] SPSS to Excel: file full warning Sent by: "SPSSX(r) Discussion" <[hidden email]> I’m helping someone to prepare a presentation for the University of the Third Age (U3A) on 18th October, dealing with age differences in self-reported well-being. She has no access to SPSS (and would be a complete newbie anyway) so has to work instead with Excel, mainly for charts. Her specific interest is in investigating the apparent U-shaped distributions across age groups, but controlling for various demographic groupings and combinations, on the following [variables] (all measured on 0 – 10 scales): 1: [satis] Overall how satisfied are you with your life nowadays? (Where nought is not at all satisfied and 10 is completely satisfied) 2: [happy] Overall how happy did you feel yesterday? (Where nought is not at all happy and 10 is completely happy) 3: [worth] Overall to what extent do you feel that the things you do in your life are worthwhile? (Where nought is not at all worthwhile and 10 is completely worthwhile) 4: [anxious] On a scale where nought is not at all anxious and 10 is completely anxious, overall how anxious did you feel yesterday? I have a cumulative file with 307,894 cases and 314 (selected) variables from the (UK) Office of National Statistics (ONS) Annual Population Survey (APS) 2012 which I have reduced to a working data set with 26 variables of interest, planning to to export the data to Excel to send to her. When I saved the reduced file to Excel I got a warning message: SAVE TRANSLATE OUTFILE='C:\Users\John\Desktop\Aileen Hingston\ons7091jfh.xls' /TYPE=XLS /VERSION=2 /MAP /REPLACE /FIELDNAMES. Warning # 6451 The output file is full - no more cases can be written. Data written to C:\Users\John\Desktop\Aileen Hingston\ons7091jfh.xls. 26 variables and 16383 cases written to range: SPSS. The resulting Excel file now only contains 16,384 cases: is that an Excel file size limit, or can I change it in SPSS? For now I can continue exporting the spv file to Word rtf, editing it and sending her that so that she can copy or transcribe anything she needs for her talk. Incidentally, it’s the first time I’ve ever tried File >> Export >> Word rtf: a bit fiddly to edit afterwards, but a fantastic facility. Must play with the ppt option as this could save Aileen a lot of work. Thanks in advance for all comments and suggestions. John F Hall (Mr) Email: johnfhall@... Website: www.surveyresearch.weebly.com |
Re: SPSS to Excel: file full warning
|
|
Thanks to everyone for pointing this out. As an export newbie I just clicked on the first one I could see in the pane. In my version of SPSS 19, the only other option is Excel 2.1. Oops! Didn’t scroll down far enough! Software designers, please note. Newbies never see everything they need. Bingo! All 307894 cases written to *.xlsx. Gets it down from 135 mb in SPSS to 28.8 mb in Excel, but a tad big for email attachments: I can always split it into 4 bits. Meanwhile the “Pursuit of Happiness” (as guaranteed by the US constitution, but not the UK’s, which doesn’t have one) continues. Next request will probably be for help with overlaid charts when I get stuck again. John F Hall (Mr) Email: [hidden email] Website: www.surveyresearch.weebly.com From: Jon K Peck [mailto:[hidden email]] You are using a very old Excel format that is limited to 16K rows. Newer Excel formats have much bigger limits, but you may still have trouble with 300,00+ cases.
|
Re: SPSS to Excel: file full warning
|
Administrator
|
You should look into something like dropbox.
https://www.dropbox.com/features Sending stuff like this via email is so 20th century ;-) --- "Bingo! All 307894 cases written to *.xlsx. Gets it down from 135 mb in SPSS to 28.8 mb in Excel, but a tad big for email attachments: I can always split it into 4 bits."
Please reply to the list and not to my personal email.
Those desiring my consulting or training services please feel free to email me. --- "Nolite dare sanctum canibus neque mittatis margaritas vestras ante porcos ne forte conculcent eas pedibus suis." Cum es damnatorum possederunt porcos iens ut salire off sanguinum cliff in abyssum?" |
Re: SPSS to Excel: file full warning
|
|
David
My time is too precious to download and learn new software that I'll only use once in a blue moon. The only time I've used it was to download the audio files from the 1984 interview with the late Dr Mark Abrams, widely acknowledged as the father of market and survey research in the UK and with a research career spanning six decades. I worked closely with him from 1976 until the late 1980s: if you want to learn a bit more about where I come from, have a look at the transcripts on my website. http://surveyresearch.weebly.com/an-interview-with-mark-abrams-transcripts.html Once, impatient for preliminary results from the Quality of Life in Britain survey, and gazing at the piles of SPSS output laid out on the corridor floor, he declared, "Computers! Computers! It's quicker to count them by foot!" He also used to say, "If it's worth saying, you can say it in percentages.", but he might have conceded a point to today's graphics. John -----Original Message----- From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of David Marso Sent: 02 October 2012 16:36 To: [hidden email] Subject: Re: SPSS to Excel: file full warning You should look into something like dropbox. https://www.dropbox.com/features Sending stuff like this via email is so 20th century ;-) --- "Bingo! All 307894 cases written to *.xlsx. Gets it down from 135 mb in SPSS to 28.8 mb in Excel, but a tad big for email attachments: I can always split it into 4 bits." John F Hall wrote > Thanks to everyone for pointing this out. > > > > As an export newbie I just clicked on the first one I could see in the > pane. In my version of SPSS 19, the only other option is Excel 2.1. > Oops! Didn’t scroll down far enough! Software designers, please note. > Newbies never see everything they need. > > > > Bingo! All 307894 cases written to *.xlsx. Gets it down from 135 mb > in SPSS to 28.8 mb in Excel, but a tad big for email attachments: I > can always split it into 4 bits. > > > > Meanwhile the “Pursuit of Happiness” (as guaranteed by the US > constitution, but not the UK’s, which doesn’t have one) continues. > Next request will probably be for help with overlaid charts when I get > stuck again. > > > > > > John F Hall (Mr) > > > > Email: > johnfhall@ > > > Website: www.surveyresearch.weebly.com > <http://surveyresearch.weebly.com/> > > > > > > > > > > > > From: Jon K Peck [mailto: > peck@.ibm > ] > Sent: 02 October 2012 14:45 > To: John F Hall > Cc: > SPSSX-L@.uga > Subject: Re: [SPSSX-L] SPSS to Excel: file full warning > > > > You are using a very old Excel format that is limited to 16K rows. > Newer Excel formats have much bigger limits, but you may still have > trouble with 300,00+ cases. > > Jon Peck (no "h") aka Kim > Senior Software Engineer, IBM > peck@.ibm > new phone: 720-342-5621 > > > > > From: John F Hall < > johnfhall@ > > > To: > SPSSX-L@.uga > > Date: 10/02/2012 03:39 AM > Subject: [SPSSX-L] SPSS to Excel: file full warning > Sent by: "SPSSX(r) Discussion" < > SPSSX-L@.uga > > > > _____ > > > > > I’m helping someone to prepare a presentation for the University of > the Third Age (U3A) on 18th October, dealing with age differences in > self-reported well-being. She has no access to SPSS (and would be a > complete newbie anyway) so has to work instead with Excel, mainly for > charts. > > Her specific interest is in investigating the apparent U-shaped > distributions across age groups, but controlling for various > demographic groupings and combinations, on the following [variables] > (all measured on > 0 – 10 scales): > > 1: [satis] > Overall how satisfied are you with your life nowadays? > (Where nought is not at all satisfied and 10 is completely satisfied) > 2: [happy] > Overall how happy did you feel yesterday? > (Where nought is not at all happy and 10 is completely happy) > 3: [worth] > Overall to what extent do you feel that the things you do in your life > are worthwhile? > (Where nought is not at all worthwhile and 10 is completely > worthwhile) > 4: [anxious] > On a scale where nought is not at all anxious and 10 is completely > anxious, overall how anxious did you feel yesterday? > > I have a cumulative file with 307,894 cases and 314 (selected) > variables from the (UK) Office of National Statistics (ONS) Annual > Population Survey > (APS) 2012 which I have reduced to a working data set with 26 > variables of interest, planning to to export the data to Excel to send > to her. When I saved the reduced file to Excel I got a warning message: > > SAVE TRANSLATE OUTFILE='C:\Users\John\Desktop\Aileen > Hingston\ons7091jfh.xls' > /TYPE=XLS > /VERSION=2 > /MAP > /REPLACE > /FIELDNAMES. > > Warning # 6451 > The output file is full - no more cases can be written. > > Data written to C:\Users\John\Desktop\Aileen Hingston\ons7091jfh.xls. > 26 variables and 16383 cases written to range: SPSS. > > > The resulting Excel file now only contains 16,384 cases: is that an > Excel file size limit, or can I change it in SPSS? > > For now I can continue exporting the spv file to Word rtf, editing it > and sending her that so that she can copy or transcribe anything she > needs for her talk. > > Incidentally, it’s the first time I’ve ever tried File >> Export >> > Word > rtf: a bit fiddly to edit afterwards, but a fantastic facility. Must > play with the ppt option as this could save Aileen a lot of work. > > Thanks in advance for all comments and suggestions. > > John F Hall (Mr) > > Email: <mailto: > johnfhall@ > > > johnfhall@ > > Website: <http://surveyresearch.weebly.com/> > www.surveyresearch.weebly.com ----- Please reply to the list and not to my personal email. Those desiring my consulting or training services please feel free to email me. -- View this message in context: http://spssx-discussion.1045642.n5.nabble.com/SPSS-to-Excel-file-full-warning-tp5715428p5715436.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 |
Re: SPSS to Excel: file full warning
|
|
In reply to this post by David Marso
>
>You should look into something like dropbox. >https://www.dropbox.com/features >Sending stuff like this via email is so 20th century ;-) > >--- >"Bingo! All 307894 cases written to *.xlsx. Gets it down from 135 mb in >SPSS to 28.8 mb in Excel, but a tad big for email attachments: I can always >split it into 4 bits." > > I'm suprised that xlsx is smaller than sav, given that xlsx is xml. I thought that .sav was pretty compact, with its compression. Csv (e.g. tab-separated) + zip will still give you smaller, email-able files ;-) ===================== 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 |
Re: SPSS to Excel: file full warning
|
|
xlsx is an XML format file - sort of. It
is actually compressed like a zip file. If you open one with Winzip,
you can see the compression ratios - there are actually several different
xml files in the xlsx container.
Traditional SPSS sav file compression uses a case-by-case compression scheme that is tuned for maximum effectiveness on small integers and has good casewise reading performance but does nothing for fractional or large values. New versions of SPSS Statistics have a new compression scheme available -zsav. Taking a dataset of uniform random numbers, zsav is about 20% smaller than sav. Saving it as xlsx produces a file 69% larger than zsav. YMMV. Jon Peck (no "h") aka Kim Senior Software Engineer, IBM [hidden email] new phone: 720-342-5621 From: Albert-Jan Roskam <[hidden email]> To: [hidden email] Date: 10/02/2012 01:09 PM Subject: Re: [SPSSX-L] SPSS to Excel: file full warning Sent by: "SPSSX(r) Discussion" <[hidden email]> > >You should look into something like dropbox. >https://www.dropbox.com/features >Sending stuff like this via email is so 20th century ;-) > >--- >"Bingo! All 307894 cases written to *.xlsx. Gets it down from 135 mb in >SPSS to 28.8 mb in Excel, but a tad big for email attachments: I can always >split it into 4 bits." > > I'm suprised that xlsx is smaller than sav, given that xlsx is xml. I thought that .sav was pretty compact, with its compression. Csv (e.g. tab-separated) + zip will still give you smaller, email-able files ;-) ===================== 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 |
Re: SPSS to Excel: file full warning
|
|
>________________________________
> From: Jon K Peck <[hidden email]> >To: [hidden email] >Sent: Tuesday, October 2, 2012 9:31 PM >Subject: Re: [SPSSX-L] SPSS to Excel: file full warning > > >xlsx is an XML format file - sort of. It is actually compressed like a zip file. If you open one with Winzip, you can see the compression ratios - there are actually several different xml files in the xlsx container. > >Traditional SPSS sav file compression uses a case-by-case compression scheme that is tuned for maximum effectiveness on small integers and has good casewise reading performance but does nothing for fractional or large values. > >New versions of SPSS Statistics have a new compression scheme available -zsav. Taking a dataset of uniform random numbers, zsav is about 20% smaller than sav. Saving it as xlsx produces a file 69% larger than zsav. YMMV. > Interesting. How does one invoke the zsav compression? Or is this done by default? Does it also mean that opening/working with a compressed data file is slower? Or is this less relevant because many computers nowadays have multiple cores? I read that cache compression in spss server is hyperthreaded and that, all in all, it is faster, despite of the additional compression overhead. ===================== 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 |
Re: SPSS to Excel: file full warning
|
|
See below.
Jon Peck (no "h") aka Kim Senior Software Engineer, IBM [hidden email] new phone: 720-342-5621 From: Albert-Jan Roskam <[hidden email]> To: [hidden email] Date: 10/05/2012 03:15 PM Subject: Re: [SPSSX-L] SPSS to Excel: file full warning Sent by: "SPSSX(r) Discussion" <[hidden email]> >________________________________ > From: Jon K Peck <[hidden email]> >To: [hidden email] >Sent: Tuesday, October 2, 2012 9:31 PM >Subject: Re: [SPSSX-L] SPSS to Excel: file full warning > > >xlsx is an XML format file - sort of. It is actually compressed like a zip file. If you open one with Winzip, you can see the compression ratios - there are actually several different xml files in the xlsx container. > >Traditional SPSS sav file compression uses a case-by-case compression scheme that is tuned for maximum effectiveness on small integers and has good casewise reading performance but does nothing for fractional or large values. > >New versions of SPSS Statistics have a new compression scheme available -zsav. Taking a dataset of uniform random numbers, zsav is about 20% smaller than sav. Saving it as xlsx produces a file 69% larger than zsav. YMMV. > Interesting. How does one invoke the zsav compression? Or is this done by default? Does it also mean that opening/working with a compressed data file is slower? >>>Use /ZCOMPRESSED on the SAVE command. I don't have timing data handy, but the reduced volume of bytes to transfer would typically mean that these files are faster, since GET and SAVE are i/o bound. There is some threading in the i/o, but for many procedures you would still be i/o bound, so fewer bytes to move probably provides additional performance. Or is this less relevant because many computers nowadays have multiple cores? I read that cache compression in spss server is hyperthreaded and that, all in all, it is faster, despite of the additional compression overhead. ===================== 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 |
«
Return to SPSSX Discussion
|
1 view|%1 views
| Free forum by Nabble | Edit this page |