Path: csiph.com!news.swapon.de!newsfeed.fsmpi.rwth-aachen.de!newsfeed.straub-nv.de!feeds.phibee-telecom.net!newsfeed.xs4all.nl!newsfeed8.news.xs4all.nl!nzpost1.xs4all.net!not-for-mail
To: gall.pavgal.gall@gmail.com
cc: python-list@python.org, lac@openend.se
From: Laura Creighton <lac@openend.se>
Subject: Re: change data in large excel file(more than 240 000 rows on sheet)
In-reply-to: <3d355596-b1cb-4a1e-b234-21bf1e637f63@googlegroups.com>
References: <9c23c9d1-4257-475b-a7f6-811e85fe83f3@googlegroups.com> <caed8133-f4cb-420b-b9fd-4512fbe11400@googlegroups.com> <mailman.468.1444300069.28679.python-list@python.org> <3d355596-b1cb-4a1e-b234-21bf1e637f63@googlegroups.com>
Comments: In-reply-to gall.pavgal.gall@gmail.com message dated "Fri, 09 Oct 2015 01:21:37 -0700."
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <28471.1444388864.1@fido>
Content-Transfer-Encoding: quoted-printable
Date: Fri, 09 Oct 2015 13:07:44 +0200
Precedence: list
Newsgroups: comp.lang.python
Message-ID: <mailman.506.1444388875.28679.python-list@python.org>
Lines: 57
NNTP-Posting-Host: 2001:888:2000:d::a6
Xref: csiph.com comp.lang.python:97533

In a message of Fri, 09 Oct 2015 01:21:37 -0700, gall.pavgal.gall@gmail.co=
m wri
tes:
>Thanks Laura!
>But i need to change existing excel file and if i use the optimised reade=
r, i can read data only, but i can't change data.  =


Hmmm.  When I have this problem I have other problems as well,
and they can pretty well all be sumarised as 'I never wanted =

an excel file anyway'. =


So first I convert the xlsx to a CVS.

import openpyxl
import csv

workbook =3D openpyxl.load_workbook('my_big_file.xlsx', read_only=3DTrue)
sheet =3D workbook['big_data']

with open('my_big_file.csv', 'wb') as f:
    c =3D csv.writer(f)
    for r in sh.rows:
        c.writerow([cell.value for cell in r])

This may be enough for you, and you may be able to use the Python
csv module to make the changes you want.
https://docs.python.org/3.5/library/csv.html
This may be all you need, if you can process your problem one line
at a time.  Reading it all into memory will give you the same problem.

But I have never actually done this.  Instead I convert the cvs to a =

postgresql database, and then make my changes there, and then
export it again as a csv, and then reverse the process to get an
xlsx again (if I need one).  I don't know any way to get postgresql
to take the xlsx directly, which is why I have the two step process
with the csv in the middle.

If you are more comfortable with MySQL or SQLite or some other database,
by all means use that.  They might even have a way to import xlsx directly=
.
I only do this because it is what I am used to.

The other thing to consider is pandas.
https://pypi.python.org/pypi/pandas

It uses openpyxl under the hood, but is set up for big data.  It accepts
xlxs files and also connects to databases.  It may be exactly what
you are looking for -- but I haven't tried this myself for such large
datasets, so  I don't know for sure.

Laura