CodeIgniter Forums
Need help to improve data import from text file - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forum-20.html)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forum-23.html)
+--- Thread: Need help to improve data import from text file (/thread-8752.html)



Need help to improve data import from text file - El Forum - 05-29-2008

[eluser]thinzar[/eluser]
I'm currently working on an application (Import Module) where it import data from text file which the user uploaded.

The problem is the import works fine if the record is < 20,000.. but it can't handle if it is more than that.
Possible problems that i can think of are:
1. Since CI is using persistent SQL connection, there might be connection time out problem
2. Is it because of the cache limit exceed since it has to hold a long SQL query? (but even if i insert one record after another, the connection time-out problem occur.)

This is my first project using CI, so i'm not very familiar with its functionalities yet. Please give me suggestions on how to improve the performance since the module needs to handle data import of large data set. Or if there is any other method that i can use (rather than trimming each record and inserting one by one)?

The partial code snippet is as below..
Code:
function importData($filepath, $data, $datasource){
            $fs = fopen($filepath, 'r');
            $id = $this->getNextClientID();

            $id++;

            $queries = "";

            while (!feof($fs) )
            {
                $sql = "";
                $line = fgets($fs);

                // data format
                $name= trim(substr($line, 0, 12));
                $age= trim(substr($line, 13, 8));
                $address= trim(substr($line, 22, 80));
                $dob = trim(substr($line, 103, 10));
                $maritalStatus = trim(substr($line, 114, 1));

                if(!$this->checkClientExistsByName($name)){
                        $sql = " INSERT INTO clients (client_id, client_name, client_age, client_addr, client_dob, client_maritalstatus) ";
                        $sql.= " VALUES($id, $name, $age, ".$this->db->escape($address).", ".$this->db->escape($dob).", $maritalStatus";
                        
                        $id++;
                        $queries .= $sql . "\n";
                }
                else{
                                       //ignore the record
                }
            }
            if($queries != ""){
                $this->db->query($queries);
            }
}
Thanks in advance.
P.S. I'm using PostgreSQL. will COPY instead of INSERT work?


Need help to improve data import from text file - El Forum - 06-01-2008

[eluser]Chris Williams[/eluser]
I actually skipped trying to figure out this one using CI and chose a solution already out there.

check out http://www.phpcsvimporter.co.uk/

You maybe able to pull code from it for your issue as well. Who knows. ;-)


Need help to improve data import from text file - El Forum - 06-01-2008

[eluser]thinzar[/eluser]
[quote author="Chris Williams" date="1212357861"]I actually skipped trying to figure out this one using CI and chose a solution already out there.

check out http://www.phpcsvimporter.co.uk/

You maybe able to pull code from it for your issue as well. Who knows. ;-)[/quote]
Thanks for the suggestion... this can be one option