ValueError: could not convert string to float: E-3

Question

I am trying to access some of the columns in a Microsoft Access database table which contains numbers of type double but I am getting the error mentioned in the title.The code used for querying the database is as below, the error is occurring in the line where cur.execute(....) command is executed. Basically I am trying filter out data captured in a particular time interval. If I exclude the columns CM3_Up, CG3_Up, CM3_Down, CG3_Down which contains double data type in the cur.execute(....) command I wont get the error. Same logic was used to access double data type from other tables and it worked fine, I am not sure what is going wrong.

Code:

start =datetime.datetime(2015,03,28,00,00)
a=start
b=start+datetime.timedelta(0,240)
r=7
while a < (start+datetime.timedelta(1)):
    params = (a,b)
    sql = "SELECT Date_Time, CM3_Up, CG3_Up, CM3_Down, CG3_Down FROM
          Lysimeter_Facility_Data_5 WHERE Date_Time >= ? AND Date_Time <= ?"
    for row in cur.execute(sql,params):
        if row is None:
            continue
        r = r+1
        ws.cell(row = r,column=12).value = row.get('CM3_Up')
        ws.cell(row = r,column=13).value = row.get('CG3_Up')
        ws.cell(row = r,column=14).value = row.get('CM3_Down')
        ws.cell(row = r,column=15).value = row.get('CG3_Down')

    a = a+five_min
    b = b+five_min
wb.save('..\SE_SW_Lysimeters_Weather_Mass_Experiment-02_03_26_2015.xlsx')

Complete error report:

Traceback (most recent call last):
  File "C:\DB_PY\access_mdb\db_to_xl.py", line 318, in <module>
    for row in cur.execute(sql,params):
  File "build\bdist.win32\egg\pypyodbc.py", line 1920, in next
    row = self.fetchone()
  File "build\bdist.win32\egg\pypyodbc.py", line 1871, in fetchone
    value_list.append(buf_cvt_func(alloc_buffer.value))
ValueError: could not convert string to float: E-3

score 0 · Answer 1 · edited Mar 07 '16 at 11:03

0

It sounds like you receive strings like '2.34E-3', so try with a conversion. Don't know Python, but in C# it could be like:

ws.cell(row = r,column=12).value = Convert.ToDouble(row.get('CM3_Up'))
ws.cell(row = r,column=13).value = Convert.ToDouble(row.get('CG3_Up'))
ws.cell(row = r,column=14).value = Convert.ToDouble(row.get('CM3_Down'))
ws.cell(row = r,column=15).value = Convert.ToDouble(row.get('CG3_Down'))

edited Mar 07 '16 at 11:03

maazza

7,016
15
63
96

answered Apr 15 '15 at 20:31

Gustav

53,498
7
29
55

In Python it would be `float(row.get('CM3_Up'))`. – Gord Thompson Apr 15 '15 at 21:04
I tried float(row.get('CM3_Up')) but the same error is displayed. Just to add some more detail, the values that are read from the access database are like: -2.91953029510136(CM3_Up), -84.8983788757143 (CG3_Up) , 3.04633983884509 (CM3_Down),-5.80184926507136(CG3_Down ). – nipidon Apr 24 '15 at 07:42
@Mukunda - Can you provide a small sample database with the table that causes the error? If so, please upload it to an Internet-accessible location (something like [Wikisend](http://wikisend.com/)) and post the link here so we can try to recreate the issue. – Gord Thompson Apr 24 '15 at 10:06
Please find the link for the sample database here, http://wikisend.com/download/504484/mukunda_sample.accdb – nipidon May 01 '15 at 03:48
That table is perfectly well. – Gustav May 01 '15 at 07:02
@Gustav, could you properly access the database with the code I have mentioned above or did you user your own code? – nipidon May 02 '15 at 16:25
I used Access 2013 to study the table. Otherwise I run VS2013 and have only done very little Python and with limited success. Did you check "replace" out: float(row.get('CM3_Up').replace('E', 'D')) – Gustav May 03 '15 at 06:46

score 0 · Answer 2 · edited May 23 '17 at 11:59

0

As to this discussion: Python: trouble reading number format

the trouble could be that e should be d, like:

float(row.get('CM3_Up').replace('E', 'D'))

Sounds weird to me though, but I know only little of Python.

edited May 23 '17 at 11:59

Community

1
1

answered May 01 '15 at 07:06

Gustav

53,498
7
29
55

This solution didn't work – nipidon May 08 '15 at 20:47
Then I'm out of ideas, sorry. – Gustav May 09 '15 at 08:46

ValueError: could not convert string to float: E-3

2 Answers2