module.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();
}