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]

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]

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]

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]

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 [phone] [phone]
Holden Web LLC/Ltd http://www.holdenweb.com
Skype: holdenweb http://holdenweb.blogspot.com
Recent Ramblings http://del.icio.us/steve.holden

[Simon Brunning]

On 5 Oct 2006 10:25:37 -0700, Matimus <mc******@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
si***@brunningo nline.net
http://www.brunningonline.net/simon/blog/

[Simon Brunning]

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

On 5 Oct 2006 10:25:37 -0700, Matimus <mc******@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
si***@brunningo nline.net
http://www.brunningonline.net/simon/blog/

[houdinihound]

>>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]

ho**********@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]

On 5 Oct 2006 12:49:53 -0700, ho**********@ya hoo.com
<ho**********@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
si***@brunningo nline.net
http://www.brunningonline.net/simon/blog/