LOAD DATA from file to Database

load-csv-to-database.php

<?php

/**
 * Adding data to table using CSV data
 * Using WPDB in WordPress but you can use any PHP...
 *
 * @link https://dev.mysql.com/doc/refman/5.7/en/load-data.html
 */

global $wpdb;

$table_name = 'example_table';

$csv_file = '/path/to/file/csv_file.csv';

if ( ! file_exists( $csv_file ) ) exit;

// Load file to table
$insert_query = "LOAD DATA LOCAL INFILE '$csv_file' INTO TABLE $table_name ";

// In CSV we use ',' to separate fields (enclosing string with '"') and each line is a row
$insert_query .= "FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' ";

// If first line is the header, ignore it
$insert_query .= "IGNORE 1 LINES ";

// Set column schema as you wish
$insert_query .= "(@field_as_var_but_not_used, column2, @field_as_var) ";

// If using variables, set the coluns as you need (in this example we are converting to DECIMAL)
$insert_query .= "SET column3 = CAST(@field_as_var AS DECIMAL(9,2))";

// Then run it
$wpdb->query( $insert_query );