Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss
openpyxl reads cell with format
1,614 views
Skip to first unread message
Mahmood Naderan
Jun 5, 2017, 5:10:43 AM6/5/17
to
Hello guys...
With openpyxl, it seems that when the content of a cell is something like "4-Feb", then it is read as "2016-02-04 00:00:00" that looks like a calendar conversion.
How can I read the cell as text instead of such an automatic conversion?
Regards,
Mahmood
With openpyxl, it seems that when the content of a cell is something like "4-Feb", then it is read as "2016-02-04 00:00:00" that looks like a calendar conversion.
How can I read the cell as text instead of such an automatic conversion?
Regards,
Mahmood
Mahmood Naderan
Jun 5, 2017, 10:46:35 AM6/5/17
to
>if the cell is an Excel date, it IS stored as a numeric
As I said, the "shape" of the cell is similar to date. The content which is "4-Feb" is not a date. It is a string which I expect from cell.value to read it as "4-Feb" and nothing else.
Also, I said before that the file is downloaded from a website. That means, from a button on a web page, I chose "export as excel" to download the data. I am pretty sure that auto format feature of the excel is trying to convert it as a date.
So, I am looking for a way to ignore such auto formatting.
Regards,
Mahmood
As I said, the "shape" of the cell is similar to date. The content which is "4-Feb" is not a date. It is a string which I expect from cell.value to read it as "4-Feb" and nothing else.
Also, I said before that the file is downloaded from a website. That means, from a button on a web page, I chose "export as excel" to download the data. I am pretty sure that auto format feature of the excel is trying to convert it as a date.
So, I am looking for a way to ignore such auto formatting.
Regards,
Mahmood
Mahmood Naderan
Jun 5, 2017, 3:33:46 PM6/5/17
to
OK thank you very much. As you said, it seems that it is too late for my python script.
Regards,
Mahmood
On Monday, June 5, 2017 10:41 PM, Dennis Lee Bieber <wlf...@ix.netcom.com> wrote:
On Mon, 5 Jun 2017 14:46:18 +0000 (UTC), Mahmood Naderan via Python-list
<pytho...@python.org> declaimed the following:
>>if the cell is an Excel date, it IS stored as a numeric
>
>As I said, the "shape" of the cell is similar to date. The content which is "4-Feb" is not a date. It is a string which I expect from cell.value to read it as "4-Feb" and nothing else.
>
>Also, I said before that the file is downloaded from a website. That means, from a button on a web page, I chose "export as excel" to download the data. I am pretty sure that auto format feature of the excel is trying to convert it as a date.
>
Then you'll have to modify the Excel file before the "export" to tell
IT that the column is plain text BEFORE the data goes into the column.
The normal behavior for Excel is: if something looks like a date
(partial or full) when entered, Excel will store it as a numeric "days from
epoch" and flag the cell as a "date" field. The visual representation is up
to the client -- as my sample table shows, the very same input value looks
different based upon how the column is defined.
>
>So, I am looking for a way to ignore such auto formatting.
>
By the time Python sees it, it is too late -- all you have is an
integer number tagged as a "date", and an import process that renders that
number as a Python datetime object (which you can then render however you
want
https://docs.python.org/2/library/datetime.html#strftime-and-strptime-behavior
)
--
Wulfraed Dennis Lee Bieber AF6VN
wlf...@ix.netcom.com HTTP://wlfraed.home.netcom.com/
--
https://mail.python.org/mailman/listinfo/python-list
Regards,
Mahmood
On Monday, June 5, 2017 10:41 PM, Dennis Lee Bieber <wlf...@ix.netcom.com> wrote:
On Mon, 5 Jun 2017 14:46:18 +0000 (UTC), Mahmood Naderan via Python-list
<pytho...@python.org> declaimed the following:
>>if the cell is an Excel date, it IS stored as a numeric
>
>As I said, the "shape" of the cell is similar to date. The content which is "4-Feb" is not a date. It is a string which I expect from cell.value to read it as "4-Feb" and nothing else.
>
>Also, I said before that the file is downloaded from a website. That means, from a button on a web page, I chose "export as excel" to download the data. I am pretty sure that auto format feature of the excel is trying to convert it as a date.
>
IT that the column is plain text BEFORE the data goes into the column.
The normal behavior for Excel is: if something looks like a date
(partial or full) when entered, Excel will store it as a numeric "days from
epoch" and flag the cell as a "date" field. The visual representation is up
to the client -- as my sample table shows, the very same input value looks
different based upon how the column is defined.
>
>So, I am looking for a way to ignore such auto formatting.
>
integer number tagged as a "date", and an import process that renders that
number as a Python datetime object (which you can then render however you
want
https://docs.python.org/2/library/datetime.html#strftime-and-strptime-behavior
)
--
Wulfraed Dennis Lee Bieber AF6VN
wlf...@ix.netcom.com HTTP://wlfraed.home.netcom.com/
--
https://mail.python.org/mailman/listinfo/python-list
Thomas Jollans
Jun 5, 2017, 4:39:26 PM6/5/17
to
date cell (this is presumably stored in the xlsx file) and to format the
value in the same way.
The content of the cell is a (numeric) date value. The cell has an
associated date format. You can verify this if you open the file in
Excel. If you don't believe what Excel tells you, you can have a look at
the actual XML.
(I have no idea how to get the date format with openpyxl, but I'm sure
that's what you want to do if you need the string "4-Feb" - unless you
can show us that that string is stored in the XML file)
-- Thomas
Gregory Ewing
Jun 5, 2017, 7:55:26 PM6/5/17
to
Mahmood Naderan wrote:
> from a button on a web page, I chose "export as excel" to download the data.
Do you get an option to export in any other format?
> from a button on a web page, I chose "export as excel" to download the data.
CSV would be best, since you can trivially read that
with Python's csv module.
If Excel is the only format available, you should
complain to the website maintainers that their
export function is broken and is corrupting data.
--
Greg
Christopher Reimer
Jun 5, 2017, 10:11:00 PM6/5/17
to
web page into a timezone-aware timestamp to save into a CSV file that
was sortable in Excel. It took me a while to figure out that my source
timestamp was set to US/Eastern even though I was viewing it as
US/Pacific on the website. I wrote down my thought process in a blog
post that may help this situation.
https://www.kickingthebitbucket.com/2017/04/04/the-python-time-zone-rabbit-hole/
Thank you,
Chris Reimer
0 new messages