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>You could experiment with using the <code>win32com</code> approach. This is normally quite slow, but it does have the advantage of being able to do most of the data transfer in a single call. You would just need to prepare your data into a suitably sized list:</p>

<pre><code>import win32com.client as win32

data = [["A", "B"] for _ in range(10000)]

excel = win32.gencache.EnsureDispatch('Excel.Application')
excel.DisplayAlerts = False
wb = excel.Workbooks.Add()
ws = wb.Worksheets.Add()
ws.Range(ws.Cells(1, 1), ws.Cells(len(data), 2)).Value = data
wb.SaveAs(r'c:\python\test.xlsx')
excel.Application.Quit()
</code></pre>

<p>Timing this with <code>range(1000000)</code> took about 7.5 seconds.</p>

<p>AFAIK there is no way in <code>openpyxl</code> to write more than one cell at a time.</p>

<hr>

<p>Based on your existing code, I would suggest something along the lines of:</p>

<pre><code>import win32com.client as win32
import time
import spss,spssaux, sys

#initialise timer
time_start = time.time()

spss.StartDataStep()
MyFile = spss.Dataset()
MyCases = MyFile.cases
spss.EndDataStep()

excel = win32.gencache.EnsureDispatch('Excel.Application')
excel.DisplayAlerts = False

wb = excel.Workbooks.Add()
ws1 = wb.Worksheets("Sheet1")
ws2 = wb.Worksheets("Sheet2")

# Add header to both sheets
ws1.Range(ws1.Cells(1, 1), ws1.Cells(1, len(MyFile.varlist))).Value = MyFile.varlist
ws2.Range(ws2.Cells(1, 1), ws2.Cells(1, len(MyFile.varlist))).Value = MyFile.varlist

# Copy data
ws1.Range(ws1.Cells(2, 1), ws1.Cells(1 + len(MyCases), len(MyCases[0]))).Value = MyCases

wb.SaveAs(r'e:\python temp\test.xlsx')
excel.Application.Quit()  

print("Saving took {:.1f} seconds.".format(time.time() - time_start))
</code></pre>

python excel spss openpyxl