how to create a mdb file from a csv file in python?

Question

I have several csv files that I need to transfer over to mdb format. I tried the answer in this post as a starting point:

How do you create a mdb database file in Python?

Source:

from comtypes.client import CreateObject

access = CreateObject('Access.Application')

from comtypes.gen import Access

DBEngine = access.DBEngine
db = DBEngine.CreateDatabase('test.mdb', Access.DB_LANG_GENERAL)


db.BeginTrans()

db.Execute("CREATE TABLE test (ID Text, numapples Integer)")
db.Execute("INSERT INTO test VALUES ('ABC', 3)")

db.CommitTrans()
db.Close()

But I received the following errors:

Traceback (most recent call last):
  File "C:\Documents and Settings\rkelly1\Desktop\New Folder (6)\testwrite.py", line 3, in ?
access = CreateObject('Access.Application')
  File "C:\Python24\Lib\site-packages\comtypes\client\__init__.py", line 242, in CreateObject
return _manage(obj, clsid, interface=interface)
  File "C:\Python24\Lib\site-packages\comtypes\client\__init__.py", line 188, in _manage
obj = GetBestInterface(obj)
  File "C:\Python24\Lib\site-packages\comtypes\client\__init__.py", line 112, in GetBestInterface
interface = getattr(mod, itf_name)
AttributeError: 'module' object has no attribute '_Application'

All I need to do is take a .csv file, and create a database with it in mdb format. I have some experience with sql but not with creating access data base files....

EDIT:

I am not proposing that this is the right solution.... If you have a better one please let me know

EDIT: The first time the script runs after a fresh install of comtypes I get these errors:

# Generating comtypes.gen._4AFFC9A0_5F99_101B_AF4E_00AA003F0F07_0_9_0
# Generating comtypes.gen._2DF8D04C_5BFA_101B_BDE5_00AA0044DE52_0_2_4
# Generating comtypes.gen._00020430_0000_0000_C000_000000000046_0_2_0
# Generating comtypes.gen.stdole
Traceback (most recent call last):
  File "C:\Documents and Settings\rkelly1\Desktop\New Folder (6)\testwrite.py", line 3, in ?
    access = CreateObject('Access.Application')
  File "C:\Python24\Lib\site-packages\comtypes\client\__init__.py", line 242, in CreateObject
    return _manage(obj, clsid, interface=interface)
  File "C:\Python24\Lib\site-packages\comtypes\client\__init__.py", line 188, in _manage
    obj = GetBestInterface(obj)
  File "C:\Python24\Lib\site-packages\comtypes\client\__init__.py", line 110, in GetBestInterface
    mod = GetModule(tlib)
  File "C:\Python24\Lib\site-packages\comtypes\client\_generate.py", line 112, in GetModule
    mod = _CreateWrapper(tlib, pathname)
  File "C:\Python24\Lib\site-packages\comtypes\client\_generate.py", line 176, in _CreateWrapper
    generate_module(tlib, ofi, pathname)
  File "C:\Python24\Lib\site-packages\comtypes\tools\tlbparser.py", line 716, in generate_module
    gen.generate_code(items.values(), filename=pathname)
  File "C:\Python24\Lib\site-packages\comtypes\tools\codegenerator.py", line 238, in generate_code
    self.generate_all(items)
  File "C:\Python24\Lib\site-packages\comtypes\tools\codegenerator.py", line 186, in generate_all
    self.generate(item)
  File "C:\Python24\Lib\site-packages\comtypes\tools\codegenerator.py", line 182, in generate
    mth(item)
  File "C:\Python24\Lib\site-packages\comtypes\tools\codegenerator.py", line 689, in ComInterface
    self.generate(itf.get_head())
  File "C:\Python24\Lib\site-packages\comtypes\tools\codegenerator.py", line 182, in generate
    mth(item)
  File "C:\Python24\Lib\site-packages\comtypes\tools\codegenerator.py", line 710, in ComInterfaceHead
    self.generate(base.get_head())
  File "C:\Python24\Lib\site-packages\comtypes\tools\codegenerator.py", line 182, in generate
    mth(item)
  File "C:\Python24\Lib\site-packages\comtypes\tools\codegenerator.py", line 616, in External
    comtypes.client.GetModule(ext.tlib)
  File "C:\Python24\Lib\site-packages\comtypes\client\_generate.py", line 112, in GetModule
    mod = _CreateWrapper(tlib, pathname)
  File "C:\Python24\Lib\site-packages\comtypes\client\_generate.py", line 188, in _CreateWrapper
    mod = _my_import(fullname)
  File "C:\Python24\Lib\site-packages\comtypes\client\_generate.py", line 26, in _my_import
    return __import__(fullname, globals(), locals(), ['DUMMY'])
  File "C:\Python24\lib\site-packages\comtypes\gen\_2DF8D04C_5BFA_101B_BDE5_00AA0044DE52_0_2_4.py", line 82
    ( ['retval', 'out'], POINTER(POINTER(IDispatch)), 'ppidisp' )),
