|
A script to export an acces database to mysql.
<?
set_time_limit(1200);
$dbq = '/path/to/mdbfile.mdb';
$dsn = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=$dbq";
$connectionType = "ADODB.Connection";
$db_connection = odbc_connect($dsn,$connectionType,"");
//connect to your mysql database
exit('You must connect to your mysql database here: please have a look in the code');
$r = odbc_tables($db_connection);
$tables = array();
while($object = odbc_fetch_object($r))
{
if($object->TABLE_TYPE=='TABLE')
{
$tables[]=$object->TABLE_NAME;
}
}
foreach($tables as $k => $v)
{
exportTable($v);
}
function exportTable($tableName)
{
global $db_connection;
$time1 = microtime(true);
echo $tableName.' import started.<br/>';
$sql = "DROP TABLE `".addslashes($tableName)."`";
mysql_query($sql);
$sql = "SELECT * FROM ".$tableName;
$r = odbc_exec($db_connection,$sql);
$first = true;
while($object = odbc_fetch_object($r))
{
if($first)
{
$columns = createMysqlTable($tableName,$object,$r);
$columnNames = array_keys($columns);
foreach($columnNames as $k => $v)
{
$columnNames[$k]=strtolower($v);
}
}
$first = false;
foreach($columns as $k => $v)
{
$columns[$k] = addslashes($object->$k);
}
$sql = "INSERT INTO `".$tableName."` (`".implode('`, `',$columnNames)."`) VALUES ('".implode("', '",$columns)."')";
mysql_query($sql);
}
$total = number_format(microtime(true)-$time1,3);
echo $tableName.' exported: '.$total.' S<br/>';
}
exit('done');
function createMysqlTable($tableName,$object,$r)
{
$parts = array();
$i = 1;
foreach($object as $k => $v)
{
switch(strtolower(odbc_field_type($r,$i)))
{
case 'varchar':
case 'counter':
case 'double':
case 'integer':
case 'real':
$parts[odbc_field_name($r,$i)]=array('type'=>'varchar','length'=>'255');
$sColumns[(odbc_field_name($r,$i))]=true;
break;
case 'longchar':
$parts[odbc_field_name($r,$i)]=array('type'=>'longtext','length'=>'255');
$sColumns[(odbc_field_name($r,$i))]=true;
break;
case 'bit':
$parts[odbc_field_name($r,$i)]=array('type'=>'tinyint','length'=>'1');
$sColumns[(odbc_field_name($r,$i))]=true;
break;
default:
exit($tableName.'.'.odbc_field_name($r,$i).': '.odbc_field_type($r,$i).' unknown column type.');
}
$i++;
}
$columns = $parts;
$parts = array();
$sql = "CREATE TABLE `".$tableName."`(";
foreach($columns as $k => $v)
{
if($v['type']=='longchar')
{
$parts[] = "`".strtolower($k)."` ".$v['type']." NOT NULL";
}
else
{
$parts[] = "`".strtolower($k)."` ".$v['type']."(".$v['length'].") NOT NULL default ''";
}
}
$sql .= implode(",\n",$parts);
$sql .= ") type = MYISAM";
mysql_query($sql);
return $sColumns;
}
?>
Sign up to add your own comment here!
|
|
Add to favourites
Subscribe comments
Copy code
Mail user
Add to friends