Efficiently write SPSS data into Excel using Python
<p>I am trying to use Python to write data from an open SPSS dataset into an excel file. The below program works fine, but it takes about 35 seconds for a file with 1.4 million data-points (2500 cases, 700 variables). </p>

<p>For now, I am looping through each case (as a tuple), then assigning each element of the tuple into a cell. <code>openpyxl</code> is the Excel module of choice (as I did not use any other in the past).</p>

<p>I am going to use the Python program for much larger data-sets, so I was wondering if there is a more efficient logic of doing this. </p>

<pre><code>BEGIN PROGRAM.

import spssdata
import spss,spssaux, sys
import openpyxl
from openpyxl import Workbook
import gc

#initialise timer
time_start = time.time()

#Create the workbook to save the codebook 
wb=openpyxl.Workbook()
ws1=wb.create_sheet()

spss.StartDataStep()
MyFile = spss.Dataset()
varDict = spssaux.VariableDict()
MyCases=MyFile.cases
MyVars=MyFile.varlist

for varnum, varname in enumerate(MyFile.varlist):
        ws1.cell(row=1,column=varnum+1).value=varname.name
        ws2.cell(row=1,column=varnum+1).value=varname.name

for eachcase in range (len(MyCases)):
    for eachvar in range (len(MyCases[eachcase])):
        ValueToWrite=MyCases[eachcase][eachvar]
        ws1.cell(row=eachcase+2,column=eachvar+1).value=ValueToWrite

spss.EndDataStep()

wb.save("some filename")
del wb
gc.collect()
time_end = time.time()
time_taken = int(time_end-time_start)
print ("Saving took " + str(time_taken) + " seconds.")
END PROGRAM.
</code></pre>

<p>Make sure lxml is installed and use openpyxl's write-only mode, assuming you can work on a row-by-row basis. If this is not directly possible then you'll need some kind of intermediate structure that can give you rows.</p>

python excel spss openpyxl