noobie - finding first available row

[jer99]

 I'm new to python. I've dealt with VBA in Access, programed in PL/SQL (Oracle) and a few others.
I have an Excel 2010  workbook with one worksheet.
There are 7 columns with the data starting at row 6.
The last column of data is Row 12.

I would like to automate a process where Python would
1) Look for the first empty row (in this case it would return 13)
2) Add the date/time to column 1
3) add a value to column 2
4) add a formula to column 3
5)continue to add values, formulas in the row


That said, I have two questions -
Which library is the best for this type of work?
How do I find the next available row?

Thanks for the help.
Jerry

[Charlie Clark]

Hiya Jerry,

Am .06.2015, 17:30 Uhr, schrieb jer99 <jerro...@gmail.com>:

> I'm new to python. I've dealt with VBA in Access, programed in PL/SQL
> (Oracle) and a few others.
> I have an Excel 2010 workbook with one worksheet.
> There are 7 columns with the data starting at row 6.
> The last column of data is Row 12.
>
> I would like to automate a process where Python would
> 1) Look for the first empty row (in this case it would return 13)
> 2) Add the date/time to column 1
> 3) add a value to column 2
> 4) add a formula to column 3
> 5)continue to add values, formulas in the row

Can we assume you intend to save the file after making the changes? If
this is the case then openpyxl is probably the best library. You can
otherwise use a combination of xlrd and xlsxwriter.

> That said, I have two questions -
> Which library is the best for this type of work?
> How do I find the next available row?

In openpyxl ws.max_row should give you the highest non-empty row. However,
if you only want to know in order to add data then just .append()

import datetime
import openpyxl
wb = load_workbook("sample.xlsx")
ws = wb.active
ws.append([datetime.datetime.now(), "a value", "=some formula()"])

Charlie
--
Charlie Clark
Managing Director
Clark Consulting & Research
German Office
Kronenstr. 27a
Düsseldorf
D- 40217
Tel: +49-211-600-3657
Mobile: +49-178-782-6226

[jer99]

Thanks Charlie - Just what I was looking for!

Jerry

[jer99]

Hi again Charlie,
I'm stumped. 
I tried the below with multiple items
ws.append (datetime.datetime.now(), int(Fdata['items_down'].replace(',','')), down_perc, int(Fdata['items_up'].replace(',','')), up_perc, int(Fdata['items'].replace(',','')))

I thought that would just append a row with the values given to populate the  columns in the row, however I received a python error:
TypeError: append() takes 2 positional arguments but 7 were given

I know the syntax of the values is correct and valid as I did a print() and it worked.
Do I need to loop ?


On Tuesday, June 23, 2015 at 11:56:01 AM UTC-5, Charlie Clark wrote:

Hiya Jerry,

[Adrian Klaver]

On 06/24/2015 11:14 AM, jer99 wrote:
> Hi again Charlie,
> I'm stumped.

Docs:

https://openpyxl.readthedocs.org/en/latest/api/openpyxl.worksheet.html?highlight=append#openpyxl.worksheet.worksheet.Worksheet.append

> I tried the below with multiple items
> ws.append (datetime.datetime.now(),
> int(Fdata['items_down'].replace(',','')), down_perc,
> int(Fdata['items_up'].replace(',','')), up_perc,
> int(Fdata['items'].replace(',','')))
>
> I thought that would just append a row with the values given to populate
> the columns in the row, however I received a python error:
> TypeError: append() takes 2 positional arguments but 7 were given
>
> I know the syntax of the values is correct and valid as I did a print()
> and it worked.

You need to put the values in a list or dict. See the docs above.

> Do I need to loop ?
>
>
> On Tuesday, June 23, 2015 at 11:56:01 AM UTC-5, Charlie Clark wrote:
>
> Hiya Jerry,
>
>
>
> In openpyxl ws.max_row should give you the highest non-empty row.
> However,
> if you only want to know in order to add data then just .append()
>
> import datetime
> import openpyxl
> wb = load_workbook("sample.xlsx")
> ws = wb.active
> ws.append([datetime.datetime.now(), "a value", "=some formula()"])
>
> Charlie
>

> --
> You received this message because you are subscribed to the Google
> Groups "python-excel" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to python-excel...@googlegroups.com
> <mailto:python-excel...@googlegroups.com>.
> To post to this group, send email to python...@googlegroups.com
> <mailto:python...@googlegroups.com>.
> Visit this group at http://groups.google.com/group/python-excel.
> For more options, visit https://groups.google.com/d/optout.


--
Adrian Klaver
adrian...@aklaver.com

[Charlie Clark]

Am .06.2015, 20:14 Uhr, schrieb jer99 <jerro...@gmail.com>:

> I know the syntax of the values is correct and valid as I did a print()
> and
> it worked.
> Do I need to loop ?

You should have got an exception telling you that you need to pass in a
sequence. Alternatively, you can always use ws.cell(row=…, column=…)

[jer99]

Thanks...
I didn't see where to find the complete docs, so that helped in many ways.
I also saw my mistake in not using a list..
Fixed and it worked!
Thanks again...

Jerry

[Adrian Klaver]

On 06/24/2015 01:23 PM, jer99 wrote:
>
> Thanks...
> I didn't see where to find the complete docs, so that helped in many ways.

http://www.python-excel.org/

The python-excel community has started on pulling all the Excel via
Python software into the above page. It is very useful and much thanks
goes out the folks that are doing this.

> I also saw my mistake in not using a list..
> Fixed and it worked!
> Thanks again...
>
> Jerry
>