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?

see this : bullzip.com/products/a2m/info.php to convert and import the mdb files — Bernd Buffen
– Bernd Buffen, Commented Dec 26, 2016 at 9:37

Parfait · Accepted Answer · 2016-12-26 18:42:08Z

3

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):

php_pdo.dll extension enabled in .ini file
MS Access ODBC Driver (usually already pre-installed)
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;

edited Dec 26, 2016 at 18:42

answered Dec 26, 2016 at 18:37

Parfait

108k19 gold badges103 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Madhura Over a year ago

Yes I have tried above one, it working with above logic, but finally i have used COM.

Madhura Over a year ago

Below is the logic i have used which is also working fine: $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");

Parfait Over a year ago

Indeed, same connection driver (remove accdb option) but different API -PHP's PDO vs Windows' ADO. Just thought using PHP's object library may provide seamless setup especially with MySQL connection in script. But to each his/her own. Happy coding!

Gord Thompson Over a year ago

( cc: @Madhura ) - In my experience, neither PDO nor PHP's (deprecated) odbc_ functions are able to use Access ODBC to retrieve Unicode characters that do not also exist in the Windows-1252 character set. Using COM with ADO has been the only way I could find to properly retrieve those characters. (Details in the related answer here.)

Parfait Over a year ago

Very interesting @GordThompson! Thank you for your comment. We should send a note to the PHP team on this PDO item. I wonder if the newly release PHP 7 resolved this unicode issue.

Madhura · Accepted Answer · 2016-12-27 09:22:30Z

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");
     }

answered Dec 27, 2016 at 9:22

Madhura

1671 gold badge2 silver badges15 bronze badges

Comments

JaNaM SoNi · Accepted Answer · 2016-12-26 09:42:01Z

0

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

edited Dec 26, 2016 at 9:42

answered Dec 26, 2016 at 9:35

JaNaM SoNi

774 bronze badges

1 Comment

Madhura Over a year ago

Thank You so much JaNaM.

Didarul Alam · Accepted Answer · 2016-12-26 16:17:55Z

0

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

edited Dec 26, 2016 at 16:17

answered Dec 26, 2016 at 10:40

Didarul Alam

792 silver badges18 bronze badges

2 Comments

Madhura Over a year ago

how to convert mdb file into CSV file using PHP code.

Didarul Alam Over a year ago

Please notice above, There you have your answer. (Wishing only well)

Collectives™ on Stack Overflow

Import Access data into MySQL using PHP

4 Answers 4

5 Comments

Comments

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

5 Comments

Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related