Skip to content

Instantly share code, notes, and snippets.

@fcingolani
Last active April 8, 2024 02:52
Show Gist options
  • Save fcingolani/5364532 to your computer and use it in GitHub Desktop.
Save fcingolani/5364532 to your computer and use it in GitHub Desktop.
PHP function to import a CSV into an SQLite database. Creates a table if needed. Uses PDO.
<?php
function import_csv_to_sqlite(&$pdo, $csv_path, $options = array())
{
extract($options);
if (($csv_handle = fopen($csv_path, "r")) === FALSE)
throw new Exception('Cannot open CSV file');
if(!$delimiter)
$delimiter = ',';
if(!$table)
$table = preg_replace("/[^A-Z0-9]/i", '', basename($csv_path));
if(!$fields){
$fields = array_map(function ($field){
return strtolower(preg_replace("/[^A-Z0-9]/i", '', $field));
}, fgetcsv($csv_handle, 0, $delimiter));
}
$create_fields_str = join(', ', array_map(function ($field){
return "$field TEXT NULL";
}, $fields));
$pdo->beginTransaction();
$create_table_sql = "CREATE TABLE IF NOT EXISTS $table ($create_fields_str)";
$pdo->exec($create_table_sql);
$insert_fields_str = join(', ', $fields);
$insert_values_str = join(', ', array_fill(0, count($fields), '?'));
$insert_sql = "INSERT INTO $table ($insert_fields_str) VALUES ($insert_values_str)";
$insert_sth = $pdo->prepare($insert_sql);
$inserted_rows = 0;
while (($data = fgetcsv($csv_handle, 0, $delimiter)) !== FALSE) {
$insert_sth->execute($data);
$inserted_rows++;
}
$pdo->commit();
fclose($csv_handle);
return array(
'table' => $table,
'fields' => $fields,
'insert' => $insert_sth,
'inserted_rows' => $inserted_rows
);
}
@roguewavelimited
Copy link

Excellent! Thank you.

@oldfart101
Copy link

called with:import_csv_to_sqlite(&new PDO('sqlite:test.sqlite'),'test1.csv', $options=array());

syntax error, unexpected '&' in /var/www/html/acsv/index.php on line 2
Apache/2.4.41 (Ubuntu)
php 7.4.3
Ubuntu server 20.04.3

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment