How do I read Excel file in Python?

[kath]

How do I read an Excel file in Python?

I have found a package to read excel file, which can be used on any
platform.

http://www.lexicon.net/sjmachin/xlrd.htm

I installed and working on the examples, I found its printing of cell's
contents in a different manner.

>>import xlrd
>>book=xlrd.ope n_workbook("Cal culation_file.x ls")
>>book=xlrd.ope n_workbook("tes tbook.xls")
>>sh=book.sheet _by_index(0)
>>for row in range(sh.nrows) :

print sh.row(rx)
[text:u'name', text:u'address' , text:u'ph']
[text:u'sudhir', text:u'bangalor e', number:1234.0]
[text:u'vinay', text:u'bangalor e', number:3264.0]

I am bit confused with slicing. help me....


Thank you,
Regards,
Sudhir.

[John Machin]

Re: How do I read Excel file in Python?


kath wrote:

How do I read an Excel file in Python?
>
I have found a package to read excel file, which can be used on any
platform.

Hi Sudhir,
So far, so good :-)

>

http://www.lexicon.net/sjmachin/xlrd.htm

I installed and working on the examples, I found its printing of cell's
contents in a different manner.
>

>import xlrd
>book=xlrd.open _workbook("Calc ulation_file.xl s")
>book=xlrd.open _workbook("test book.xls")
>sh=book.sheet_ by_index(0)
>for row in range(sh.nrows) :

print sh.row(rx)
[text:u'name', text:u'address' , text:u'ph']
[text:u'sudhir', text:u'bangalor e', number:1234.0]
[text:u'vinay', text:u'bangalor e', number:3264.0]

It helps when asking questions if you copy/paste exactly what is on
your screen;
in this case
print sh.row(rx)
would have given an error; you must have typed
for rx in range.....

A row is returned as a sequence of Cell objects. What you are seeing is
Python automatically doing repr(cell) on each cell in the row. The
Cell.__repr__ method formats it that way for debugging. Here are some
examples from a little test file of mine:

>>import xlrd
>>bk = xlrd.open_workb ook('sjm1.xls')
>>sh = bk.sheet_by_ind ex(0)
>>row0 = sh.row(0)
>>row0

[text:u'fubar', number:1.0, number:2.0]

>>firstcell = row0[0]
>>type(firstcel l)

<class 'xlrd.sheet.Cel l'>

>>firstcell.cty pe

1

>># cell type 1 is text
>>firstcell.val ue

u'fubar'

>>repr(firstcel l)

"text:u'fub ar'"

>
I am bit confused with slicing. help me....
>

None of the above is anything to do with slicing; is this a 2nd
problem?

Perhaps you are having trouble with this:

>>help(sh.row_s lice)

Help on method row_slice in module xlrd.sheet:

row_slice(self, rowx, start_colx=0, end_colx=None) method of
xlrd.sheet.Shee t instance
##
# Returns a slice of the Cell objects in the given row.

>>>

sh.row_slice(ro wx, lo, hi) gives the same result as sh.row(rowx)[lo:hi]
-- it is provided because the latter would be inefficient for getting a
small slice from a long row.

If you are having trouble with the general concept of slicing, perhaps
you might like to try the Python tutorial. Otherwise, please try to be
a bit more specific about what the confusion is.

HTH, and e-mail me if you prefer ...

Cheers,
John

[kath]

Re: How do I read Excel file in Python?


John Machin wrote:

kath wrote:

How do I read an Excel file in Python?

I have found a package to read excel file, which can be used on any
platform.

>
Hi Sudhir,
So far, so good :-)
>

http://www.lexicon.net/sjmachin/xlrd.htm

I installed and working on the examples, I found its printing of cell's
contents in a different manner.

>>import xlrd
>>book=xlrd.ope n_workbook("Cal culation_file.x ls")
>>book=xlrd.ope n_workbook("tes tbook.xls")
>>sh=book.sheet _by_index(0)
>>for row in range(sh.nrows) :

print sh.row(rx)
[text:u'name', text:u'address' , text:u'ph']
[text:u'sudhir', text:u'bangalor e', number:1234.0]
[text:u'vinay', text:u'bangalor e', number:3264.0]

>
It helps when asking questions if you copy/paste exactly what is on
your screen;
in this case
print sh.row(rx)
would have given an error; you must have typed
for rx in range.....
>
A row is returned as a sequence of Cell objects. What you are seeing is
Python automatically doing repr(cell) on each cell in the row. The
Cell.__repr__ method formats it that way for debugging. Here are some
examples from a little test file of mine:
>

>import xlrd
>bk = xlrd.open_workb ook('sjm1.xls')
>sh = bk.sheet_by_ind ex(0)
>row0 = sh.row(0)
>row0

