1

I am just trying to connect to an .accdb and read in an existing table. I am using pypypodbc to connect:

import pandas as pd
import pypyodbc

conn = pypyodbc.connect(
    r"Driver={Microsoft Access Driver (*.mdb, *.accdb)};"
    r"Dbq=C:\Users\Public\Data.accdb;")

The connection seems to work and I can read in most tables using:

data = pd.read_sql('SELECT * FROM TABLE', conn)

However, when I try to read in a table with a column containing Double values with varying decimal points (but no nulls) I get the following:

ValueError: could not convert string to float: b'E-2'

How can I get this data in to pandas?

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
Gman
  • 134
  • 2
  • 9
  • The error message is telling you that the column contains the value 'E-2' ... Is it possible that Access allows this in a Float column? – maxymoo Aug 03 '15 at 23:30
  • The column type is double so it shouldn't allow any text values. I also did a search for 'E-2' which doesn't seem to exist, there is however some scientific notation where a value might end in E-02. could this be part of the problem? – Gman Aug 03 '15 at 23:36
  • hmm yes that could definately be what's causing it, check out this forum thread http://www.pcreview.co.uk/threads/avoid-scientific-notation-in-my-number-field.3303779/ – maxymoo Aug 03 '15 at 23:38
  • 1
    Thanks for that, it turns out that this was indeed the problem. To get around the problem I had to reduce the number of decimal places by multiplying the data by some factor , converting to an intiiger, dividing it by the same factor and converting to a double again . – Gman Aug 04 '15 at 00:30

0 Answers0