Importing to a SQL table from a CSV file in Drupal, so you don't have to parse exceptionally large CSVs line by line and instead can do so with MySQL querying. This snippet assumes you have read the column headers already from the CSV and passed them as parameters, as well as the temp table name you want to use.
<?php
/**
* Creates a temporary table to hold values from an uploaded CSV.
* @param $table_name
* @param $columns
* @param $message
* @param $context
*/
function csv_import_create_temp_table($table_name, $columns) {
if (db_table_exists($table_name)) {
return;
}
$schema = array(
'description' => 'Temporary table.',
);
$schema['fields']['imported'] = array(
'type' => 'int',
'unsigned' => TRUE,
'not null' => TRUE,
'default' => 0
);
foreach ($columns as $column) {
// @todo: handle bad headers better than this
if ($column == ' ') {
$column = 'Blank';
}
// convert human friendly CSV header to a machine name
$column = _custom_generate_machine_name($column);
// example - if you have a specific column that contains data values, create it as a float
if ($column == 'datapoint') {
$schema['fields'][$column] = array(
'description' => 'The value of this data point.',
'type' => 'float',
'size' => 'big',
'unsigned' => TRUE,
'not null' => TRUE,
);
} else {
$schema['fields'][$column] = array(
'type' => 'varchar',
'length' => 256,
'not null' => TRUE,
'default' => ''
);
}
}
$schema['primary key'] = array('id');
db_create_table($table_name, $schema);
}
/**
* This takes the provided uploaded file and imports it directly into a temporary table in the database.
* Drupal has some issues executing the statement, so the PDO class is leveraged with some different options to allow it without error.
* @param $table_name
* @param $uri
* @param $message
* @param $context
*/
function populate_mysql_table_with_infile($table_name, $uri) {
$database = Database::getConnectionInfo()['default'];
$data_source = 'mysql:host=' . $database['host'] . ';dbname=' . $database['database'];
$db_user = $database['username'];
$db_password = $database['password'];
$connection = new PDO($data_source, $db_user, $db_password,
array(
PDO::ATTR_EMULATE_PREPARES => TRUE,
PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => TRUE,
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_PERSISTENT
)
);
$statement = $connection->prepare("LOAD DATA LOCAL INFILE '" . drupal_realpath($uri) . "' INTO TABLE " . $table_name . "
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES");
$statement->execute();
$statement->closeCursor();
}