Migrating MS Access to other databases using XML

A common problem I have had is to migrate data contained in a Microsoft Access database to other databases or formats.

I have used the Tclodbc extension [1] to generate an XML file describing the Access data. This file can then processed with XSLT to recreate the database on another platform.

The script can export enough information to recreate tables, indexes and data however not enough information is available via ODBC to recreate database views or key constraints. (Note that this approach can also be used to read Excel spreadsheets.)

If anyone has more ideas in this area please let me know.

Regards,

MArk mailto:[email protected]

The following tcl script demonstrates how the XML file is generated:

 ####################
 #
 # Packages
 #
 ####################
 package require tclodbc

 ##
 #
 # Procedures
 #
 ##

 #
 # Certain characters must be encoded in XML
 #
 proc Encode {str} {

    #
    # Standard encodings
    #
    set enc(&)  {&amp;}
    set enc(<)  {&lt;}
    set enc(>)  {&gt;}
    set enc(\") {"}

    #
    # HACK ALERT
    #
    # Single quotes cause problems in SQL INSERT statements
    #
    set enc(') {''}

    return [string map [array get enc] $str]
 }

 #
 # Take an Access file and dump its contents to an XML file
 #
 proc XMLfromAccess {dbFile xmlFile} {

    #
    # Assume that we are converting Access files
    #
    set driver "Microsoft Access Driver (*.mdb)"

    #
    # Connect to db
    #
    database db "DRIVER=$driver;DBQ=$dbFile"

    #
    # Start XML file
    #
    lappend xml "<?xml version=\"1.0\"?>"
    lappend xml "<ODBC file=\"$dbFile\">"

    #
    # Obtain the Schema details
    #
    lappend xml "\t<SCHEMA>"

    foreach tableDef [db tables] {

       set table(TABLE_QUALIFIER) [lindex $tableDef 0]
       set table(TABLE_OWNER)     [lindex $tableDef 1]
       set table(TABLE_NAME)      [lindex $tableDef 2]
       set table(TABLE_TYPE)      [lindex $tableDef 3]
       set table(REMARKS)         [lindex $tableDef 4]

       #
       # Only process normal tables
       #
       if {[string compare $table(TABLE_TYPE) TABLE]} {
          continue
       }

       #
       # TABLE tags start here
       #
       lappend xml "\t\t<TABLE name=\"$table(TABLE_NAME)\">"

       foreach key [array names table] {
          lappend xml "\t\t\t<$key>$table($key)</$key>"
       }

       foreach columnDef [db columns $table(TABLE_NAME)] {

          set column(TABLE_QUALIFIER) [lindex $columnDef 0]
          set column(TABLE_OWNER)     [lindex $columnDef 1]
          set column(TABLE_NAME)      [lindex $columnDef 2]
          set column(COLUMN_NAME)     [lindex $columnDef 3]
          set column(DATA_TYPE)       [lindex $columnDef 4]
          set column(TYPE_NAME)       [lindex $columnDef 5]
          set column(PRECISION)       [lindex $columnDef 6]
          set column(LENGTH)          [lindex $columnDef 7]
          set column(SCALE)           [lindex $columnDef 8]
          set column(RADIX)           [lindex $columnDef 9]
          set column(NULLABLE)        [lindex $columnDef 10]
          set column(REMARKS)         [lindex $columnDef 11]

          #
          # COLUMN tags start here
          #
          lappend xml "\t\t\t<COLUMN name=\"$column(COLUMN_NAME)\">"

          foreach key [array names column] {
             lappend xml "\t\t\t\t<$key>$column($key)</$key>"
          }

          lappend xml "\t\t\t</COLUMN>"
       }
       unset column

       foreach indexDef [db indexes $table(TABLE_NAME)] {

          set index(TABLE_QUALIFIER)  [lindex $indexDef 0]
          set index(TABLE_OWNER)      [lindex $indexDef 1]
          set index(TABLE_NAME)       [lindex $indexDef 2]
          set index(NON_UNIQUE)       [lindex $indexDef 3]
          set index(INDEX_QUALIFIER)  [lindex $indexDef 4]
          set index(INDEX_NAME)       [lindex $indexDef 5]
          set index(TYPE)             [lindex $indexDef 6]
          set index(SEQ_IN_INDEX)     [lindex $indexDef 7]
          set index(COLUMN_NAME)      [lindex $indexDef 8]
          set index(COLLATION)        [lindex $indexDef 9]
          set index(CARDINALITY)      [lindex $indexDef 10]
          set index(PAGES)            [lindex $indexDef 11]
          set index(FILTER_CONDITION) [lindex $indexDef 12]

          #
          # INDEX tags
          #
          lappend xml "\t\t\t<INDEX name=\"$index(INDEX_NAME)\">"

          foreach key [array names index] {
             lappend xml "\t\t\t\t<$key>$index($key)</$key>"
          }

          lappend xml "\t\t\t</INDEX>"
       }
       unset index
       lappend xml "\t\t</TABLE>"
    }
    unset table
    lappend xml "\t</SCHEMA>"

    #
    # Obtain the Table data
    #
    lappend xml "\t<DATA>"

    foreach tableDef [db tables] {

       set table(TABLE_QUALIFIER) [lindex $tableDef 0]
       set table(TABLE_OWNER)     [lindex $tableDef 1]
       set table(TABLE_NAME)      [lindex $tableDef 2]
       set table(TABLE_TYPE)      [lindex $tableDef 3]
       set table(REMARKS)         [lindex $tableDef 4]

       #
       # Only process normal tables
       #
       if {[string compare $table(TABLE_TYPE) TABLE]} {
          continue
       }

       #
       # Execute SELECT
       #
       db statement data "SELECT * FROM $table(TABLE_NAME)"
       data execute

       #
       # Save column specification
       #
       foreach colSpec [db columns $table(TABLE_NAME)] {
          lappend typeList [lindex $colSpec 3]
          lappend typeList [lindex $colSpec 5]
       }
       array set type $typeList

       #
       # Fetch each row
       #
       lappend xml "\t\t<TABLE name=\"$table(TABLE_NAME)\">"

       while {[data fetch row]} {
          lappend xml "\t\t\t<ROW>"

          foreach key [array names row] {
             lappend xml "\t\t\t\t<COLUMN name=\"$key\" type=\"$type($key)\">[Encode $row($key)]</COLUMN>"
          }

          lappend xml "\t\t\t</ROW>"
       }
       unset row

       lappend xml "\t\t</TABLE>"
    }

    lappend xml "\t</DATA>"
    lappend xml "</ODBC>"

    #
    # Write the XML file
    #

    set fp [open $xmlFile w]
    puts $fp [join $xml "\n"]
    close $fp

    #
    # Cleanup
    #
    db disconnect
 }

 ##
 #
 # Main Program
 #
 ##
 foreach file [glob *.mdb] {
    XMLfromAccess $file [file rootname $file].xml
 }

The following example XSLT stylesheet shows how the tables and indexes can be created and the data loaded.

 <?xml version="1.0" encoding="utf-8"?>
 <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

 <xsl:output method="text"/>

 <!--
 Match the whole XML document
 -->
 <xsl:template match="/">
   <xsl:apply-templates select="//SCHEMA/TABLE"/>
   <xsl:apply-templates select="//DATA/TABLE/ROW"/>
 </xsl:template>

 <!--
 CREATE TABLE ..  and call the INDEX template
 -->
 <xsl:template match="TABLE">
   CREATE TABLE <xsl:value-of select="@name"/> (<xsl:apply-templates select="COLUMN"  mode="schema_table"/>);
   <!--
   Create all indexes associated with a table
   -->
   <xsl:apply-templates select="INDEX"/>
 </xsl:template>

 <!--
 CREATE TABLE .. (?)
 -->
 <xsl:template match="COLUMN" mode="schema_table">
   <xsl:value-of select="@name"/>
   <xsl:text> </xsl:text>
   <!--
   Type matchings
   -->
   <xsl:choose>
     <xsl:when test="TYPE_NAME='COUNTER'">INTEGER</xsl:when>
     <xsl:otherwise><xsl:value-of select="TYPE_NAME"/></xsl:otherwise>
   </xsl:choose>
   <xsl:if test="position()!=last()">,</xsl:if>
 </xsl:template>

 <!--
 CREATE INDEX ..
 -->
 <xsl:template match="INDEX">
   <xsl:if test="string-length(@name)&gt;0">
   CREATE <xsl:choose><xsl:when test="NON_UNIQUE=0">UNIQUE</xsl:when></xsl:choose> INDEX <xsl:value-of  select="INDEX_NAME"/> ON <xsl:value-of select="TABLE_NAME"/> (<xsl:value-of select="COLUMN_NAME"/>);
   </xsl:if>
 </xsl:template>

 <!--
 INSERT INTO ..
 -->
 <xsl:template match="ROW">
   INSERT INTO <xsl:value-of select="../@name"/> (<xsl:apply-templates select="COLUMN"  mode="data_table_row1"/>) VALUES (<xsl:apply-templates select="COLUMN" mode="data_table_row2"/>);
 </xsl:template>

 <!--
 INSERT INTO .. (?)
 -->
 <xsl:template match="COLUMN" mode="data_table_row1">
   <xsl:value-of select="@name"/>
   <xsl:if test="position()!=last()">,</xsl:if>
 </xsl:template>

 <!--
 INSERT INTO .. (..) VALUES (?)
 -->
 <xsl:template match="COLUMN" mode="data_table_row2">
   <!--
   Some types need to be quoted
   -->
   <xsl:choose>
     <xsl:when test="@type='CHAR'">'<xsl:value-of select="."/>'</xsl:when>
     <xsl:when test="@type='VARCHAR'">'<xsl:value-of select="."/>'</xsl:when>
     <xsl:otherwise><xsl:value-of select="."/></xsl:otherwise>
   </xsl:choose>
   <xsl:if test="position()!=last()">,</xsl:if>
 </xsl:template>

 </xsl:stylesheet>

[ Category Database | Category XML | ]