[text:u'fubar', number:1.0, number:2.0]

>firstcell = row0[0]
>type(firstcell )

<class 'xlrd.sheet.Cel l'>

>firstcell.ctyp e

1

># cell type 1 is text
>firstcell.valu e

u'fubar'

>repr(firstcell )

"text:u'fub ar'"
>

I am bit confused with slicing. help me....

None of the above is anything to do with slicing; is this a 2nd
problem?
>
Perhaps you are having trouble with this:

>help(sh.row_sl ice)

Help on method row_slice in module xlrd.sheet:
>
row_slice(self, rowx, start_colx=0, end_colx=None) method of
xlrd.sheet.Shee t instance
##
# Returns a slice of the Cell objects in the given row.

>>

>
sh.row_slice(ro wx, lo, hi) gives the same result as sh.row(rowx)[lo:hi]
-- it is provided because the latter would be inefficient for getting a
small slice from a long row.
>
If you are having trouble with the general concept of slicing, perhaps
you might like to try the Python tutorial. Otherwise, please try to be
a bit more specific about what the confusion is.
>
HTH, and e-mail me if you prefer ...
>
Cheers,
John

Hi, thanks for the reply. I just took some time reading help file and
came to know to there is nothing do with slicing. But I do have a
problem with date field in the excel file.

the date( 8/9/2006 ) in Excel file, i am getting the value as 38938.0,
which I get when I convert date values to general format in Excel. I
want the actual date value. How do get that?


Thank you.
regards,
Sudhir.

[Matimus]

Re: How do I read Excel file in Python?

the date( 8/9/2006 ) in Excel file, i am getting the value as 38938.0,

which I get when I convert date values to general format in Excel. I
want the actual date value. How do get that?

38938 appears to be the date in days since 1/1/1900. I'm sure someone
can help you figure out how to convert that to a more useful value.

-Matt

[Steve Holden]

Re: How do I read Excel file in Python?

Matimus wrote:

>>the date( 8/9/2006 ) in Excel file, i am getting the value as 38938.0,
>>which I get when I convert date values to general format in Excel. I
>>want the actual date value. How do get that?

>
>
38938 appears to be the date in days since 1/1/1900. I'm sure someone
can help you figure out how to convert that to a more useful value.
>

I believe the win32all extension contains functionality to help with
that, thought it's a long time since I even ran PythonWin (sorry, Mark).

regards
Steve
--
Steve Holden +44 150 684 7255 +1 800 494 3119
Holden Web LLC/Ltd http://www.holdenweb.com
Skype: holdenweb http://holdenweb.blogspot.com
Recent Ramblings http://del.icio.us/steve.holden

[Simon Brunning]

Re: How do I read Excel file in Python?

On 5 Oct 2006 10:25:37 -0700, Matimus <mccredie@gmail .comwrote:

the date( 8/9/2006 ) in Excel file, i am getting the value as 38938.0,
which I get when I convert date values to general format in Excel. I
want the actual date value. How do get that?

>
38938 appears to be the date in days since 1/1/1900. I'm sure someone
can help you figure out how to convert that to a more useful value.

>>excel_date = 38938.0
>>python_date = datetime.date(1 900, 1, 1) +

datetime.timede lta(days=excel_ date)

>>python_date

datetime.date(2 006, 8, 11)

--
Cheers,
Simon B
simon@brunningo nline.net

http://www.brunningonline.net/simon/blog/

[Simon Brunning]

Re: How do I read Excel file in Python?

On 10/5/06, Simon Brunning <simon@brunning online.netwrote :

On 5 Oct 2006 10:25:37 -0700, Matimus <mccredie@gmail .comwrote:

the date( 8/9/2006 ) in Excel file, i am getting the value as 38938.0,
which I get when I convert date values to general format in Excel. I
want the actual date value. How do get that?

38938 appears to be the date in days since 1/1/1900. I'm sure someone
can help you figure out how to convert that to a more useful value.

>

>excel_date = 38938.0
>python_date = datetime.date(1 900, 1, 1) + datetime.timede lta(days=excel_ date)
>python_date

datetime.date(2 006, 8, 11)

Err, that's the wrong answer, isn't it? Perhaps it shoud be
datetime.date(1 900, 1, 29)?

--
Cheers,
Simon B
simon@brunningo nline.net

http://www.brunningonline.net/simon/blog/

[houdinihound@yahoo.com]

Re: How do I read Excel file in Python?

>>excel_date = 38938.0

>>python_date = datetime.date(1 900, 1, 1) + datetime.timede lta(days=excel_ date)
>>python_date

datetime.date(2 006, 8, 11)

