python error in reading number from csv

Question

I have a csv file like - order_id,name,address
When i try to insert data from csv to postgresql table via python it does not read the number properly.

e.g let data be

order_id  |  name  |  address
----------+--------+----------
5432548543| Manish | Dummy Address

it reads the order_id like 5.43E+9 instead of whole number. my code is like:

filename.encode('utf-8')
with open(filename) as file:
    data = csv.DictReader(file)
    cur.executemany("""Insert into temp_unicom values(%(Order Id)s,%(Name)s,%(Address)s)""", data)

Here Order ID, Name, Address are headers of my csv file.

How to correctly format the data? EDIT::
Link to csv File CSV File

Maybe the number is read in correctly and you are just displaying it in scientific form? What type is the order_id column? — Karl Bartel
– Karl Bartel, Commented Jun 30, 2015 at 8:53
It is of varchar type. Because many order id contains alphabets too. — Manish Gupta
– Manish Gupta, Commented Jun 30, 2015 at 8:54
If you use a data file with a single entry in it (say the example you gave) - does that have the issue? Does your csv look like the one I posted in my answer? Just trying to figure out if it's an issue on the data side, or the postgres side. — Aidan Kane
– Aidan Kane, Commented Jun 30, 2015 at 9:11
Most look but some don't. When i expand the column width then they are shown correctly. — Manish Gupta
– Manish Gupta, Commented Jun 30, 2015 at 9:12

Aidan Kane · Accepted Answer · 2015-06-30 09:45:24Z

1

When I change the example you provided to a csv:

order_id,name,address
5432548543,Manish,Dummy Address

And just iterate over the rows, printing them out:

with open('./data.txt') as f:
    data = csv.DictReader(f)
    for row in data:
            print(l)

I get:

{'order_id': '5432548543', 'name': 'Manish', 'address': 'Dummy Address'}

Which suggests the issue isn't in the csv parsing step - but you should try the same thing on your data set to double check.

Then the question is - what is your postgres driver doing that could be causing the issue? Are you using psycopg2? Does it do some automatic casting somewhere?

EDIT so the problem is the src data. Sometimes you have ints in scientific notation. You need to scrub the data before passing it to executemany:

data = csv.DictReader(f)

clean_data = []
for d in data:
    clean_data.append(d)
    try:
        d['Order Id'] = str(int(float(d['Order Id'])))
    except ValueError:
        pass

cur.executemany("""Insert into temp_unicom values (%(Order Id)s, %(Name)s, %(Address)s)""", clean_data)

edited Jun 30, 2015 at 9:45

answered Jun 30, 2015 at 9:01

Aidan Kane

4,0162 gold badges29 silver badges29 bronze badges

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

14 Comments

Anand S Kumar Over a year ago

Issue maybe there in python 2.x?

Manish Gupta Over a year ago

Yes i am using psycopg2

Manish Gupta Over a year ago

I have some order_ids which contains alphabets with number. Will this code affect them too? I mean if this code will throw any error with alphanumeric order ids?

Aidan Kane Over a year ago

No, that's why we use the try...except block. Only integers will be effected. Actually, let's turn them back to strings too - to make sure everything is consistent. Will update the answer.

Aidan Kane Over a year ago

So an integer will go from '87176421' to 87176421.0 to 87176421 to '87176421' again. Scientific notation will be '5.43E+3' to 5430.0 to 5430 to '5430'. String will be 'some_string' to expection -> pass -> nothing changed (still 'some_string' in the dict).

|

Anand S Kumar · Accepted Answer · 2015-06-30 09:15:45Z

0

Try formatting the float to string before sending the dict.

Example -

cur.executemany("""Insert into temp_unicom values(%(Order Id)f,%(Name)s,%(Address)s)""",dict((k,v )if k != "Order Id" else (k,'%f'%(v)) for k,v in dict1.iteritems()))

Also, rename the dictionary to something else (I for example renamed to dict1 , because otherwise it would replace the built-in dict function.

edited Jun 30, 2015 at 9:15

answered Jun 30, 2015 at 9:01

Anand S Kumar

91.5k18 gold badges196 silver badges179 bronze badges

3 Comments

Aidan Kane Over a year ago

From the pyscopg2 docs (initd.org/psycopg/docs/…) "The variables placeholder must always be a %s, even if a different placeholder (such as a %d for integers or %f for floats) may look more appropriate"

Aidan Kane Over a year ago

Looking at the docs it looks as though executemany should faithfully pass through whichever string is in the dict. It feels like a case where maybe the src data could have the inconsistencies.

Anand S Kumar Over a year ago

Tried a newer approach on changing the dict itself to hold formatted string, rather than float.

Collectives™ on Stack Overflow

python error in reading number from csv

2 Answers 2

14 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

14 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related