Import Access data into MySQL using PHP

Question

How to load .mdb file into mysql database using php code.

I have a .mdb file and location is /example/employee.mdb. I need to read record from attendance table and insert it into mysql's emp_attendance table.

What will be PHP logic for that?

Answer 1

Like most relational databases, PHP can connect to MS Access and MySQL with PDO. Consider doing so with a dual connection with MySQL. No external, third-party software is required. Below example uses a mock table and fields. Adjust as needed:

Requirements include (no MSAccess.exe installation required):

1. php_pdo.dll extension enabled in .ini file
2. MS Access ODBC Driver (usually already pre-installed)
3. Any Windows PC (pre-built with Jet/ACE SQL engine -Access' underlying engine)

PHP Dual PDO Connection

$accdatabase="C:\Path\To\database.accdb";

$host="localhost";
$mydatabase="****";
$username="****";
$password="****";

try { 
  # OPEN BOTH DATABASE CONNECTIONS
  $accConn = new PDO("odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)}; DBq=$accdatabase;Uid=Admin;Pwd=;"); 

  $myConn = new PDO("mysql:host=$host;dbname=$mydatabase",$username,$password); 
  $myConn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

  $sql = "SELECT * FROM emp_attendance"; 
  $accstmt = $accConn->query($sql);
  $accstmt->setFetchMode(PDO::FETCH_ASSOC); 

  // FETCH ROWS FROM MS ACCESS
  while($row = $accstmt->fetch()) { 
    // APPEND TO MYSQL
    $mystmt = $myConn->prepare("INSERT INTO emp_attendance (empid, `date`, status, notes) VALUES (?, ?, ?, ?)");

    # BIND PARAMETERS 
    $mystmt->bindParam(1, $row['empid'], PDO::PARAM_STR, 50); 
    $mystmt->bindParam(2, $row['date'], PDO::PARAM_STR, 50);    
    $mystmt->bindParam(3, $row['status'], PDO::PARAM_STR, 50); 
    $mystmt->bindParam(4, $row['notes'], PDO::PARAM_STR, 50);

    # EXECUTE QUERY
    $mystmt->execute();
  }
} 
catch(PDOException $e) {         
    echo $e->getMessage()."\n"; 
    exit; 
}

// CLOSE CONNECTIONS
$accConn = null;
$myConn = null;

Answer 2

Finally Below logic worked for me.

$fileNm = "C:\Users\abc\Desktop\attendanceData.mdb";
$conn = new COM("ADODB.Connection") or die("ADODB Connection Faild!");
$conn->Open("DRIVER={Microsoft Access Driver (*.mdb)};DBQ=$fileNm");
    if($conn){
        $data = $conn->Execute("SELECT * FROM em_attendance");
     }

Answer 3

read the data from mdb to csv

read from csv and insert into mysql

Or

Try this

http://board.phpbuilder.com/showthread.php?10365863-Convert-mdb-to-mysql

Answer 4

MDB is a database file used by Microsoft Access. MDB is Access’s own format which is based on the Access Jet Database Engine. You can open it by Microsoft Access as it is it's own format, also can open it by Microsoft Excel, If you don't have Microsoft Office you don't need to worry about. MDB files can also be opened by OpenOffice.org, SAS Institute SAS, Wolfram Mathematica, Softpedia MDB Converter¸ and Microsoft Visual Studio 2010. MDB file files can be converted to .TXT or .CSV formats using a MDB Converter. Even if you don't have above all you can download the a free popular tool MDB Viewer Plus (http://download.cnet.com/MDB-Viewer-Plus/3001-10254_4-75285626.html?onid=10254) Read datas and insert into database however you want. Better convert the file mdb to csv and insert into database. It's easier using by PHP. Hope you have your answer

Steps to Convert Microsoft Access Files to CSV Format

• Open the MDB file with Microsoft access to export to a CSV format
• Click on the ‘External Data’ tab
• Select ‘Excel’ from the Export section
• “Select the destination for the data you want to export”
  • Choose an appropriate file name and location
  • Select Excel Workbook as the file format
  • Specify the appropriate export options
  • Hit the ‘OK’ button
• Open the file you just saved using Microsoft Excel
• Select File and ‘Save As’
• Click the drop-down next to ‘Save as type:’
• Change the ‘Save as type’ to ‘CSV (Comma delimited)(*.csv)’
• Choose an appropriate ‘File name’
• ‘Save’ to your desktop

Hope your problem will be solved.Thanks