>
Err, that's the wrong answer, isn't it? Perhaps it shoud be
datetime.date(1 900, 1, 29)?

Actually was about to post same solution and got same results. (BTW
Simon, the OP date is Aug 9th, 2006). Scratched head and googled for
excel date calculations... found this bug where it treats 1900 as leap
year incorrectly:

http://www.ozgrid.com/Excel/ExcelDateandTimes.htm

Plus it treats 1 jan 1900 as day 1, not 0 so just subtract 2 in the
calc:

>>>python_dat e = datetime.date(1 900, 1, 1) + datetime.timede lta(days=excel_ date - 2)
>>python_date

datetime.date(2 006, 8, 9)

HTH.

[John Machin]

Re: How do I read Excel file in Python?

houdinihound@ya hoo.com wrote:

>excel_date = 38938.0
>python_date = datetime.date(1 900, 1, 1) + datetime.timede lta(days=excel_ date)
>python_date
datetime.date(2 006, 8, 11)

Err, that's the wrong answer, isn't it? Perhaps it shoud be
datetime.date(1 900, 1, 29)?

>
Actually was about to post same solution and got same results. (BTW
Simon, the OP date is Aug 9th, 2006). Scratched head and googled for
excel date calculations... found this bug where it treats 1900 as leap
year incorrectly:

http://www.ozgrid.com/Excel/ExcelDateandTimes.htm

>
Plus it treats 1 jan 1900 as day 1, not 0 so just subtract 2 in the
calc:

>>python_date = datetime.date(1 900, 1, 1) + datetime.timede lta(days=excel_ date - 2)
>python_date

datetime.date(2 006, 8, 9)
>

.... and 2006-08-09 is only the correct answer if the spreadsheet was,
when last saved, using the 1900 ("Windows") date system, not the 1904
("Macintosh" ) date system.

All the OP needs to do is to read the documentation that comes with the
xlrd package. It describes the problems with Excel dates, and offers
functions for conversion between the Excel date numbers and (year,
month, day, hour, minute, second) tuples which of course are
interoperable with Python's datetime module and with mx.DateTime.

| >>import xlrd
| >>xlrd.xldate_a s_tuple(38938.0 , 0)
| (2006, 8, 9, 0, 0, 0)
| >>xlrd.xldate_a s_tuple(38938.0 , 1)
| (2010, 8, 10, 0, 0, 0)
| >>>

Cheers,
John

[Simon Brunning]

Re: How do I read Excel file in Python?

On 5 Oct 2006 12:49:53 -0700, houdinihound@ya hoo.com
<houdinihound@y ahoo.comwrote:

Actually was about to post same solution and got same results. (BTW
Simon, the OP date is Aug 9th, 2006). Scratched head and googled for
excel date calculations... found this bug where it treats 1900 as leap
year incorrectly:
http://www.ozgrid.com/Excel/ExcelDateandTimes.htm

Ah - I was reading the OP's 8/9/2006 in the European way - DD/MM/YYYY.

One might argue over whether DD/MM/YYYY or MM/DD/YYYY are more
rational, but I find it best to avoid *both* those formats, 'cos they
are so easily confused.

--
Cheers,
Simon B
simon@brunningo nline.net

http://www.brunningonline.net/simon/blog/

[Giles Brown]

Re: How do I read Excel file in Python?

John Machin wrote:

houdinihound@ya hoo.com wrote:

>>excel_date = 38938.0
>>python_date = datetime.date(1 900, 1, 1) + datetime.timede lta(days=excel_ date)
>>python_date
datetime.date(2 006, 8, 11)
>
Err, that's the wrong answer, isn't it? Perhaps it shoud be
datetime.date(1 900, 1, 29)?

Actually was about to post same solution and got same results. (BTW
Simon, the OP date is Aug 9th, 2006). Scratched head and googled for
excel date calculations... found this bug where it treats 1900 as leap
year incorrectly:

http://www.ozgrid.com/Excel/ExcelDateandTimes.htm

Plus it treats 1 jan 1900 as day 1, not 0 so just subtract 2 in the
calc:

>>>python_dat e = datetime.date(1 900, 1, 1) + datetime.timede lta(days=excel_ date - 2)
>>python_date

datetime.date(2 006, 8, 9)

>
... and 2006-08-09 is only the correct answer if the spreadsheet was,
when last saved, using the 1900 ("Windows") date system, not the 1904
("Macintosh" ) date system.

John,
Just for me own curiosity, is this Excel concept of date numbers same
as the OLE
concept (see http://msdn2.microsoft.com/en-us/library/82ab7w69.aspx or
search "MFC DATE" on MSDN).

I put in some test cases for conversion code here:

http://aspn.activestate.com/ASPN/Cookbook/Python/Recipe/496683

