PHP实现TXT转CSV并插入数据库的技术实现咨询

Alright, let's get this TXT-to-CSV-to-database script sorted out. Your original code has some rough edges—like messy whitespace handling, no SQL injection protection, missing CSV generation, and basic upload safety gaps. Here's a complete, polished version that fixes all those issues and works reliably:

Full Functional Script #

<?php
// Database configuration - update these values to match your setup!
$dbHost = 'localhost';
$dbName = 'your_database';
$dbUser = 'your_username';
$dbPass = 'your_password';

// Upload directory setup (creates folder if it doesn't exist)
$uploadDir = 'attandance_file/';
if (!is_dir($uploadDir)) {
    mkdir($uploadDir, 0755, true);
}

if (isset($_POST['submit'])) {
    // Basic upload validation
    if (!isset($_FILES['field']) || $_FILES['field']['error'] !== UPLOAD_ERR_OK) {
        die("Oops: No file uploaded or upload failed.");
    }

    $fileInfo = pathinfo($_FILES['field']['name']);
    // Restrict to only TXT files
    if (strtolower($fileInfo['extension']) !== 'txt') {
        die("Oops: Only TXT files are allowed for upload.");
    }

    // Use unique filename to avoid overwriting existing files
    $fileName = uniqid() . '_' . $_FILES['field']['name'];
    $uploadPath = $uploadDir . $fileName;
    $csvPath = $uploadDir . str_replace('.txt', '.csv', $fileName);

    // Move uploaded file to our target directory
    if (!move_uploaded_file($_FILES['field']['tmp_name'], $uploadPath)) {
        die("Oops: Failed to save the uploaded file.");
    }

    // Initialize secure database connection with PDO
    try {
        $pdo = new PDO("mysql:host=$dbHost;dbname=$dbName;charset=utf8mb4", $dbUser, $dbPass);
        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        // Prepare SQL insert statement (update columns to match your `attandance` table!)
        $insertStmt = $pdo->prepare("INSERT INTO attandance (No, TMNo, Name, AttendanceStatus) VALUES (?, ?, ?, ?)");

        // Open TXT file for reading and CSV file for writing
        $txtFile = fopen($uploadPath, 'rt');
        $csvFile = fopen($csvPath, 'w');

        // Uncomment this line if your TXT has a header row you want to skip
        // fgets($txtFile);

        while (($line = fgets($txtFile)) !== false) {
            $line = trim($line);
            // Skip empty lines to avoid junk data
            if (empty($line)) continue;

            // Split line by any number of spaces/tabs (handles messy formatting)
            $rowData = preg_split('/\s+/', $line);

            // Skip rows that don't have enough columns (adjust count to match your TXT structure)
            if (count($rowData) < 4) {
                error_log("Skipped invalid row: $line");
                continue;
            }

            // Extract data from the split row (adjust indices based on your TXT layout)
            $no = $rowData[0];
            $tmNo = $rowData[1];
            $name = $rowData[2];
            $attendanceStatus = $rowData[3];

            // Insert into database using prepared statement (prevents SQL injection)
            $insertStmt->execute([$no, $tmNo, $name, $attendanceStatus]);

            // Write the same row to our CSV file
            fputcsv($csvFile, $rowData);
        }

        // Clean up file handles
        fclose($txtFile);
        fclose($csvFile);

        echo "Success! Data parsed, CSV generated, and records added to the database.";
        echo "<br>CSV file saved at: " . $csvPath;

    } catch (PDOException $e) {
        die("Database error: " . $e->getMessage());
    } catch (Exception $e) {
        die("Error: " . $e->getMessage());
    }
}
?>

<!-- Simple HTML upload form -->
<form method="post" enctype="multipart/form-data">
    <input type="file" name="field" accept=".txt" required>
    <button type="submit" name="submit">Upload & Process</button>
</form>

Key Fixes & Customization Tips #

• Upload Safety: Checks for valid TXT files, uses unique filenames to avoid overwrites, and creates the upload directory automatically.
• Robust Parsing: Uses preg_split('/\s+/', $line) to handle any number of spaces or tabs as separators, skips empty/invalid rows.
• Security: Uses PDO prepared statements to eliminate SQL injection risks (never concatenate user input into SQL queries!).
• CSV Generation: Automatically creates a CSV version of your TXT data alongside processing.
• Must-Do Customizations:
  • Update the database credentials at the top of the script.
  • Modify the SQL insert columns and $rowData indices to match your actual TXT file structure (add/remove columns as needed).
  • Uncomment the header skip line if your TXT file has a header row that shouldn't be inserted into the database.

内容的提问来源于stack exchange，提问作者omkara