Handle Access MDB files import with PHP on Linux

I’ve used handle in the title because you cannot actually manage Access MDB tables from within PHP itself, and I didn’t want to add a misleading title just to attract people…

What you can do is import the data, and not even from PHP itself but by using external tools.

I’m showing the most I could achieve to import an Access file to MySQL, and then handle the data in there. Of course this way you cannot write data back to Access. And I hope it’s the goal of everyone else: who as of today is still relying on Access to store data?

Anyway, the only way I managed to get the data out of Access is using mdbtools. They’re some open source tools to get data from mdb files, so through exec‘s php function you can call them and obtain the informations.

I’m showing off what I did for importing some tables of an Access file to temporary MySQL tables, in order to do some manipulations later. You can adjust this example if you want to just keep the tables as is, without furhter modifications. The example is based on Yii2, but the commands are pretty alike PHP’s PDO database calls, so it won’t be hard to adjust to your needs.

Here’s a full example of the tweaks I had to do to import a not-properly-formed old Access file:

// Your MDB file
$filemdb = "/path/to/your/file.mdb";
// I prefix Access imported tables with "import_", not to mess with possibly existing tables
$imported_table_prefix = "import_";

// Check that mdbtools are installed
$this->_checkAccessRequirements();

// Select the specific tables to import. If you want to list ALL the tables you could do something like 
// `mdb-tables -1 $filemdb` 
// or use a different separator with `-d`
// @see https://linux.die.net/man/1/mdb-tables
$tables = ['table1', 'table2', 'sadly I had a table with spaces'];
foreach ($tables as $t) {
    // MySQL table name - Replace spaces into table names with underscores
    $t_mysql = str_replace(" ", "_", $t);
    // Remove existing table - up to your needs
    Yii::$app->db->createCommand("DROP TABLE IF EXISTS {$imported_table_prefix}{$t_mysql}")->execute();

    // Obtain table schema, in MySQL format
    $schema = shell_exec("mdb-schema -T '{$t}' {$filemdb} mysql");
    // Make a temporary table instead of a standard one - you may want to comment this out
    $schema = str_replace("CREATE TABLE", "CREATE TEMPORARY TABLE", $schema);
    // As said before prefix the tables
    $schema = str_replace("`{$t}`", "`{$imported_table_prefix}{$t_mysql}`", $schema);
    // Create the import table with schema
    Yii::$app->db->createCommand($schema)->execute();

    // Fetch the data of our table, formatting date in MySQL Y-m-d H:i:s way
    $insert = shell_exec("mdb-export -D '%Y-%m-%d %H:%M:%S' -I mysql {$filemdb} '{$t}' ");
    // Again the prefix
    $insert = str_replace("`{$t}`", "`{$imported_table_prefix}{$t_mysql}`", $insert);
    // Remove existing ^M characters
    $insert = str_ireplace("\x0D", "", $insert);
    // Escape ? single char with ??, because PDO may think it's a placeholder for parameters
    $insert = str_replace("?", "??", $insert);
    // Prefix any / char which may be in front of a single or double quote
    $insert = str_replace("\\", "\\\\", $insert);
    // Remove newlines which may exist into textfields 
    $insert = preg_replace('/\n(?=.*?")/ims','',$insert);
    // Finally execute the inserts extracted from mdb
    Yii::$app->db->createCommand($insert)->execute();
}

 

Lascia un commento

Il tuo indirizzo email non sarà pubblicato. I campi obbligatori sono contrassegnati *

Solve : *
20 × 22 =


Questo sito usa Akismet per ridurre lo spam. Scopri come i tuoi dati vengono elaborati.