Consume .mdb file and write to MySQL with PHP

Navigation
0

I have an .mdb file, and I need to inject all its data into MySQL using PHP code. What I do is the following: 

// Executa comando via shell para upload do banco no mysql          
$scriptEsquema = shell_exec('mdb-schema uploads/arquivo.mdb mysql | grep -v ^DROP | mysql -u usuario meubancodedados');
$scriptInsert = shell_exec('mdb-export -D "%Y-%m-%d %H:%M:%S" -H -I mysql uploads/arquivo.mdb MINHATABELA | mysql -u usuario meubancodedados');
  • On my computer it works quietly, but when it goes to a server it does not work anymore.

Would there be another way to do this with PHP? Or something I should do on the server?

    
asked by anonymous 01.07.2016 / 04:23

2 answers

0

One way to do this is to make a connection directly to the file: 

$db = 'uploads/arquivo.mdb';
$conn_mdb = new COM('ADODB.Connection');
$conn_mdb->Open("DRIVER={Driver do Microsoft Access (*.mdb)}; DBQ=$db");
if (!$conn_mdb) {
   echo "Xiii, faiô a conexão do MDB! ";
   die();
}
$sql = 'SELECT campo1, campo2 FROM tabela';
$res = $conn_mdb->Execute($sql);

$conn_mysqli=mysqli_connect("localhost","my_user","my_password","my_db");
if (mysqli_connect_errno()) {
   echo "Xiii, faiô a conexão do MySQL: " . mysqli_connect_error();
   die();
}

while (!$res->EOF) {
     mysqli_query($conn_mysqli,"INSERT INTO tabela (campo1, campo2)
                                VALUES ('".$res->Fields['campo1']->Value."',
                                        '".$res->Fields['campo2']->Value."')");
    $res->MoveNext();
}

Here you have more about this type of connection.

If your system is Linux, it is best to use a tool called MDBTOOLS .

By the way, if you only want to export the data from your mdb to the MySQL database, the program itself Microsoft Access has an option to export data in Excel spreadsheet (CSV) format whose format is compatible with MySQL.

For Windows, there are also other options for exporting mdb files directly to MySQL. For example, MySQL Front .

    
01.07.2016 / 21:45
0

If I'm not mistaken, this "shell_exec" command is usually blocked by security on the production server, especially in shared hosting.

I suggest three things:

1 / Check if your hosting releases this command to run on the server.

2 / Export the data from this DB to CSV, create routine in PHP to read this CSV and then load the MySQL BD.

3 / If your hosting is dedicated, then upload this data via SSH, although the chance of releasing the shell_exec command will be greater because the server will be dedicated.

So that's it. I hope it helps in some way the answers.

    
01.07.2016 / 18:28
Change button behavior according to URL Gem not available