But would be interested to add any additional info on variations on
this theme.

Cheers,
Giles

[kath]

Re: How do I read Excel file in Python?

John Machin wrote:

houdinihound@ya hoo.com wrote:

>>excel_date = 38938.0
>>python_date = datetime.date(1 900, 1, 1) + datetime.timede lta(days=excel_ date)
>>python_date
datetime.date(2 006, 8, 11)
>
Err, that's the wrong answer, isn't it? Perhaps it shoud be
datetime.date(1 900, 1, 29)?

Actually was about to post same solution and got same results. (BTW
Simon, the OP date is Aug 9th, 2006). Scratched head and googled for
excel date calculations... found this bug where it treats 1900 as leap
year incorrectly:

http://www.ozgrid.com/Excel/ExcelDateandTimes.htm

Plus it treats 1 jan 1900 as day 1, not 0 so just subtract 2 in the
calc:

>>>python_dat e = datetime.date(1 900, 1, 1) + datetime.timede lta(days=excel_ date - 2)
>>python_date

datetime.date(2 006, 8, 9)

>
... and 2006-08-09 is only the correct answer if the spreadsheet was,
when last saved, using the 1900 ("Windows") date system, not the 1904
("Macintosh" ) date system.
>
All the OP needs to do is to read the documentation that comes with the
xlrd package. It describes the problems with Excel dates, and offers
functions for conversion between the Excel date numbers and (year,
month, day, hour, minute, second) tuples which of course are
interoperable with Python's datetime module and with mx.DateTime.
>
| >>import xlrd
| >>xlrd.xldate_a s_tuple(38938.0 , 0)
| (2006, 8, 9, 0, 0, 0)
| >>xlrd.xldate_a s_tuple(38938.0 , 1)
| (2010, 8, 10, 0, 0, 0)
| >>>
>
Cheers,
John

Hi,

>>import xlrd
>>book = xlrd.open_workb ook("testbook1. xls")
>>sh = book.sheet_by_i ndex(0)
>>sh.cell_value (rowx=1,colx=0)

38938.0

>>type(sh.cell_ value(rowx=1,co lx=0))

<type 'unicode'>

>>xlrd.xldate_a s_tuple( sh.cell_value( rowx = 1,colx= 0 ), 0 )

Traceback (most recent call last):
File "D:\Python23\Te sting area\Python and Excel\xlrdRead. py", line
30, in ?
temp=xlrd.xldat e_as_tuple(sh.c ell_value(rowx= r,colx=c),0)
File "D:\PYTHON23\Li b\site-packages\xlrd\x ldate.py", line 61, in
xldate_as_tuple
xldays = int(xldate)
ValueError: invalid literal for int(): Date

because xlrd.xldate_as_ tuple() function expects first argument to be an
integer. How do I convert an unicode character to integer, so that I
could get the date using xlrd.xldate_as_ tuple() function.

Thank you,
sudhir.

[John Machin]

Re: How do I read Excel file in Python?


Giles Brown wrote:

John Machin wrote:

houdinihound@ya hoo.com wrote:

>excel_date = 38938.0
>python_date = datetime.date(1 900, 1, 1) + datetime.timede lta(days=excel_ date)
>python_date
datetime.date(2 006, 8, 11)

Err, that's the wrong answer, isn't it? Perhaps it shoud be
datetime.date(1 900, 1, 29)?
>
Actually was about to post same solution and got same results. (BTW
Simon, the OP date is Aug 9th, 2006). Scratched head and googled for
excel date calculations... found this bug where it treats 1900 as leap
year incorrectly:

http://www.ozgrid.com/Excel/ExcelDateandTimes.htm

>
Plus it treats 1 jan 1900 as day 1, not 0 so just subtract 2 in the
calc:
>>python_date = datetime.date(1 900, 1, 1) + datetime.timede lta(days=excel_ date - 2)
>python_date
datetime.date(2 006, 8, 9)
>

... and 2006-08-09 is only the correct answer if the spreadsheet was,
when last saved, using the 1900 ("Windows") date system, not the 1904
("Macintosh" ) date system.

>
John,
Just for me own curiosity, is this Excel concept of date numbers same
as the OLE
concept (see http://msdn2.microsoft.com/en-us/library/82ab7w69.aspx or
search "MFC DATE" on MSDN).
>
I put in some test cases for conversion code here:

http://aspn.activestate.com/ASPN/Cookbook/Python/Recipe/496683

>
But would be interested to add any additional info on variations on
this theme.
>

Hi Giles,

Those OLE date numbers coincide with Excel 1900-system date numbers
from 1900-03-01 onwards. Excel treats day 60 as the non-existent
1900-02-29.

Cheers,
John