Import CSV File into a SQLite Database via PHP

Question

I want to import a table from a CSV file into a SQLite DB via a PHP script that I can manually run to update the data.

Heres a list of what I want to achieve:

Rename old table (which is called "produkte") into product-currentdate (Or drop the table)
Then import the files from the CSV File ( ; separated and ISO 8859-1 charset / The first row of the CSV-file contains the table header)
Save the date in the table "product"

I've found a script which for some reason does not work:

<?php
 $dir = 'sqlite:test.sqlite';
 $dbh  = new PDO($dir) or die("cannot open the database");


 $query = <<<eof
  LOAD DATA LOCAL INFILE 'produkte.csv'
  INTO TABLE produkte
  FIELDS TERMINATED BY ';'
  OPTIONALLY ENCLOSED BY '"' 
  LINES TERMINATED BY '\n'
  IGNORE 1 LINES 
  (id, Hauptmenue, Produktgruppe, Beschreibung, Text, Bild, Shop, Info)

 eof;

 $dbh->query($query);

?>

I hope someone knows how to solve my problem...

Best regards Dave

as both functions are not working for you i would suggest you check your file for errors — Rachel Gallen
– Rachel Gallen, Commented Jun 28, 2014 at 4:55

Paul · Accepted Answer · 2014-06-27 12:17:20Z

3

Federico Cingolani has posted a php script at Github that meets your needs

 <?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
        );

}

edited Jun 27, 2014 at 12:17

Paul

27.8k13 gold badges90 silver badges127 bronze badges

answered Jun 27, 2014 at 12:14

Rachel Gallen

28.7k22 gold badges77 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

iD4ve Over a year ago

Thank you very much :) How does the options array look like?

iD4ve Over a year ago

can someone give me an example of how to execute the function (I need the parameters..)

iD4ve Over a year ago

So if i have: $dir = 'sqlite:test.sqlite'; $dbh = new PDO($dir) or die("cannot open the database"); $csv = 'produkte.csv'; and then I call the function like this?: import_csv_to_sqlite(&$dbh, $csv);

iD4ve Over a year ago

I get an error: Parse error: syntax error, unexpected T_FUNCTION, expecting ')' in /path-to/getcsvdata.php on line 20 and line 20 says: $fields = array_map(function ($field){

iD4ve Over a year ago

no changes.... I used the following code: I copied the version from Github and then called the function like this: import_csv_to_sqlite(&new PDO('sqlite:test.sqlite'),'produkte.csv', $options=array());

Collectives™ on Stack Overflow

Import CSV File into a SQLite Database via PHP

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related