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
Download
Copy to clipboard1 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.
Tags: database