COMMETHOD([dispid(1610743809), 'propget'], HRESULT, 'Creator',
                                                               ^
SyntaxError: invalid syntax

Without the source of your script it is very difficult to guess what's going on there. — Mad Scientist
– Mad Scientist, Commented Sep 2, 2010 at 13:22
@Fabian the source was from the linked answer, but I get what you are saying. Editing now... — Richard
– Richard, Commented Sep 2, 2010 at 13:24

tgray · Accepted Answer · 2010-09-02 13:59:53Z

4

First thing I would suggest is upgrading to python 2.7 if at all possible.

Second, have you tried win32com?

Here's a test script that does what you have above:

import win32com.client
import os

def main():
    db_path = r'C:\temp.mdb'
    if os.path.exists(db_path):
        os.remove(db_path)

    db_eng = win32com.client.gencache.EnsureDispatch("DAO.DBEngine.36")
    db = db_eng.CreateDatabase(db_path, win32com.client.constants.dbLangGeneral)

    db.Execute("CREATE TABLE test (ID Text, numapples Integer)")
    db.Execute("INSERT INTO test VALUES ('ABC', 3)")

    db.Close()


if __name__ == '__main__':
    main()

answered Sep 2, 2010 at 13:59

tgray

8,9865 gold badges38 silver badges41 bronze badges

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

2 Comments

Richard Over a year ago

I find moving to python 2.7 a scary task seeing that most of the scripts that I maintain are written with python 2.4 :S

David-W-Fenton Over a year ago

One advantage of this approach is that it depends on components installed on every copy of Windows (since Win2000) -- it uses DAO and the Jet database engine, instead of attempting to automate Access, which may not be installed.

Katriel · Accepted Answer · 2010-09-02 13:28:08Z

2

You haven't got Access installed on the computer on which this script is running.

answered Sep 2, 2010 at 13:28

Katriel

124k19 gold badges141 silver badges172 bronze badges

2 Comments

Richard Over a year ago

hmmm... But I do. I have access 2007

Katriel Over a year ago

@Richard: I can't reproduce this at all... you are doing the right thing. I would try @tgray's answer.

pypyodbc · Accepted Answer · 2012-09-29 01:34:36Z

0

Try PyPyODBC, it can be as easy as:

import pypyodbc
pypyodbc.win_create_mdb( "D:\\Your MDB file path.mdb" )

https://code.google.com/p/pypyodbc/wiki/pypyodbc_for_access_mdb_file

answered Sep 29, 2012 at 1:34

pypyodbc

1,1391 gold badge12 silver badges9 bronze badges

1 Comment

x89 Over a year ago

how can we do this in macOS?

Collectives™ on Stack Overflow

how to create a mdb file from a csv file in python?

3 Answers 3

2 Comments

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related