1. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2005
    Rep Power

    python and odbc dsn name?


    trying to use odbc with an excel file but it says:
    import dbi,odbc
    s = odbc.odbc('test.xls')
    Traceback (most recent call last):
      File "<interactive input>", line 1, in ?
    dbi.operation-error: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified in LOGIN
    Looks like i have to declare the DSN. Can this be done via Python or am I looking at windows to declare Excel a source of the odbc connection? (please say no

    although there are examples online, none actually show the dsn being connected or a real example path for the odbc.odbc method call. any basic tips would be great and I'm sure add useful documentation for newbs like me.

    to head off any questions, I'm doing this so the script only uses plain modules because this is going on another machine without internet access. target computer just has python 2.2, win32all, mysql 3.23 and msoffice.

    As an alternative, I saw some ConfigParser script in PythonCard connect to a csv file, so I could also go that route too, but the DSN question still remains.

    thanks for any help

  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2004
    Regensburg, Germany
    Rep Power
    To access an excel file at 'd:\test\path\test.xls' you need to create a DSN like this:
    import dbi,odbc
    s = odbc.odbc(r"Driver={Microsoft Excel Driver (*.xls)};DriverId=790;DBQ=d:\test\path\test.xls;ReadOnly= false"
    Set the 'ReadOnly' flag to 'true' if you don't want to write to the file.

    To retrieve the the rows on a sheet with name 'MySheet', the query string should look like:
    "select * from [mysheet$]"
    Note that the fetchall() method of the cursor of the connection returns all rows of an excel sheet even if they are empty.

    Comments on this post

    • sf2k agrees : thanks for the tip! I'll give it a try.

IMN logo majestic logo threadwatch logo seochat tools logo