Automated CSV and Excel Data Import to MySQL and PostgreSQL Databases Using PHP

To automate data transfer from a CSV or Excel file to both a MySQL and PostgreSQL database using PHP, follow these steps:

Prerequisites

1. Install necessary libraries:

   • PHP's PDO extension for MySQL and PostgreSQL.
   • PHPExcel library (or PhpSpreadsheet if available, but we'll use PHPExcel as it's more compatible with PHP 5.6).

2. Download the PHPExcel library and include it in your project directory.

---

Step 1: Set Up Database Connections

We'll use PDO to connect to both MySQL and PostgreSQL.

<?php
// MySQL connection
$mysqlHost = 'localhost';
$mysqlDB = 'mysql_database';
$mysqlUser = 'mysql_user';
$mysqlPassword = 'mysql_password';

try {
    $mysqlConnection = new PDO("mysql:host=$mysqlHost;dbname=$mysqlDB", $mysqlUser, $mysqlPassword);
    $mysqlConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connected to MySQL successfully.<br>";
} catch (PDOException $e) {
    die("MySQL connection failed: " . $e->getMessage());
}

// PostgreSQL connection
$pgHost = 'localhost';
$pgDB = 'pgsql_database';
$pgUser = 'pgsql_user';
$pgPassword = 'pgsql_password';

try {
    $pgConnection = new PDO("pgsql:host=$pgHost;dbname=$pgDB", $pgUser, $pgPassword);
    $pgConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connected to PostgreSQL successfully.<br>";
} catch (PDOException $e) {
    die("PostgreSQL connection failed: " . $e->getMessage());
}
?>

---

Step 2: Load Data from CSV or Excel File

We will create a function that reads either a CSV or Excel file and returns the data as an array.

<?php
require 'path/to/PHPExcel.php';

function readFileData($filePath) {
    $fileType = strtolower(pathinfo($filePath, PATHINFO_EXTENSION));

    if ($fileType === 'csv') {
        $data = [];
        if (($handle = fopen($filePath, 'r')) !== false) {
            while (($row = fgetcsv($handle, 1000, ',')) !== false) {
                $data[] = $row;
            }
            fclose($handle);
        }
        return $data;
    } elseif ($fileType === 'xls' || $fileType === 'xlsx') {
        $data = [];
        $excel = PHPExcel_IOFactory::load($filePath);
        $sheet = $excel->getActiveSheet();
        foreach ($sheet->getRowIterator() as $row) {
            $rowData = [];
            $cellIterator = $row->getCellIterator();
            $cellIterator->setIterateOnlyExistingCells(false);
            foreach ($cellIterator as $cell) {
                $rowData[] = $cell->getValue();
            }
            $data[] = $rowData;
        }
        return $data;
    } else {
        throw new Exception("Unsupported file format");
    }
}
?>

---

Step 3: Transfer Data to MySQL and PostgreSQL

Define functions to insert data into both MySQL and PostgreSQL. This example assumes the data is an array of arrays, where each inner array represents a row in the database.

<?php
function insertIntoMySQL($mysqlConnection, $data) {
    $query = "INSERT INTO your_mysql_table (column1, column2, column3) VALUES (?, ?, ?)";
    $stmt = $mysqlConnection->prepare($query);
    foreach ($data as $row) {
        $stmt->execute($row);
    }
    echo "Data inserted into MySQL successfully.<br>";
}

function insertIntoPostgreSQL($pgConnection, $data) {
    $query = "INSERT INTO your_pg_table (column1, column2, column3) VALUES (?, ?, ?)";
    $stmt = $pgConnection->prepare($query);
    foreach ($data as $row) {
        $stmt->execute($row);
    }
    echo "Data inserted into PostgreSQL successfully.<br>";
}
?>

---

Step 4: Putting It All Together

Load data from the file, then pass it to each function to insert into both MySQL and PostgreSQL.

<?php
$filePath = 'path/to/yourfile.csv'; // or .xls / .xlsx
try {
    $data = readFileData($filePath);
    insertIntoMySQL($mysqlConnection, $data);
    insertIntoPostgreSQL($pgConnection, $data);
} catch (Exception $e) {
    echo "Error: " . $e->getMessage();
}
?>

---

Example Execution

1. Make sure the MySQL and PostgreSQL databases and tables (your_mysql_table, your_pg_table) are set up and have the correct columns (column1, column2, column3).
2. Place your CSV or Excel file in the specified path ($filePath).
3. Run this PHP script from the command line or a browser (if on a web server).

This script will read the data from the specified file and insert it into both databases.

Connect with me:@ LinkedIn and checkout my Portfolio.

Please give my GitHub Projects a star ⭐️