Export more than 256 vars to Excel

 

Download as .sps Shows as plain text

 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
* To export more than 256 variables to excel.

*//////////////////////////////.
DEFINE !exportToExcel(dataFile=!TOKENS(1) / excelFile=!TOKENS(1) /nbPerSheet=!TOKENS(1) )

!LET !nbPerSheet2=!CONCAT('n',!nbPerSheet)
GET FILE=!datafile.
N OF CASES 1.
FLIP.
COMPUTE casen=$CASENUM.
COMPUTE nobreak=1.
ADD FILES FILE=* /BY=nobreak /LAST=last.
COMPUTE m=MOD(casen -1,!nbPerSheet).
DO IF last=0.
- WRITE OUTFILE='c:\\temp\\var names.txt' /case_lbl.
ELSE.
- LOOP cnt=m TO !nbPerSheet - 1.
-   WRITE OUTFILE='c:\\temp\\var names.txt' /case_lbl.
- END LOOP.
END IF.
EXECUTE.

DATA LIST FILE='c:\\temp\\var names.txt' FREE /n1 TO !nbPerSheet2 (!nbPerSheet A20).
EXECUTE.

VECTOR n=n1 TO !nbPerSheet2.
STRING theseNames(A50) mysheet(A10).
DO IF n1 <> !nbPerSheet2.
- COMPUTE theseNames=CONCAT(n1," TO ",!nbpersheet2).
ELSE.
- COMPUTE theseNames=n1.
END IF.
COMPUTE mysheet=CONCAT('sheet',LTRIM(STRING($CASENUM,F4))).

WRITE OUTFILE='c:\\temp\\save.sps' /"SAVE TRANSLATE "
 /" /CONNECT='DSN=Excel Files;DBQ=C:\\temp\\myexcel.xls;DriverId=790;MaxBufferSize=2048;PageTimeout=5;' "
 /" /TABLE='"mysheet "' "
 /" /TYPE=ODBC"
 /" /KEEP=" theseNames
 /" /REPLACE."
 /"EXECUTE.".
EXECUTE.

GET FILE=!datafile.
INSERT FILE='c:\\temp\\save.sps' .
!ENDDEFINE.
*//////////////////////////////.


!exportToExcel	dataFile='c:\\Program files\\spss\\employee data.sav' 
		excelFile='C:\\temp\\myexcel.xls'
		nbPerSheet=4.
Related pages

...

Navigate from here