RE: Pywin32: How to import data into Excel?
[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.
+ 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:
import win32com.client
xl = win32com.client.gencache.EnsureDispatch ("Excel.Application")
xl.Visible = 1
xl.Workbooks.OpenText ("c:/temp/temp.txt")
+ 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:
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
+ 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
--
http://mail.python.org/mailman/listinfo/python-list
Re: Pywin32: How to import data into Excel?
On 08/11/05, Dmytro Lesnyak <[EMAIL PROTECTED]> 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. -- Cheers, Simon B, [EMAIL PROTECTED], http://www.brunningonline.net/simon/blog/ -- http://mail.python.org/mailman/listinfo/python-list
RE: Pywin32: How to import data into Excel?
Thanks a lot. It really works!
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:
>
>
> import win32com.client
> xl = win32com.client.gencache.EnsureDispatch ("Excel.Application")
xl.Visible = 1 xl.Workbooks.OpenText
> ("c:/temp/temp.txt")
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:
>
>
> 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
>
>
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
--
http://mail.python.org/mailman/listinfo/python-list
Re: Pywin32: How to import data into Excel?
On 11/8/05, Dmytro Lesnyak <[EMAIL PROTECTED]> wrote: Hello, 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 I have to question the reasoning behind using Excel. That much data seems like it would be troublesome to manage in Excel. How good is Excel at working with that much data? -- http://mail.python.org/mailman/listinfo/python-list
RE: Pywin32: How to import data into Excel?
> I have to question the reasoning behind using Excel. That much data seems like it would be troublesome to manage in Excel. How good is Excel at working with that much data? Well, It's not that big data. As result, It will be 52 X 25000 table and it works fine in Excel (f.e. I need to make several Pivot tables from that data and it is really fast). -- http://mail.python.org/mailman/listinfo/python-list
RE: Pywin32: How to import data into Excel?
Tim Golden wrote: > [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. -- http://mail.python.org/mailman/listinfo/python-list
RE: Pywin32: How to import data into Excel?
[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. 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 -- http://mail.python.org/mailman/listinfo/python-list
Re: Pywin32: How to import data into Excel?
PyXLWriter might be at your service: http://pyxlwriter.sourceforge.net/ Have a nice day, Marco -- http://mail.python.org/mailman/listinfo/python-list
Re: Pywin32: How to import data into Excel?
Simon Brunning wrote: > On 08/11/05, Dmytro Lesnyak <[EMAIL PROTECTED]> 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 -- http://mail.python.org/mailman/listinfo/python-list
