Msaccess2rdbms

Transferring MS Access data to Other Database Servers

This document explains how to export data from an MS Access database to an XML file, and then how to import the XML into another database vendor's product, eg into MySQL or Postgres.

Exporting from MS Access

Set up a DSN (Data Source Name) which points to your MS Access database. Let's call this DSN 'old_data'.

Now you write a small program which outputs 2 files:

• old_data.xml

  This is the output from DBIx::Admin::BackupRestore V 1.07 which holds all the data from the MS Access database.

  This file will be input to sub restore in DBIx::Admin::BackupRestore.

• old_data.schema

  This is the (optional) output from the same module, which holds a list of table names and column names per table.

  This file will be input to sub new in DBIx::Admin::CreateTrivialSchema.

Code:

	#!/usr/bin/perl
	#
	# Name:
	#	msaccess2xml.pl.

	use strict;
	use warnings;

	use Carp;
	use DBI;
	use DBIx::Admin::BackupRestore;

	# -----------------------------------------------

	my($dbh) = DBI -> connect
	(
		'dbi:ODBC:old_data', '', '',
		{
			FetchHashKeyName   => 'NAME_lc', # Affects skip_tables etc below.
			PrintError         => 0,
			RaiseError         => 1,
			ShowErrorStatement => 1,
		}
	);
	my($backup) = DBIx::Admin::BackupRestore -> new
	(
		clean          => 1,
		croak_on_error => 0,
		dbh            => $dbh,
		odbc           => 1,
		rename_columns =>
		{
			order => 'orders', # 'order' is a reserved word in MySQL.
		},
		rename_tables =>
		{
			'off site' => 'off_site',
		},
		skip_tables =>
		[
			'yukky_name_&_funny_char',
			qw/msysaces
				msysaccessobjects
				msyscolumns
				msysimexcolumns
				msysimexspecs
				msysindexes
				msysmacros
				msysmodules2
				msysmodules
				msysobjects
				msysqueries
				msysrelationships
			/
		],
		verbose => 1
	);

	my($file_name) = 'old_data.xml';
	open(OUT, "> $file_name") || die("Can't open(> $file_name): $!");
	print OUT $backup -> backup('old_data');
	close OUT;

	$file_name  = 'old_data.schema';
	my($column) = $backup -> get_column_names();

	open(OUT, "> $file_name") || Carp::croak("Can't open(> $file_name): $!");

	my($table_name);

	for $table_name (sort keys %$column)
	{
		print OUT join(', ', $table_name, @{$$column{$table_name} }), "\n";
	}

	close OUT;

	print "Wrote: $file_name. \n";

Use ftp or scp to get old_data.xml and old_data.schema across to the other server.

Importing XML into Another RDBMS

Firstly, create a trivial schema to hold the data.

Code:

	#!/usr/bin/perl

	use strict;
	use warnings;

	use Carp;
	use DBI;
	use DBIx::Admin::CreateTrivialSchema;

	# ----------------------------

	my($input_file_name) = 'old_data.schema';

	open(INX, $input_file_name) || Carp::croak("Can't open($input_file_name): $!");
	my(@line) = <INX>;
	close INX;
	chomp @line;

	my(@field, %schema);

	for (@line)
	{
		@field             = split(/, /, $_);
		$schema{$field[0]} = [@field[1 .. $#field] ];
	}

	my($new_dbh) = DBI -> connect
	(
		'dbi:Pg:dbname=new_data', 'postgres', '',
		{
			PrintError         => 0,
			RaiseError         => 1,
			ShowErrorStatement => 1,
		}
	);

	DBIx::Admin::CreateTrivialSchema -> new(dbh => $new_dbh, not_null => 1, schema => \%schema, verbose => 1);

Then, import the XML.

Code:

	#!/usr/bin/perl
	#
	# Name:
	#	restore-db.pl.

	use strict;
	use warnings;

	use DBI;
	use DBIx::Admin::BackupRestore;

	# -----------------------------------------------

	my($file_name) = 'old_data.xml';
	my($dbh)       = DBI -> connect
	(
		'dbi:Pg:dbname=new_data', 'postgres', '',
		{
			AutoCommit         => 1,
			PrintError         => 0,
			RaiseError         => 1,
			ShowErrorStatement => 1,
		}
	);

	DBIx::Admin::BackupRestore -> new
	(
		dbh     => $dbh,
		verbose => 1
	) -> restore($file_name);

Perl Modules

• DBIx::Admin::BackupRestore V 1.07
• DBIx::Admin::CreateTrivialSchema V 1.01

Resources

http://dev.mysql.com/tech-resources/articles/migrating-from-microsoft.html

Author

Ron Savage.

Home page: http://savage.net.au/index.html

This POD was converted to HTML by /Perl.html#fancy-pom2.pl

• Version: 1.01 01-Jun-2006

  This version disguises my email address.

• Version: 1.00 01-Jan-2005

  Original version.

Licence

Australian Copyright © 2002 Ron Savage. All rights reserved.

	All Programs of mine are 'OSI Certified Open Source Software';
	you can redistribute them and/or modify them under the terms of
	The Artistic License, a copy of which is available at:
	http://www.opensource.org/licenses/index.html