How to extract dates
Strupp, Karsten (NSN - DE/Berlin)
Well, the only thing I get is floating point numbers. I extract the cell content using str(sh.row_values(r)[col]). sh is the sheet.
I found a function xldate_as_tuple(xldate, datemode) [#] in the docs but python says: NameError: name 'xldate_as_tuple' is not defined. I installed the xlrd-0.6.1.win32.exe.
What's the problem?
Karsten
Wouter van Heyst
Say you have a date value in cell = sh.row_values(r)[col] (by checking
sh.row(r)[col].ctype == xlrd.XL_CELL_DATE for instance), and 'book' is
the workbook you got sh from:
xlrd.xldate_as_tuple(cell, book.datemode)
Did you import xldate_as_tuple from the xlrd package?
Wouter van Heyst
John Machin
Nothing to do with your problem, but you might like to get the (quite
recent) version 0.7.0 from http://pypi.python.org/pypi/xlrd
>
> What's the problem?
Possibly, skipping over these:
http://www.lexicon.net/sjmachin/xlrd.html#xlrd.Sheet.row_types-method
http://www.lexicon.net/sjmachin/xlrd.html#xlrd.Cell-class
Here are some examples of use; the referenced spreadsheet has today's
date in cell A3 of the first sheet.
| >>> import xlrd
| >>> book = xlrd.open_workbook('xlrd_cell_types_demo.xls')
| >>> sh = book.sheet_by_index(0)
| >>> sh.row_values(0)
| [u'Hello', 1234.567, 39896.0, 1, 7, '', 0]
| >>> sh.row_types(0)
| array('B', [1, 2, 3, 4, 5, 0, 4])
| >>> c = sh.cell(0, 2)
| >>> type(c)
| <class 'xlrd.sheet.Cell'>
| >>> c
| xldate:39896.0
| >>> atuple = xlrd.xldate_as_tuple(sh.row_values(0)[2], book.datemode)
| >>> atuple
| (2009, 3, 24, 0, 0, 0)
| >>> import datetime
| >>> datetime.datetime(*atuple)
| datetime.datetime(2009, 3, 24, 0, 0)
| >>>
HTH,
John
John Machin
[snip]
> Here are some examples of use; the referenced spreadsheet has today's
> date in cell A3 of the first sheet.
Would you believe C1?
Cheers,
John
Strupp, Karsten (NSN - DE/Berlin)
|>
|> Wouter van Heyst
sorry for bothering. I'm not very familiar with Python though I wrote
several small programs using Python. I have imported the xlrd package,
and, of course, I have to specify the function name together with
the package name. Again and again I step into this trap.
The complete line of code is:
outputText = cols[col] + "=\"" + str(xlrd.xldate_as_tuple(sh.row_values(r)[col],0))
This produces:
release="(2008, 9, 8, 0, 0, 0)"
Anyway, what I have expected from the cell evaluation done by xlrd is
a string like "8.09.2008". Seems there is handwork necessary.
---
Hello John,
|> Would you believe C1?
Sorry, I don't understand the question. What is "C1"?
Well, this was the version I tried first. Executing the Windows installer
just gave an error message saying the program is not configured correctly.
(WinXP Prof SP2)
Thank you both for your help.
With kind regards / Mit freundlichen Grüßen / Ystävällisin Terveisin,
Karsten Strupp
John Machin
> |> Did you import xldate_as_tuple from the xlrd package?
> |>
> |> Wouter van Heyst
>
> Hello Wouter,
>
> sorry for bothering. I'm not very familiar with Python though I wrote
> several small programs using Python. I have imported the xlrd package,
> and, of course, I have to specify the function name together with
> the package name. Again and again I step into this trap.
> The complete line of code is:
> outputText = cols[col] + "=\"" + str(xlrd.xldate_as_tuple(sh.row_values(r)[col],0))
Please read the documentation, and listen to Wouter: if you get a date
value from a file, *always* pass book.datemode as the second argument of
xl_date_as_tuple.
> This produces:
> release="(2008, 9, 8, 0, 0, 0)"
>
> Anyway, what I have expected from the cell evaluation done by xlrd is
> a string like "8.09.2008".
or 2008-09-08 or 9/8/2008 or ... ??? This would require xlrd to
know/guess your locale and use some locale-specific method to format the
date for you ... and what if the next date value is 35 seconds after the
first; perhaps we should have formatted the first as 2008-09-08T00:00:00?
And all this guessing and converting bolted on top of the basic
extraction would not amuse people whose sole need is to pop the float
out into another xls file without any conversion at all.
> Seems there is handwork necessary.
A minute fraction of the handwork it took to get that float value and
XL_CELL_DATE type to you.
> ---
>
> Hello John,
>
> |> Would you believe C1?
> Sorry, I don't understand the question. What is "C1"?
In relation to the example session in my first reply to you, I
mistakenly said that the date-containing cell was A3, which was
unbelievable. The correct cell is C1 -- the third cell in the first row.
"Would you believe C1?" was a rhetorical question.
>
> |> but you might like to get the (quite
> recent) version 0.7.0 from http://pypi.python.org/pypi/xlrd
> Well, this was the version I tried first. Executing the Windows installer
> just gave an error message saying the program is not configured correctly.
> (WinXP Prof SP2)
Thanks for the problem report. Could you supply the exact text of the
error message that you get? Which version of Python are you using? If I
send you another installer, will you try it out, and perhaps answer
questions on what MSVC-related DLLs you may have installed?
Cheers,
John
Wouter van Heyst
> |> Did you import xldate_as_tuple from the xlrd package?
> |>
> |> Wouter van Heyst
>
> Hello Wouter,
>
> sorry for bothering. I'm not very familiar with Python though I wrote
> several small programs using Python. I have imported the xlrd package,
> and, of course, I have to specify the function name together with
> the package name. Again and again I step into this trap.
> The complete line of code is:
> outputText = cols[col] + "=\"" + str(xlrd.xldate_as_tuple(sh.row_values(r)[col],0))
> This produces:
> release="(2008, 9, 8, 0, 0, 0)"
>
> Anyway, what I have expected from the cell evaluation done by xlrd is
> a string like "8.09.2008". Seems there is handwork necessary.
Some, but not much, giving you a lot more control in return. As John
mentioned in this code example, slightly modified:
import datetime
date_tuple = xlrd.xldate_as_tuple(sh.row_values(r)[col], book.datemode)
date = datetime.datetime(*date_tuple)
print date.isoformat()
Or only consume the parts of the date tuple you care about
(hours/minutes, year, whatever) and something with them. For example, I
recently only handled the hours and minutes (fair enough, that was all
that was in the cell) to match measurements of electrical load.
Wouter van Heyst
Strupp, Karsten (NSN - DE/Berlin)
|> get a date value from a file, *always* pass book.datemode as the second
|> argument of xl_date_as_tuple.
|>
|> > This produces:
|> > release="(2008, 9, 8, 0, 0, 0)"
|> >
|> > Anyway, what I have expected from the cell evaluation done
|> > by xlrd is a string like "8.09.2008".
|>
|> or 2008-09-08 or 9/8/2008 or ... ??? This would require xlrd to
|> know/guess your locale and use some locale-specific method
|> to format the
|> date for you ... and what if the next date value is 35
|> seconds after the
|> first; perhaps we should have formatted the first as
|> 2008-09-08T00:00:00?
|>
|>
|> A minute fraction of the handwork it took to get that float
|> value and
|> XL_CELL_DATE type to you.
Mainly I expected a string. I did not know what to do with these "tuples".
In PHP there is a date function which can format the date in many ways.
It's similar like formatting a string with % operator in Python 2.x.
Maybe the datetime modul will exactly do this.
Well, I found out I can access the elements of that tuple just like
it was an array (or in python called a list) by using their indices.
So I used simple string formatting:
date = xlrd.xldate_as_tuple(sh.row_values(r)[col],0)
dateStr = "%2d.%02d.%4d" % (date[2], date[1], date[0])
Some things are really simple. You just have to know about ...
BTW: I see that book.datemode should be used. But I know the source
and set it to 0. For me this is just fine.
John Machin
> |> Please read the documentation, and listen to Wouter: if you
> |> get a date value from a file, *always* pass book.datemode as the second
> |> argument of xl_date_as_tuple.
> BTW: I see that book.datemode should be used. But I know the source
> and set it to 0. For me this is just fine.
What does "know the source" mean and why do you imagine that it is
justification for taking a *needless* risk just to save typing 12
characters?
Strupp, Karsten (NSN - DE/Berlin)
|> > |> get a date value from a file, *always* pass
|> > |> book.datemode as the second
|> > |> argument of xl_date_as_tuple.
|>
|> > and set it to 0. For me this is just fine.
|>
|> What does "know the source" mean and why do you imagine that it is
|> justification for taking a *needless* risk just to save typing 12
|> characters?
MS-Excel on my Windows-PC. End of lifetime for the code will be in a
few weeks, so there is no risc. For those special cases I prefer to
have a code beeing as short and therefore as clear as possible.
Karsten
John Machin
> Hello John,
>
> |> It installs fine on a Windows 2000 SP4 box, for example;
> |> please let me know how you get on.
>
> So, I have installed the py254 version without any problems.
Clarifying for the benefit of other readers:
xlrd version 0.7.0 was successfully installed using a Windows installer
that had been built using Python version 2.5.4 -- a previous attempt
using an installer built using Python 2.6.1 had failed.
And that's great news.
I'm a little curious why only one problem report out of over 250
downloads from PyPI of the 2.6-built installer. I'm asking around to see
if I can find out if yours is an isolated case or if installers should
be built using 2.5 until further notice.
> I guess, the old version has just been overwritten because I have
> not seen any hint about update installation.
That's correct -- the xlrd version 0.6.1 will have been replaced by 0.7.0.
>
> Another problem we had installing the module appeared on another
> system which is a Linux computer and so we took the tar.gz version:
> thw@scotty:~/ein/xlrd-0.7.0: python setup.py
> Traceback (most recent call last):
> File "setup.py", line 14, in ?
> from distutils.core import setup
> ImportError: No module named distutils.core
That's a bit of a worry -- either it's a very ancient version of Python
on the Linux computer, or [more likely] it hasn't been installed
properly. The distutils ("distribution utilities") package is part of
the standard distribution, and that setup.py should work straight out of
the box at least as far back as Python 2.1.
Or perhaps it's because Python has been installed from a run-only (as
opposed to development) package which doesn't include distutils for some
crazy reason ...
I know nothing about the arcane mechanisms of Linux packaging systems.
If you care to tell:
1. what brand of Linux
2. the version and packaging info that Python prints when run from the shell
3. what Python has in sys.path ... do this:
import sys, pprint
pprint.pprint(sys.path)
one or more of the Linux-knowledgeable group members should be able to help.
> Thomas (thw) doesn't have root access to scotty. He solved this
> problem by unpacking the archive to a local directory. The
> import works this way also.
Yes, provided the directory is in sys.path.
Cheers,
John
Wouter van Heyst
...
> > Another problem we had installing the module appeared on another
> > system which is a Linux computer and so we took the tar.gz version:
> > thw@scotty:~/ein/xlrd-0.7.0: python setup.py
> > Traceback (most recent call last):
> > File "setup.py", line 14, in ?
> > from distutils.core import setup
> > ImportError: No module named distutils.core
>
> That's a bit of a worry -- either it's a very ancient version of Python
> on the Linux computer, or [more likely] it hasn't been installed
> properly. The distutils ("distribution utilities") package is part of
> the standard distribution, and that setup.py should work straight out of
> the box at least as far back as Python 2.1.
>
> Or perhaps it's because Python has been installed from a run-only (as
> opposed to development) package which doesn't include distutils for some
> crazy reason ...
Debian/Ubuntu have distutils.core in the python{2.4,2.5,3.0,etc}
packages. If only the -minimal variant is installed, then distutils.core
and most of stdlib will not be present. Those packages are intended for
scripts during the boot process, not end-user python consumption.
Wouter van Heyst
Chris Withers
> MS-Excel on my Windows-PC. End of lifetime for the code will be in a
> few weeks, so there is no risc.
There is always risk. There is simply no excuse for this kind of
stupidity...
> For those special cases I prefer to
> have a code beeing as short and therefore as clear as possible.
It's not clear, and it's not much shorter.
Chris
--
Simplistix - Content Management, Zope & Python Consulting
- http://www.simplistix.co.uk