getting started with processing data with python

Question

i have an excel spreadsheet of about 3 million cells. i asked the following question and i liked the answer about saving the spreadsheet as CSV and then processing it with python:

http://stackoverflow.com/questions/3322939/solution-to-perform-lots-of-calculations-on-3-million-data-points-and-make-charts/3323168#3323168

1. is there a library that i can use that will read the csv into a matrix or should i write one myself?
2. does python speak with VBA at all?
3. after i am done processing the data, is it simple to put it back in the form of a CSV so that i can open it in excel for viewing?

Answer 1

I like NumPy's loadtxt for this sort of thing. Very configurable for reading CSVs. And savetxt for putting it back after manipulation. Or you could check out the built in csv module if you'd rather not install anything new.

Answer 2

is there a library that i can use that will read the csv into a matrix or should i write one myself?

The csv module handles just about everything you could want.

does python speak with VBA at all?

Iron Python might.

after i am done processing the data, is it simple to put it back in the form of a CSV so that i can open it in excel for viewing?

The csv module handles just about everything you could want.

Suggestion: Read this: http://docs.python.org/library/csv.html

Answer 3

If we speak pythonish, why not to use http://www.python-excel.org/ ?

Example of read file:

import xlrd
rb = xlrd.open_workbook('file.xls',formatting_info=True)
sheet = rb.sheet_by_index(0)
for rownum in range(sheet.nrows):
  row = sheet.row_values(rownum)
  for c_el in row:
    print c_el

Writing the new file:

import xlwt
from datetime import datetime

font0 = xlwt.Font()
font0.name = 'Times New Roman'
font0.colour_index = 2
font0.bold = True

style0 = xlwt.XFStyle()
style0.font = font0

style1 = xlwt.XFStyle()
style1.num_format_str = 'D-MMM-YY'

wb = xlwt.Workbook()
ws = wb.add_sheet('A Test Sheet')

ws.write(0, 0, 'Test', style0)
ws.write(1, 0, datetime.now(), style1)
ws.write(2, 0, 1)
ws.write(2, 1, 1)
ws.write(2, 2, xlwt.Formula("A3+B3"))

wb.save('example.xls')

There are other examples on the page.

Answer 4

If you don't want to deal with changing back and forth from CSV you can use win32com, which can be downloaded here. http://python.net/crew/mhammond/win32/Downloads.html