RE: Pywin32: How to import data into Excel?
Tim Golden
> I need to import some big data into Excel from my
> Python script. I have TXT file (~7,5 Mb). I'm using
> Pywin32 library for that, but if I first try to read
> the TXT file and then save the values one by one like
> xlBook.Sheets(sheet_name).Cells(i,j).Value = value_from_TXT_file
> it takes about 10 hours to process whole data
A few suggestions:
+ When trying to automate anything in Excel, it's
usually illuminating to record a macro which does
what you want, and then to translate that VBA code
into Python.
+ To find documentation on the Microsoft object models,
MSDN is usually a good bet. Googling for:
site:msdn.microsoft.com excel object model
gives some hopeful-looking hits
+ I fiddled around for a few moments, and found that
the following code at least worked:
<code>
import win32com.client
xl = win32com.client.gencache.EnsureDispatch ("Excel.Application")
xl.Visible = 1
xl.Workbooks.OpenText ("c:/temp/temp.txt")
</code>
+ If you needed more control, bear in mind that you
can put lists and lists of lists directly into an
Excel range. So, something like this:
<code>
import win32com.client
a = [1, 2, 3]
b = [['a', 'b', 'c'], ['d', 'e', 'f'], ['g', 'h', 'i']]
xl = win32com.client.gencache.EnsureDispatch ("Excel.Application")
xl.Visible = 1
wb = xl.Workbooks.Add ()
ws = wb.ActiveSheet
ws.Range (ws.Cells (1, 1), ws.Cells (1, 3)).Value = a
ws.Range (ws.Cells (2, 1), ws.Cells (4, 3)).Value = b
</code>
+ Finally, you might look at PyExcelerator. I've toyed with
it only a little, but it seems to do the business, and
doesn't need Excel installed (so you can even run it on
Linux, if that takes your fancy). It's had a very recent
release, so it's definitely being maintained:
http://sourceforge.net/projects/pyexcelerator/
TJG
________________________________________________________________________
This e-mail has been scanned for all viruses by Star. The
service is powered by MessageLabs. For more information on a proactive
anti-virus service working around the clock, around the globe, visit:
http://www.star.net.uk
________________________________________________________________________
Simon Brunning
> I need to import some big data into Excel from my Python script. I have TXT
> file (~7,5 Mb).
Have you considered converting your text data to CSV format? Excel
opens CSV files happily enough, and you could always automate
save-as-workbook and any formatting you need afterwards.
--
Cheers,
Simon B,
si...@brunningonline.net,
http://www.brunningonline.net/simon/blog/
Dmytro Lesnyak
Now, I can solve my problem and make my script faster!
> A few suggestions:
>
> + When trying to automate anything in Excel, it's
> usually illuminating to record a macro which does
> what you want, and then to translate that VBA code
> into Python.
Yes, I also doing VBA macros first, but sometimes it is difficult for me
to translate it into Python.
> + To find documentation on the Microsoft object models,
> MSDN is usually a good bet. Googling for:
>
> site:msdn.microsoft.com excel object model
Thanks. I got very good links
> I fiddled around for a few moments, and found that
> the following code at least worked:
>
> <code>
> import win32com.client
> xl = win32com.client.gencache.EnsureDispatch ("Excel.Application")
xl.Visible = 1 xl.Workbooks.OpenText
> ("c:/temp/temp.txt") </code>
Yes. It works. I wonder why I did not in my case.
It's strange, but if you put
xl.Visible = 1
after xl.Workbooks.OpenText("c:/temp/temp.txt")
It doesn't display the result. Probably, it is some Excel's feature :)
>+ If you needed more control, bear in mind that you
> can put lists and lists of lists directly into an
> Excel range. So, something like this:
>
> <code>
> import win32com.client
>
> a = [1, 2, 3]
> b = [['a', 'b', 'c'], ['d', 'e', 'f'], ['g', 'h', 'i']]
>
> xl = win32com.client.gencache.EnsureDispatch ("Excel.Application")
xl.Visible = 1 wb = xl.Workbooks.Add () ws = wb.ActiveSheet ws.Range
(ws.Cells (1, 1), ws.Cells (1, 3)).Value = a ws.Range (ws.Cells (2, 1),
ws.Cells (4, 3)).Value = b
>
> </code>
I didn't know that. Probably, It would be faster.
> + Finally, you might look at PyExcelerator. I've toyed with
> it only a little, but it seems to do the business, and
> doesn't need Excel installed (so you can even run it on
> Linux, if that takes your fancy). It's had a very recent
> release, so it's definitely being maintained:
>
> http://sourceforge.net/projects/pyexcelerator/
Good option. Thanks :)
-Dima
Duncan Booth
> [Dmytro Lesnyak]
>> I need to import some big data into Excel from my
>> Python script. I have TXT file (~7,5 Mb). I'm using
>> Pywin32 library for that, but if I first try to read
>> the TXT file and then save the values one by one like
>> xlBook.Sheets(sheet_name).Cells(i,j).Value = value_from_TXT_file
>> it takes about 10 hours to process whole data
>
> A few suggestions:
>
> + When trying to automate anything in Excel, it's
> usually illuminating to record a macro which does
> what you want, and then to translate that VBA code
> into Python.
>
Another suggestion: when automating Excel, turn off the automatic
recalculation (set Application.Calculation=xlManual) and then turn it back
on again when you have finished.
If you don't do this, Excel will attempt to recalculate the sheet after
every update to a cell. Even if you don't have any formulae referencing the
cells you change it still makes a big difference.
Tim Golden
> + When trying to automate anything in Excel, it's
> usually illuminating to record a macro which does
> what you want, and then to translate that VBA code
> into Python.
>
[Duncan Booth]
> Another suggestion: when automating Excel, turn off the automatic
> recalculation (set Application.Calculation=xlManual) and then turn it
back
> on again when you have finished.
> If you don't do this, Excel will attempt to recalculate the sheet
after
> every update to a cell. Even if you don't have any formulae
referencing the
> cells you change it still makes a big difference.
Thanks. Didn't know that.
Marco Aschwanden
Norbert
> On 08/11/05, Dmytro Lesnyak <Dmytro....@xtract.fi> wrote:
> > I need to import some big data into Excel from my Python script. I have TXT
> > file (~7,5 Mb).
>
> Have you considered converting your text data to CSV format? Excel
> opens CSV files happily enough, and you could always automate
> save-as-workbook and any formatting you need afterwards.
But there are thorny issues with different locales and number formats.
Excel is also just too clever in recognising dates
All the best
Norbert