import data from excel to postgres in python using pyodbc

Question

I am importing data from MS-Excel to PostgreSQL in python(2.6) using pyodbc.

The problem faced is:

There are characters like left single quotation mark(ANSI hex code : 0x91), etc in the excel source. Now, when it is import into PostgreSQL using pyodbc, it terminates and gives the error DatabaseError: invalid byte sequence for encoding "UTF8": 0x91.

What I tried: I used decode('unicode_escape') for the time being. But, this cannot be done as this simply removes/escapes the concerned character.

Alternate trial: Decode initially, Unicode everywhere and then Encode later when needed from database. This can also not be done due to the expanse of the project at hand.

Please suggest me some method/procedure/in-built functions to accomplish the task.

Don't know if this answer your question. But you might find this related answer of some use. — mac
– mac, Commented Nov 23, 2011 at 8:07
Hey, thanx mac for your efforts. I read that and tried using encode('utf-8') but the following error comes up : UnicodeDecodeError: 'ascii' codec can't decode byte 0x92 in position 33: ordinal not in range(128) — Sandip Agarwal
– Sandip Agarwal, Commented Nov 23, 2011 at 8:24

Erwin Brandstetter · Accepted Answer · 2011-11-23 11:25:55Z

1

Find out the real encoding of the source document. It might be WIN1251. Either transcode it (for instance with iconv) or set the client_encoding of PostgreSQL accordingly.

If you don't have a setting in pyodbc (which I don't know), you can always issue a plain SQL command:

SET CLIENT_ENCODING TO 'WIN1251';

More in the chapter "Automatic Character Set Conversion Between Server and Client" of the manual.

answered Nov 23, 2011 at 11:25

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sandip Agarwal Over a year ago

Hey,Erwin. Could we change the already created database's encoding to the required/said encoding?

Erwin Brandstetter Over a year ago

@SandipAgarwal: No. It is possible to create a new database (based on template0) in the same db cluster with a different encoding, but the encoding must be compatible with your locale settings, which narros it down. Here is a related posting on SO. I am not convinced this would be a good idea to fix your problem.

Collectives™ on Stack Overflow

import data from excel to postgres in python using pyodbc

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related