ENH: Excel to support reading Timedeltas

[timmie]

ExcelFile should print out line or even cell warnings

Today I was spending quite some time debugging why a decoding error stopped the code from reading in a table.

I thought the skiprows counts from 0 (= Excel row 1). It was always failing.

In one line there were column headers, the next line contained units which couldn't probably be parsed.

I think it could be helpful if the parser would show the line number or even cell were it fails to ready (like due to decoding errors).

[jreback]

can you put post the error you did get?

[jtratner]

Also it would be helpful if you could print the versions of pandas and xlrd you are using (might need openpyxl if you're not using the dev version.)

[timmie]

pd.__version__
Out[116]: '0.9.1'

import xlrd

xlrd.__VERSION__
Out[118]: '0.9.1'

import openpyxl

openpyxl.__version__
Out[120]: '1.5.8'

[timmie]

please see an example error msg at #4339

[jreback]

@timmie

you have a pretty old version of pandas, 0.11 has been out since april, and 0.12 is releasing this week. excel parsing uses the csv parser under the hood, and pretty sure that all of your 3 posted issues are fixed in more recent versions. Pls try and close these issues if that is the case. (e.g. #4332 , #4340)

[timmie]

With

In [38]: pd.__version__

Out[38]: '0.12.0rc1'

The issue does nit arise. But now I get:

XLDateAmbiguous: 1.0

even if I change to parse_dates=False and index_col=0.

There is one column in the xlsx that has time (not date).

But aapraently, the parser expects a datetime:

C:\Python27\lib\site-packages\pandas\io\excel.pyc in _parse_excel(self, sheetname, header, skiprows, skip_footer, index_col, has_index_names, parse_cols, parse_dates, date_parser, na_values, thousands, chunksize, **kwds)
    184                 if parse_cols is None or should_parse[j]:
    185                     if typ == XL_CELL_DATE:
--> 186                         dt = xldate_as_tuple(value, datemode)
    187                         # how to produce this first case?
    188                         if dt[0] < datetime.MINYEAR:  # pragma: no cover

C:\Python27\lib\site-packages\xlrd\xldate.pyc in xldate_as_tuple(xldate, datemode)
     78 
     79     if xldays < 61 and datemode == 0:
---> 80         raise XLDateAmbiguous(xldate)
     81 
     82     jdn = xldays + _JDN_delta[datemode]

[jreback]

This is essentially a timedelta. Maybe just change the column formatting to text?

[timmie]

Can I not get around this?
Like let the program read it as strings and then convert later?

I am receiving these tables from elsewhere. So the process should be automatised and I'd rather not touch the Excel tables.

[jreback]

you could try passing : dtype = { 'column_name' : object }

[jtratner]

@timmie that sounds like an xlrd error. What happens if you just try to
read the spreadsheet outside of pandas?

On Wed, Jul 24, 2013 at 8:50 AM, jreback notifications@github.com wrote:

you could try passing : dtype = { 'column_name' : object }

—
Reply to this email directly or view it on GitHubhttps://github.com//issues/4332#issuecomment-21482111
.

[timmie]

@jreback

it says: ValueError: dtype is not supported with python parser

[timmie]

@jtratner
in the source xlsx file I removed the date column. Now everything goes in smoothly.

So we would need to find a way to read the time cloumn.
In Excel, the cell properties say "userdefined".

So can we read it as string or alike?

[jreback]

ok...so maybe 2 bugs here, I thought dtype in the PythonParser worked.....

and 2 processing as @jtratner suggest....