Not able to extract strings containing apostrophe or single quotation mark of a excel cell
pradnya
i have used openpyxl module to import excel data into database, data
contains apostrophe mark and other punctuations marks .but when tried
to run i got error
UnicodeEncodeError: 'charmap' codec can't encode character u'\u2019'
in position
4: character maps to <undefined>
when tried to find this python code
import unicodedata
unicodedata.name(u'\u2019')
i get : 'RIGHT SINGLE QUOTATION MARK'
is there some problem data in the cell or are there any issues with
parsing of data in openpyxl module
Sample data giving me error is :The Banker's algorithm is used
Eric Gazoni
maybe you could use the encoding parameter when creating your workbook:
wb = openpyxl.workbook.Workbook(encoding='utf-8') # replace utf-8 by the
encoding you're actually using in your database ;-)
By default, it's using utf-8, and make sure you've got the latest
version of openpyxl, as encoding was only added recently.
Cheers,
Eric
Le 7/11/11 14:08, pradnya a �crit :
Brent Hoover
I can give you a better answer later today once I have a chance to look at the code again.
Brent Hoover
Computer Scientist
323-687-3265
pradnya
Thanks for your reply.
But Eric the version i am using is 1.5.6 and i m not creating object
of workbook
here is the code
from openpyxl.reader.excel import load_workbook
def main():
wb = load_workbook(filename = u'test.xlsx')
for sheet in wb.worksheets:
for row in sheet.rows:
for cell in row:
print cell.value
and the xlsx file contains two rows as
1 Subject Test 1
2 Question What is the banker’s algorithm ?
and the error is :
Traceback (most recent call last):
File "test.py", line 9, in <module>
main()
File "test.py", line 7, in main
print cell.value
File "C:\Python26\lib\encodings\cp437.py", line 12, in encode
return codecs.charmap_encode(input,errors,encoding_map)
in position
Eric Gazoni
have a look there :
http://stackoverflow.com/questions/3224268/python-unicode-encode-error
When you're printing cell.value, actually you're trying to decode the
string contained inside value using your system default encoding. Try to
do "print cell.value.decode('utf-8')" if your workbook is in utf-8 for
example.
Cheers,
Eric
Le 11/11/11 07:08, pradnya a �crit :
> hi,
> Thanks for your reply.
> But Eric the version i am using is 1.5.6 and i m not creating object
> of workbook
> here is the code
>
> from openpyxl.reader.excel import load_workbook
> def main():
> wb = load_workbook(filename = u'test.xlsx')
> for sheet in wb.worksheets:
> for row in sheet.rows:
> for cell in row:
> print cell.value
>
> and the xlsx file contains two rows as
>
> 1 Subject Test 1
> 2 Question What is the banker�s algorithm ?
pradnya
cell.value.encode('ascii','ignore')
solved the issue little bit
the output i m getting for What is the banker's algorithm ?
is What is the bankers algorithm ?
Is there any way to get the apostrophe ?
Eric Gazoni
map, and to 'ignore' what's not in the character map (that's why it
displays the regular alphabet letters, but not the apostrophe that was
causing issue since the begining).
To get the apostrophe, I'd suggest to use a different encoding, that
contains the apostrophe. That's why it's important to know what is the
original encoding of your file, usually in europe, you can try 'latin1'
or 'utf-8' and get lucky.
Cheers,
Eric
Le 12/11/11 07:52, pradnya a �crit :
sjma...@lexicon.net
Your problem is nothing to do with openpyxl at all. Your problem can be summarised in the following one-line script:
print u'\u2019\n
You are currently running this in a Command Prompt window on a Windows machine. sys.stdout.encoding is set to the antique MS-DOS cp437. Result: splat.
Option 1: Run your script in an environment (e.g. IDLE) where writing unicode objects to stdout produces a better outcome.
Option 2: Substitute replacement characters before you write to stdout. You are likely to find other non-cp437-able characters in other workbooks. You may want to use the unicode.translate method (http://docs.python.org/library/stdtypes.html#str.translate) instead of just your_unicode_object.replace(u"\u2019", u"'")
sjma...@lexicon.net
On Thursday, November 10, 2011 12:37:34 AM UTC+11, Brent Hoover wrote:
It's also possible that the spreadsheet is using 'latin-1' encoding rather than utf8. This is the default encoding for Windows and the right quotation mark would be an example of a character that would be not be the same between the two character sets.