PHP script to import csv data into mysql

PHP script to import csv data into mysql

  1. Blog
  2. 15 November 2026
  3. 3 min read

Stefan Bogdanescu

Founder & Senior Architect

Migrating Legacy CSV Imports in PHP: A Modern Approach

The provided code snippet attempts to handle a common data migration task: reading a CSV file and inserting records directly into a MySQL database. While the logic is straightforward, the implementation relies on several deprecated functions and unsafe practices that make it unsuitable for modern PHP versions (5.6+) or any environment running PHP 7.0+.

The immediate error Deprecated: Function split() indicates that the code is using a function removed in PHP 5.4. The standard replacement for splitting strings by a specific delimiter is explode(). Furthermore, the use of the old MySQL API (mysql_*) was deprecated in PHP 5.5 and removed entirely in PHP 7.0. This extension has been superseded by mysqli (MySQL Improved) or PDO, which offer robust prepared statements to prevent SQL injection vulnerabilities inherent in the dynamic string concatenation seen in the original script.

Handling CSV Parsing Safely

The original script uses explode($fieldseparator, $line) to parse rows. This approach fails if a data field itself contains a comma, a scenario common in CSV files where values are quoted (e.g., "New York, NY"). Without proper parsing logic, the record count will be accurate, but the database insertion will fail or corrupt data.

For robustness, you should use fgetcsv(). This function reads a single line from a file pointer and parses it into an array, automatically handling quoted fields and escaped delimiters. If you must stick to string manipulation for performance on massive files (though this is rarely the bottleneck), ensure you validate that the delimiter does not appear within quotes before using explode().

Modernizing Database Connections

The connection string $con = @mysql_connect(...) is no longer viable. You should migrate to mysqli or PDO. Below is a migration example using mysqli with prepared statements. This prevents SQL injection by treating user data as strings rather than executable code.

<?php
$host = "localhost"; 
$dbname = "test"; 
$username = "test"; 
$password = ""; 

// Create connection
$conn = new mysqli($host, $username, $password);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$conn->select_db($dbname);

// Prepare the INSERT statement
$stmt = $conn->prepare("INSERT INTO sample (column1, column2, column3) VALUES (?, ?, ?)");

// Bind parameters. 's' stands for string. Adjust based on your actual table schema.
$stmt->bind_param("sss", $col1, $col2, $col3);
?>

Processing the File Stream

The original script loads the entire file into memory using fread($file, $size). For a 5,124-record file (assuming average row size), this fits in RAM easily. However, loading gigabytes of data at once can cause memory exhaustion. A streaming approach is safer and more scalable.

Instead of reading the whole file into $csvcontent, iterate over the file pointer directly:

$file = fopen($csvfile, "r");
while (($line = fgetcsv($file)) !== false) {
    // Process each row immediately without storing all in memory
    $lines++;
    
    // Example: Map CSV columns to SQL placeholders
    // Assuming the CSV has headers matching 'col1', 'col2', etc.
    // Or assuming fixed columns at indices 0, 1, 2
    
    if ($addauto) {
        // Handle auto-increment scenarios
        $query = "INSERT INTO sample VALUES ('', ?, ?)";
    } else {
        $query = "INSERT INTO sample VALUES (?, ?, ?)";
    }
    
    // Execute immediately to avoid building a massive SQL string in memory
    $stmt = $conn->prepare($query);
    $stmt->bind_param("sss", $val1, $val2, $val3);
    $stmt->execute();
}
fclose($file);
$conn->close();

Output and Permissions

The error File is not writable occurs because the script attempts to write to $outputfile relative to the web root (e.g., C:\wamp\www\aaj2). Web servers often run as a user with restricted permissions. If you need to generate an .sql dump, ensure the directory has write access for the web server process (e.g., chmod 755 or specific ownership). Alternatively, output the results to standard output (stdout) or a database transaction log instead of a file if file system permissions are restrictive.

Conclusion on Migration

Replacing legacy code is not just about fixing errors; it is about adopting secure patterns. Switching from mysql_* to mysqli with prepared statements is mandatory for security. Switching from split() to fgetcsv() or explode() ensures data integrity. By streaming the file and executing queries immediately, you maintain memory efficiency regardless of the file size.

Our network