I have some output from SPSS that I've exported as a .txt file (but also as various Excel formats). Below is a snippet from that file. I need the observed and expected percentages for correlation, and this will happen for hundreds of these outputs (many different log linear models). Currently it is formatted with spaces as text, and as Excel it has the whole row in a single cell. I am trying to avoid having to write code to decipher this, as that will also be fraught given the overall size of the outputs, the location of spaces in them etc.
Does anyone have any ideas?
One clear solution is to use a different package, however the ability to specify polynomial contrasts and have the weights calculated for me in the SPSS LOGLINEAR syntax cannot be achieved as easily elsewhere (i.e. it might be possible but it is beyond my skills).
Observed, Expected Frequencies and Residuals
Factor Code OBS. count & PCT. EXP. count & PCT. Residual Std. Resid. Adj. Resid.
deg_code 0
res_code 100
edge_cod 2.520 13.00 ( .29) 112.75 ( 2.56) -99.7500 -9.3941 -9.8118
edge_cod 0.630 40.00 ( .91) 112.75 ( 2.56) -72.7500 -6.8513 -7.1560
edge_cod 0.315 130.00 ( 2.95) 112.75 ( 2.56) 17.2500 1.6245 1.6968
edge_cod 0.105 194.00 ( 4.40) 112.75 ( 2.56) 81.2500 7.6518 7.9921
res_code 85
edge_cod 2.520 21.00 ( .48) 112.75 ( 2.56) -91.7500 -8.6407 -9.0249
edge_cod 0.630 60.00 ( 1.36) 112.75 ( 2.56) -52.7500 -4.9678 -5.1887
edge_cod 0.315 136.00 ( 3.08) 112.75 ( 2.56) 23.2500 2.1896 2.2870
edge_cod 0.105 194.00 ( 4.40) 112.75 ( 2.56) 81.2500 7.6518 7.9921
res_code 70
edge_cod 2.520 70.00 ( 1.59) 112.75 ( 2.56) -42.7500 -4.0260 -4.2051
edge_cod 0.630 117.00 ( 2.65) 112.75 ( 2.56) 4.2500 .4002 .4180
edge_cod 0.315 164.00 ( 3.72) 112.75 ( 2.56) 51.2500 4.8265 5.0412
edge_cod 0.105 214.00 ( 4.85) 112.75 ( 2.56) 101.2500 9.5354 9.9594
deg_code 8
res_code 100
edge_cod 2.520 17.00 ( .39) 120.08 ( 2.72) -103.0833 -9.4069 -9.8252
edge_cod 0.630 56.00 ( 1.27) 120.08 ( 2.72) -64.0833 -5.8480 -6.1080
edge_cod 0.315 138.00 ( 3.13) 120.08 ( 2.72) 17.9167 1.6350 1.7077
edge_cod 0.105 195.00 ( 4.42) 120.08 ( 2.72) 74.9167 6.8366 7.1405
res_code 85
edge_cod 2.520 27.00 ( .61) 120.08 ( 2.72) -93.0833 -8.4944 -8.8721
edge_cod 0.630 72.00 ( 1.63) 120.08 ( 2.72) -48.0833 -4.3879 -4.5830
edge_cod 0.315 146.00 ( 3.31) 120.08 ( 2.72) 25.9167 2.3650 2.4702
edge_cod 0.105 200.00 ( 4.54) 120.08 ( 2.72) 79.9167 7.2928 7.6171
res_code 70
edge_cod 2.520 82.00 ( 1.86) 120.08 ( 2.72) -38.0833 -3.4753 -3.6298
edge_cod 0.630 119.00 ( 2.70) 120.08 ( 2.72) -1.0833 -.0989 -.1033
edge_cod 0.315 172.00 ( 3.90) 120.08 ( 2.72) 51.9167 4.7377 4.9483
edge_cod 0.105 217.00 ( 4.92) 120.08 ( 2.72) 96.9167 8.8442 9.2374
deg_code 16
res_code 100
edge_cod 2.520 39.00 ( .88) 134.67 ( 3.05) -95.6667 -8.2439 -8.6104
edge_cod 0.630 67.00 ( 1.52) 134.67 ( 3.05) -67.6667 -5.8310 -6.0903
edge_cod 0.315 132.00 ( 2.99) 134.67 ( 3.05) -2.6667 -.2298 -.2400
edge_cod 0.105 211.00 ( 4.78) 134.67 ( 3.05) 76.3333 6.5779 6.8703
res_code 85
edge_cod 2.520 48.00 ( 1.09) 134.67 ( 3.05) -86.6667 -7.4683 -7.8004
edge_cod 0.630 87.00 ( 1.97) 134.67 ( 3.05) -47.6667 -4.1076 -4.2902
edge_cod 0.315 161.00 ( 3.65) 134.67 ( 3.05) 26.3333 2.2692 2.3701
edge_cod 0.105 212.00 ( 4.81) 134.67 ( 3.05) 77.3333 6.6640 6.9603
res_code 70
edge_cod 2.520 92.00 ( 2.09) 134.67 ( 3.05) -42.6667 -3.6767 -3.8402
edge_cod 0.630 151.00 ( 3.42) 134.67 ( 3.05) 16.3333 1.4075 1.4701
edge_cod 0.315 195.00 ( 4.42) 134.67 ( 3.05) 60.3333 5.1991 5.4303
edge_cod 0.105 221.00 ( 5.01) 134.67 ( 3.05) 86.3333 7.4396 7.7704
Once in Excel you can use Text to Columns with space as the delimiter to parse the single cells into multiple cells. Having done so you might choose to save the result as character separated values, should you prefer the .csv format.