{"action":"created","issue":{"url":"https://api.github.com/repos/alimanfoo/petl/issues/383","repository_url":"https://api.github.com/repos/alimanfoo/petl","labels_url":"https://api.github.com/repos/alimanfoo/petl/issues/383/labels{/name}","comments_url":"https://api.github.com/repos/alimanfoo/petl/issues/383/comments","events_url":"https://api.github.com/repos/alimanfoo/petl/issues/383/events","html_url":"https://github.com/alimanfoo/petl/issues/383","id":169650947,"number":383,"title":"How to work around xlrd.xldate.XLDateAmbiguous exception?","user":{"login":"TravisDart","id":5155310,"avatar_url":"https://avatars.githubusercontent.com/u/5155310?v=3","gravatar_id":"","url":"https://api.github.com/users/TravisDart","html_url":"https://github.com/TravisDart","followers_url":"https://api.github.com/users/TravisDart/followers","following_url":"https://api.github.com/users/TravisDart/following{/other_user}","gists_url":"https://api.github.com/users/TravisDart/gists{/gist_id}","starred_url":"https://api.github.com/users/TravisDart/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/TravisDart/subscriptions","organizations_url":"https://api.github.com/users/TravisDart/orgs","repos_url":"https://api.github.com/users/TravisDart/repos","events_url":"https://api.github.com/users/TravisDart/events{/privacy}","received_events_url":"https://api.github.com/users/TravisDart/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":1,"created_at":"2016-08-05T16:56:41Z","updated_at":"2016-08-05T19:17:58Z","closed_at":null,"body":"I am importing an Excel (.xls) file with `etl.io.xls.fromxls()` and I get the error: \r\n\r\n    Traceback (most recent call last):\r\n      File \"excel.py\", line 44, in <module>\r\n        etl.todb(table, cursor, 'production_board')\r\n      File \"/usr/local/lib/python2.7/site-packages/petl/io/db.py\", line 337, in todb\r\n        truncate=True)\r\n      File \"/usr/local/lib/python2.7/site-packages/petl/io/db.py\", line 362, in _todb\r\n        truncate=truncate)\r\n      File \"/usr/local/lib/python2.7/site-packages/petl/io/db.py\", line 528, in _todb_dbapi_cursor\r\n        cursor.executemany(insertquery, it)\r\n      File \"/usr/local/lib/python2.7/site-packages/petl/transform/headers.py\", line 143, in itersetheader\r\n        for row in it:\r\n      File \"/usr/local/lib/python2.7/site-packages/petl/io/xls.py\", line 42, in __iter__\r\n        yield tuple(row)\r\n      File \"/usr/local/lib/python2.7/site-packages/petl/io/xlutils_view.py\", line 82, in __row\r\n        date_parts = xldate_as_tuple(value, self.book.datemode)\r\n      File \"/Library/Python/2.7/site-packages/xlrd/xldate.py\", line 86, in xldate_as_tuple\r\n        raise XLDateAmbiguous(xldate)\r\n    xlrd.xldate.XLDateAmbiguous: 8.0\r\n\r\n(You may notice from the traceback that it isn't until I try to import the sheet with `etl.todb()` that the error is thrown.)\r\n\r\nI can import the sheet if I use the `use_view=False` option for `etl.io.xls.fromxls()`. That's the workaround that I'm using now.\r\n\r\nI recognize that this is an xlrd error, not a petl error. But using petl, how do I work around this error? Ideally, I'd like to replace the value or something.\r\n\r\n(Note that OpenOffice renders this date as \"January 7, 1900\".)\r\n\r\nThanks."},"comment":{"url":"https://api.github.com/repos/alimanfoo/petl/issues/comments/237939194","html_url":"https://github.com/alimanfoo/petl/issues/383#issuecomment-237939194","issue_url":"https://api.github.com/repos/alimanfoo/petl/issues/383","id":237939194,"user":{"login":"alimanfoo","id":703554,"avatar_url":"https://avatars.githubusercontent.com/u/703554?v=3","gravatar_id":"","url":"https://api.github.com/users/alimanfoo","html_url":"https://github.com/alimanfoo","followers_url":"https://api.github.com/users/alimanfoo/followers","following_url":"https://api.github.com/users/alimanfoo/following{/other_user}","gists_url":"https://api.github.com/users/alimanfoo/gists{/gist_id}","starred_url":"https://api.github.com/users/alimanfoo/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/alimanfoo/subscriptions","organizations_url":"https://api.github.com/users/alimanfoo/orgs","repos_url":"https://api.github.com/users/alimanfoo/repos","events_url":"https://api.github.com/users/alimanfoo/events{/privacy}","received_events_url":"https://api.github.com/users/alimanfoo/received_events","type":"User","site_admin":false},"created_at":"2016-08-05T19:17:58Z","updated_at":"2016-08-05T19:17:58Z","body":"Hi Travis,\n\nI think the only option is to set use_view=False when extracting from the\nExcel file, which (IIRC) will give you the raw number value for each date,\nthen fix the dodgy values with a petl function like convert() or replace().\nThen if you want to then convert the raw date numbers into whatever you\nwould get if you had one use_view=True, there should be a function\nsomewhere inside xlrd (maybe xldate_as_tuple?) you could use with petl\nconvert().\n\nE.g., something along the lines of (untested)...\n\ntbl = (\n    etl.fromxls('foo.xls', use_view=False)\n    .replace('my_date_field', 8.0, [some valid date value])\n    .convert('my_date_field', lambda v: xldate_as_tuple(v, book.datemode)\n\n)\n\nYou would have to get the book variable from somewhere I guess. Sorry this\nis a bit vague, hope it helps in the right direction.\n\nYou can always do tbl.head() to check if this is working as expected, or\njust return tbl to the console. The error will not get triggered until you\nactually pull the offending row through somehow, e.g., by calling todb() or\nnrows() or any other function that will iterate over the table. Petl does\nno work until you try and iterate.\n\nHth,\nAlistair\n\nOn Friday, August 5, 2016, Travis Dart <notifications@github.com> wrote:\n\n> I am importing an Excel (.xls) file with etl.io.xls.fromxls() and I get\n> the error:\n>\n> Traceback (most recent call last):\n>   File \"excel.py\", line 44, in <module>\n>     etl.todb(table, cursor, 'production_board')\n>   File \"/usr/local/lib/python2.7/site-packages/petl/io/db.py\", line 337, in todb\n>     truncate=True)\n>   File \"/usr/local/lib/python2.7/site-packages/petl/io/db.py\", line 362, in _todb\n>     truncate=truncate)\n>   File \"/usr/local/lib/python2.7/site-packages/petl/io/db.py\", line 528, in _todb_dbapi_cursor\n>     cursor.executemany(insertquery, it)\n>   File \"/usr/local/lib/python2.7/site-packages/petl/transform/headers.py\", line 143, in itersetheader\n>     for row in it:\n>   File \"/usr/local/lib/python2.7/site-packages/petl/io/xls.py\", line 42, in __iter__\n>     yield tuple(row)\n>   File \"/usr/local/lib/python2.7/site-packages/petl/io/xlutils_view.py\", line 82, in __row\n>     date_parts = xldate_as_tuple(value, self.book.datemode)\n>   File \"/Library/Python/2.7/site-packages/xlrd/xldate.py\", line 86, in xldate_as_tuple\n>     raise XLDateAmbiguous(xldate)\n> xlrd.xldate.XLDateAmbiguous: 8.0\n>\n> (You may notice from the traceback that it isn't until I try to import the\n> sheet with etl.todb() that the error is thrown.)\n>\n> I can import the sheet if I use the use_view=False option for\n> etl.io.xls.fromxls(). That's the workaround that I'm using now.\n>\n> I recognize that this is an xlrd error, not a petl error. But using petl,\n> how do I work around this error? Ideally, I'd like to replace the value or\n> something.\n>\n> (Note that OpenOffice renders this date as \"January 7, 1900\".)\n>\n> Thanks.\n>\n> —\n> You are receiving this because you are subscribed to this thread.\n> Reply to this email directly, view it on GitHub\n> <https://github.com/alimanfoo/petl/issues/383>, or mute the thread\n> <https://github.com/notifications/unsubscribe-auth/AAq8QszqQLIlKQOPgr5gBRxyiSLCmhAoks5qc2tJgaJpZM4Jd3n0>\n> .\n>\n\n\n-- \nAlistair Miles\nHead of Epidemiological Informatics\nCentre for Genomics and Global Health <http://cggh.org>\nThe Wellcome Trust Centre for Human Genetics\nRoosevelt Drive\nOxford\nOX3 7BN\nUnited Kingdom\nEmail: alimanfoo@googlemail.com <alimanfoo@gmail.com>\nWeb: http://purl.org/net/aliman\nTwitter: https://twitter.com/alimanfoo\nTel: +44 (0)1865 287721\n"}}
{"actor":{"display_login":"alimanfoo"}}