Welcome, guest | Sign In | My Account | Store | Cart

Parses the output of MS Access's documenter report and generates sql create table statements for the tables in the report. To use - run the documenter and save its output in a text file named documenter.txt. Then run this program. Output goes to documenter2.txt.

Python, 54 lines
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
# simplistic state machine for reading an Access Database Documenter text file
# and generating create table sql statements compatible with MySQL
# by Bob Gailer 01/02/06 bgailer@alum.rpi.edu
import re
# accomodate a space as part of fieldname or type e.g. e.g. Expense Type, Long Integer
pat = re.compile(r"\s*(\S* ?\S* ?\S* ?\S*)")
out = file("documenter2.txt", "w")
def newTbl(line):
  global tbl, outLine, lookFor
  newtbl = line.split()[1]
  # could be page header or new table
  if tbl != newtbl:
    tbl = newtbl
    if outLine: out.write(outLine[:-2] + ")\n")
    outLine = "create table %s (" % tbl
    lookFor = "columns"
    return True
# target types taken from MySQL documentation
types = {"text": "varchar",  "byte": "int1",   "date/time": "timestamp", 
         "single": "float",  "integer": "int", "hyperlink": "varchar", "long integer": "int",
         "memo": "longtext", "yes/no": "int1", "currency": "decimal(10,2)"}
outLine = tbl = ""
lookFor = "table:"
specialChars = """ ~@#$%^&*()-=+\\|{};:'",<>/?|"""
replacements = '_'*len(specialChars)
for lineIn in file("documenter.txt"):
  lineIn = lineIn.lower()
  line = lineIn.strip()
  if lookFor == "table:":
    if line.startswith(lookFor):
      if newTbl(line):continue 
  elif lookFor == "columns":
    if line.startswith(lookFor): lookFor = "name"
  elif lookFor == "name":
    if line.startswith(lookFor): lookFor = "table indexes, relationships, table:"
  elif lookFor == "table indexes, relationships, table:":
    if line.startswith("table indexes"): lookFor = "next table"
      # future objective: parse indexes to identify primary key and generate create index statements
    if line.startswith("relationships"): lookFor = "table:"
    if line.startswith("table:"):
      if newTbl(line):continue
    if lineIn[:9] == "         " and lineIn[9] != " ": # field name
      print line # diagnostic tool; can drop
      fld, type, length = pat.findall(line)[:3]
      outLine += "%s %s" % (fld.strip().replace(specialChars, replacements), types[type.strip()])
      if type == "text": outLine += "(%s)" % length
      lookFor = "allowzerolength"
  elif lookFor == "allowzerolength":
    if line.startswith(lookFor):
      null = (" not", "")[line.split()[1] == "False"]
      outLine += null + " null, "
      lookFor = "table indexes, relationships, table:"
if outLine: out.write(outLine[:-2] + ")\n")
out.close()

MS Access does not provide a tool for generating create table statements. My project is to create a MySQL database with the same table structures as the Access database. I tried to write a tool in VBA and ran into roadblocks.

Created by Bob Gailer on Mon, 2 Jan 2006 (PSF)
Python recipes (4591)
Bob Gailer's recipes (1)

Required Modules

Other Information and Tasks