PHP 自动导入 CSV/Excel 数据到 MySQL/PostgreSQL 数据库

作为 IT 工程师，我将为您提供一个完整的解决方案，使用 PHP 自动将 CSV 和 Excel 文件数据导入到 MySQL 和 PostgreSQL 数据库中。

解决方案概述

1. 文件上传处理 - 接收用户上传的 CSV/Excel 文件
2. 文件解析 - 使用 PHP 库解析文件内容
3. 数据库连接 - 建立与 MySQL/PostgreSQL 的连接
4. 数据导入 - 将解析后的数据批量插入数据库
5. 错误处理 - 处理可能出现的各种异常情况

实现代码

1. 安装必要的 PHP 库

首先确保安装了以下 PHP 扩展： - PDO (用于数据库连接) - pdo_mysql (MySQL 驱动) - pdo_pgsql (PostgreSQL 驱动) - php_fileinfo (文件类型检测)

对于 Excel 文件处理，推荐使用 PhpSpreadsheet 库：

composer require phpoffice/phpspreadsheet

2. 完整实现代码

<?php
require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\IOFactory;

class DataImporter {
    private $db;
    private $dbType;

    public function __construct($dbType, $host, $dbname, $username, $password) {
        $this->dbType = strtolower($dbType);

        try {
            if ($this->dbType === 'mysql') {
                $this->db = new PDO("mysql:host=$host;dbname=$dbname;charset=utf8", $username, $password);
            } elseif ($this->dbType === 'postgresql') {
                $this->db = new PDO("pgsql:host=$host;dbname=$dbname", $username, $password);
            } else {
                throw new Exception("Unsupported database type: $dbType");
            }

            $this->db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        } catch (PDOException $e) {
            die("Database connection failed: " . $e->getMessage());
        }
    }

    public function importFile($filePath, $tableName, $hasHeader = true) {
        $fileType = $this->detectFileType($filePath);

        try {
            $data = $this->parseFile($filePath, $fileType, $hasHeader);

            if (empty($data)) {
                throw new Exception("No data found in the file");
            }

            $this->importToDatabase($tableName, $data, $hasHeader);

            return ['success' => true, 'message' => 'Data imported successfully', 'count' => count($data)];
        } catch (Exception $e) {
            return ['success' => false, 'message' => $e->getMessage()];
        }
    }

    private function detectFileType($filePath) {
        $finfo = finfo_open(FILEINFO_MIME_TYPE);
        $mime = finfo_file($finfo, $filePath);
        finfo_close($finfo);

        switch ($mime) {
            case 'text/plain':
            case 'text/csv':
                return 'csv';
            case 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet':
                return 'xlsx';
            case 'application/vnd.ms-excel':
                return 'xls';
            default:
                throw new Exception("Unsupported file type: $mime");
        }
    }

    private function parseFile($filePath, $fileType, $hasHeader) {
        $data = [];

        if ($fileType === 'csv') {
            $handle = fopen($filePath, 'r');
            if (!$handle) {
                throw new Exception("Failed to open CSV file");
            }

            if ($hasHeader) {
                $headers = fgetcsv($handle);
            }

            while (($row = fgetcsv($handle)) !== false) {
                if ($hasHeader) {
                    $data[] = array_combine($headers, $row);
                } else {
                    $data[] = $row;
                }
            }

            fclose($handle);
        } else {
            // Handle Excel files
            $spreadsheet = IOFactory::load($filePath);
            $sheet = $spreadsheet->getActiveSheet();
            $rows = $sheet->toArray();

            if ($hasHeader) {
                $headers = array_shift($rows);
                foreach ($rows as $row) {
                    $data[] = array_combine($headers, $row);
                }
            } else {
                $data = $rows;
            }
        }

        return $data;
    }

    private function importToDatabase($tableName, $data, $hasHeader) {
        if (empty($data)) {
            return;
        }

        // Start transaction
        $this->db->beginTransaction();

        try {
            $firstRow = reset($data);

            if ($hasHeader) {
                // Associative array (with headers)
                $columns = array_keys($firstRow);
                $placeholders = implode(', ', array_fill(0, count($columns), '?'));
                $columnsStr = implode(', ', $columns);

                $stmt = $this->db->prepare("INSERT INTO $tableName ($columnsStr) VALUES ($placeholders)");

                foreach ($data as $row) {
                    $values = array_values($row);
                    $stmt->execute($values);
                }
            } else {
                // Numeric array (no headers)
                $columnCount = count($firstRow);
                $placeholders = implode(', ', array_fill(0, $columnCount, '?'));

                $stmt = $this->db->prepare("INSERT INTO $tableName VALUES ($placeholders)");

                foreach ($data as $row) {
                    $stmt->execute($row);
                }
            }

            $this->db->commit();
        } catch (Exception $e) {
            $this->db->rollBack();
            throw $e;
        }
    }
}

// 使用示例
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_FILES['data_file'])) {
    $uploadDir = 'uploads/';
    if (!file_exists($uploadDir)) {
        mkdir($uploadDir, 0777, true);
    }

    $fileName = basename($_FILES['data_file']['name']);
    $filePath = $uploadDir . $fileName;

    if (move_uploaded_file($_FILES['data_file']['tmp_name'], $filePath)) {
        try {
            // 配置数据库连接 - 根据需要修改
            $dbType = 'mysql'; // 或 'postgresql'
            $host = 'localhost';
            $dbname = 'your_database';
            $username = 'your_username';
            $password = 'your_password';

            $importer = new DataImporter($dbType, $host, $dbname, $username, $password);

            // 执行导入
            $result = $importer->importFile($filePath, 'your_table_name', true);

            if ($result['success']) {
                echo "Successfully imported {$result['count']} records.";
            } else {
                echo "Error: " . $result['message'];
            }
        } catch (Exception $e) {
            echo "Error: " . $e->getMessage();
        }

        // 清理上传的文件
        unlink($filePath);
    } else {
        echo "Failed to upload file.";
    }
}
?>

<!-- 简单的文件上传表单 -->
<!DOCTYPE html>
<html>
<head>
    <title>Data Importer</title>
</head>
<body>
    <h1>Import CSV/Excel to Database</h1>
    <form method="post" enctype="multipart/form-data">
        <input type="file" name="data_file" required>
        <button type="submit">Import</button>
    </form>
</body>
</html>

安全注意事项

1. 文件上传安全：

   • 验证文件类型
   • 限制文件大小
   • 将上传文件存储在非公开目录
   • 使用随机生成的文件名

2. SQL 注入防护：

   • 使用 PDO 预处理语句
   • 不要直接将用户输入拼接到 SQL 中

3. 数据验证：

   • 验证导入数据的格式和内容
   • 处理可能的编码问题

高级功能扩展

1. 批量插入优化：

   • 对于大量数据，可以使用批量插入语句
   • MySQL: INSERT INTO table VALUES (...), (...), ...
   • PostgreSQL: 使用 COPY 命令

2. 数据转换：

   • 在导入前对数据进行清洗和转换
   • 处理日期格式、空值等

3. 进度显示：

   • 对于大文件，实现进度显示功能
   • 可以使用 AJAX 轮询或 WebSocket

4. 日志记录：

   • 记录导入操作和结果
   • 记录错误和异常

这个解决方案提供了从文件上传到数据库导入的完整流程，可以根据具体需求进行进一步定制和扩展。