Import csv long file into sqlite database takes too long

I want to insert al large csv file (2500 rows) into a sqlite database. On my lokal computer (xampp) the import takes about 3 minutes. This project on a real linux webserver takes enless time. What is the best practices for importing large csv files? How can I speed up the insert procedure wirth CI?

Here is my code:

Database

PHP Code:

$db['default'] = array(
    'dsn'    => '',
    'hostname' => '',
    'username' => '',
    'password' => '',
    'database' => APPPATH.'/database/myDB.db',
    'dbdriver' => 'sqlite3',
    'dbprefix' => '',
    'pconnect' => FALSE,
    'db_debug' => (ENVIRONMENT !== 'production'),
    'cache_on' => FALSE,
    'cachedir' => '',
    'char_set' => 'utf8',
    'dbcollat' => 'utf8_general_ci', //utf8_unicode_ciutf8_general_ci
    'swap_pre' => '',
    'encrypt' => FALSE,
    'compress' => FALSE,
    'stricton' => FALSE,
    'failover' => array(),
    'save_queries' => TRUE
); 


Controller

PHP Code:

$this->load->model('db_model');

$csv = read_file('./folder/import.csv');
$new_csv = explode(PHP_EOL, $txt);

foreach($new_csv as $row){ 
    $array = explode(',', str_replace("\"", "", $row));
}

$this->db_model->emptyTable();

for ($i = 0; $i < sizeof($array)-1; $i++) {
    $myData = array(
        'var1'     => $array[$i][0],
        'var2'     => $array[$i][1],
        'var3'     => $array[$i][2],
        'var4'     => $array[$i][3],
        'var5'     => $array[$i][4],
        'var6'     => $array[$i][5],
        'var7'     => $array[$i][6],
        'var8'     => $array[$i][7]
    );
    
$this->db_model->setTimetable($myData); 


Model

PHP Code:

public function emptyTable() {
    $this->db->empty_table('tableName');         
}    

public function setTimetable($data) {
    $this->db->insert('tableName', $data);    
} 


Thanks for your help!

1. First, that is a big import and your server may well have limits on the size of upload or a time limit on the processing

2. To speed it up, create your entire multi dimensional array first, then do a single database call with batch_insert. Otherwise you are doing 2500 database queries, which is ridiculous.

3. Allow partial updates and limit the file upload size. So first do a delete using where_in and an array of row identifiers from the CSV, then insert the new data.

4. Stuff on a local device is always going to be much quicker than on a web server, that not only has to do all the work your local machine does, it is probably having to do a whole lot more. Server environments can be very complicated.

Thanks PaulD! I will try no. 2. batch_insert was unknown to me and i will try this function.

Partial updates is no solution for me, becouse each csv file has compleately new data, so I have to empty the table and have to insert all 2500 rows again.


I read something about wrapping your inserts in a single transaction like this:

Code:

$db->beginTransaction();

...

$db->commit();

Is this equal to insert_batch?

Not really, that is a different thing.

The transactions you are talking about there do something like an entire series of queries, inserts, updates and deletes, but if any of the queries within one transaction fails, they are all cancelled and none of them are implemented. Any changes made during that transaction are rolled back. Imagine a bank has to take some money out of one account and then add it to another, but the add fails, so the bank wants the entire transaction to roll back to the original state, so no money is lost anywhere.

Insert batch just writes the sql query for you but inserting lots of data all in one go in a single sql query. (I say 'just' but that is not to imply it is simple or unsophisticated). All the batch functions are pretty cool actually.

From the docs (http://www.codeigniter.com/user_guide/da...sert_batch)

PHP Code:

$data = array(
    array(
        'title' => 'My title',
        'name' => 'My Name',
        'date' => 'My date'
    ),
    array(
        'title' => 'Another title',
        'name' => 'Another Name',
        'date' => 'Another date'
    )
);

$this->db->insert_batch('mytable', $data);
// Produces: INSERT INTO mytable (title, name, date) VALUES ('My title', 'My name', 'My date'),  ('Another title', 'Another name', 'Another date') 


Best wishes,

Paul.

SirTom,

Another way to import files is you could upload the file to the server and then use command line file import for sqlite https://www.sqlite.org/cli.html#section_8.

That is only useful if you have the correct matching file to table layout.

I have had to do this with 100,000+ rows with MySql and it was VERY quick. I would just upload the file then run the command line to do the import (LOAD DATA INFILE Syntax).

Again only if your file matches your table.

iWAT LLC - Application Development & Website Creation

I want to insert al large csv file (2500 rows) into a sqlite database. On my lokal computer (xampp) the import takes about 3 minutes. This project on a real linux webserver takes enless time. What is the best practices for importing large csv files? How can I speed up the insert procedure wirth CI?

Here is my code:

Database

PHP Code:

$db['default'] = array(
    'dsn'    => '',
    'hostname' => '',
    'username' => '',
    'password' => '',
    'database' => APPPATH.'/database/myDB.db',
    'dbdriver' => 'sqlite3',
    'dbprefix' => '',
    'pconnect' => FALSE,
    'db_debug' => (ENVIRONMENT !== 'production'),
    'cache_on' => FALSE,
    'cachedir' => '',
    'char_set' => 'utf8',
    'dbcollat' => 'utf8_general_ci', //utf8_unicode_ciutf8_general_ci
    'swap_pre' => '',
    'encrypt' => FALSE,
    'compress' => FALSE,
    'stricton' => FALSE,
    'failover' => array(),
    'save_queries' => TRUE
); 


Controller

PHP Code:

$this->load->model('db_model');

$csv = read_file('./folder/import.csv');
$new_csv = explode(PHP_EOL, $txt);

foreach($new_csv as $row){ 
    $array = explode(',', str_replace("\"", "", $row));
}

$this->db_model->emptyTable();

for ($i = 0; $i < sizeof($array)-1; $i++) {
    $myData = array(
        'var1'     => $array[$i][0],
        'var2'     => $array[$i][1],
        'var3'     => $array[$i][2],
        'var4'     => $array[$i][3],
        'var5'     => $array[$i][4],
        'var6'     => $array[$i][5],
        'var7'     => $array[$i][6],
        'var8'     => $array[$i][7]
    );
    
$this->db_model->setTimetable($myData); 


Model

PHP Code:

public function emptyTable() {
    $this->db->empty_table('tableName');         
}    

public function setTimetable($data) {
    $this->db->insert('tableName', $data);    
} 


Thanks for your help!

Maybe you can try to use Spout Library for import csv file

http://teknosains.com/i/codeigniter-baca...ngan-spout