Insert 10000 rows data from CSV in mysql without skipping any rows

Question

I need to insert my CSV data into mysql by insert query. Currently my CSV having 9976 rows data. But after running the query 9-10 lines skips randomly after 1000 rows and very hard to find out that which rows was skipped.

Firstly I tried to access the folder, then accessing the file and taking the csv first row as my header for table. Then running the query to insert the data in mysql but it skipped some random rows.

    $dir = "C:\Users\\".strtolower($username)."\Downloads";
    $fp = opendir($dir);
    $dates = array();
    $latest_file = glob($dir."\\Filter_ Tempo-jql-AP*");
    closedir($fp);
    $filepath=$latest_file[0];
    $the_big_array = [];
    $tablename="aht_tracker";
    $dbname="ford_resource_capacity";
    $conn =mysqli_connect("localhost","root","","$dbname") or die(mysqli_connect_error());
    $fields="";
    $fields1="";
    $fieldsinsert="";
    if (($h = fopen("{$filepath}", "r")) !== FALSE)
    {
        if (($data = fgetcsv($h, 100000, ",")) !== FALSE)
        {
            $issuekey=array_search("Issue key", $data);
            $hours=array_search("Hours", $data);
            $username=array_search("Username", $data);
            $issuetype=array_search("Issue Type", $data);
            $workdescription=array_search("Work Description", $data);
            $new_array=array($issuekey,$hours,$username,$issuetype,$workdescription);
            $arr_count=count($new_array);

            $c=0;
            $fieldsinsert .='(';
            foreach ($new_array as $key => $value)
            {
                $fieldsinsert .=($key==0) ? '' : ', ';
                $fieldsinsert .="`".str_replace(" ","_",$data[$value])."`";
                $fields .="`".str_replace(" ","_",$data[$value])."` varchar(250) DEFAULT NULL,";
            }
            $fieldsinsert .= ')';
        }
        while(($data = fgetcsv($h, 100000, ",")) !== FALSE)
        {

            $fieldsInsertvalues="";
            $c=0;
            foreach ($new_array as $key => $value)
            {
                $fieldsInsertvalues .=($key==0) ? '(' : ', ';
                $fieldsInsertvalues .="'".$data[$value]."'";
            }
            $fieldsInsertvalues .= ')';
            $sql1 = "INSERT INTO ".$tablename." ".$fieldsinsert."VALUES".$fieldsInsertvalues;
            mysqli_query($conn,$sql1);
        }
        fclose($h);
        //unlink($filepath);
    }

Show me some code which will help me to insert my all rows from csv data or give me some idea that is it possible to insert the csv data in 500-500 packet.

I would seriously suggest using `LOAD DATA` for bulk loading into MySQL from a CSV file. Cooking up a PHP script is reinventing the wheel IMHO. If you CSV file has problems, then this is another issue. — Tim Biegeleisen, Oct 11 '19 at 09:30
Refer this: [https://stackoverflow.com/questions/12622013/import-large-csv-file-into-mysql](https://stackoverflow.com/questions/12622013/import-large-csv-file-into-mysql) — Shaunak Sontakke, Oct 11 '19 at 09:40
Can you show an example of a few lines of the input CSV file please — RiggsFolly, Oct 11 '19 at 09:43
@TimBiegeleisen I tried with LOAD DATA but unable to achieve the result. — kumarravi, Oct 14 '19 at 08:33

score 0 · Answer 1 · answered Oct 11 '19 at 10:00

0

https://dev.mysql.com/doc/refman/8.0/en/load-data.html

It is more effective to do the MySQL command. Import the data from CSV. You can specificy Container, Separator and stuff.

answered Oct 11 '19 at 10:00

dodzb

379
2
5

This is really just a comment, you have enough reps to comment – RiggsFolly Oct 11 '19 at 10:08
Please have a look on the code:- $username="Ravi.Kumar"; $dir = "C:\Users\\".strtolower($username)."\Downloads"; $fp = opendir($dir); $latest_file = glob($dir."\\Filter_ Tempo-jql-AP*"); closedir($fp); $filepath=$latest_file[0]; $tablename="aht_tracker"; $dbname="ford_resource_capacity"; $conn =mysqli_connect("localhost","root","","$dbname") or die(mysqli_connect_error()); $select= mysqli_query($conn, 'LOAD DATA LOCAL INFILE "'.$filepath.'" INTO TABLE "'.$tablename.'" IGNORE 1 LINES'); – kumarravi Oct 14 '19 at 08:09
@kumarravi you dont have to do that inside PHP. Just use konsole window or dos prompt and execute LOAD DATA command. – dodzb Oct 16 '19 at 10:10
check this image. http://www.agcross.com/2014/09/import-csv-mysql-database/ @kumarravi – dodzb Oct 16 '19 at 10:12

score 0 · Answer 2 · answered Oct 15 '19 at 08:55

Now I understood why my program skips some random line. This is not just skipping the random lines. It is skipping the whole row when that row contain single quote in any of the column data.

So, again i am asking to all of you those. Does any one have the solution that how can i skip that particular column which contain single quote. I do not want to skip the row i just want o skip that cell from the csv.

Please update my code in which the program can skip those particular cell which contain single quote.

Insert 10000 rows data from CSV in mysql without skipping any rows

2 Answers2