Import a large CSV file to MySQL table

csv-to-mysql.php

<?php

    // Quick n' dirty script to import a CSV to MySQL

    // -- BEGIN CONFIG --
    $file = ""; // Path to CSV file

    $host = "";
    $user = "";
    $pass = "";
    $database = "";
    $table = "";

    $row_buffer_size = 10; // VALUES() will contain this many rows.
    // -- END CONFIG --

    $db = mysqli_connect($host,$user,$pass,$database);

    $fp = fopen($file, 'r');
    $frow = fgetcsv($fp);

    // Extract column names from first row
    $head = explode(";",$frow[0]);
    $head = array_map(fn($column) => trim($column,"\""),$head);

    // Wrap and concat column names for query
    $head_keys = array_map(fn($column) => "`${column}`",$head);
    $head_keys = implode(",",$head_keys);
    $columns_create = array_map(fn($column) => "`${column}` text(255)",$head);
    $columns_create = implode(",",$columns_create);
    
    // Create database columns from CSV column names
    $columns_create_query = "CREATE TABLE IF NOT EXISTS $table ($columns_create);";
    mysqli_query($db, $columns_create_query) or die(mysqli_error($db));

    // All row insert queries wil start with this string
    $template = "INSERT INTO `${table}` (${head_keys}) VALUES ";

    // Read CSV line by line and submit query when buffer is full
    $row_buffer = [];
    while(($line = fgetcsv($fp,$row_buffer_size,";")) !== false) {
        $cells = array_map(fn($cell) => strip_tags($cell),$line);
        $cells = array_map(fn($cell) => "'${cell}'",$cells);
        $row = implode(",",$cells);
        $row_buffer[] = "(${row})";

        // Insert rows and clear buffer
        if(count($row_buffer) >= $row_buffer_size) {
            $query = $template.implode(",",$row_buffer);
            mysqli_query($db,$query) or die(mysqli_error($db));
            $row_buffer = [];
        }
    }

    // Insert remaining rows in buffer
    if(count($row_buffer) > 0) {
        $query = $template .= implode(",",$row_buffer);
        mysqli_query($db,$query) or die(mysqli_error($db));
        $row_buffer = [];
    }

?>