DEV Community

MD ARIFUL HAQUE
MD ARIFUL HAQUE

Posted on

2 1 1 1 1

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());
}
?>
Enter fullscreen mode Exit fullscreen mode

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");
    }
}
?>
Enter fullscreen mode Exit fullscreen mode

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>";
}
?>
Enter fullscreen mode Exit fullscreen mode

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();
}
?>
Enter fullscreen mode Exit fullscreen mode

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 ⭐️

Image of AssemblyAI

Automatic Speech Recognition with AssemblyAI

Experience near-human accuracy, low-latency performance, and advanced Speech AI capabilities with AssemblyAI's Speech-to-Text API. Sign up today and get $50 in API credit. No credit card required.

Try the API

Top comments (0)

👋 Kindness is contagious

Engage with a sea of insights in this enlightening article, highly esteemed within the encouraging DEV Community. Programmers of every skill level are invited to participate and enrich our shared knowledge.

A simple "thank you" can uplift someone's spirits. Express your appreciation in the comments section!

On DEV, sharing knowledge smooths our journey and strengthens our community bonds. Found this useful? A brief thank you to the author can mean a lot.

Okay