0

I have a problem importing csv-files into a PostgreSQL database using php. The csv-file holds around 11000 datalines, however php only imports around 2100 lines. (I looked through the data and found special characters like <!--. I replaced them with "nan" but the problem remains.)

When I import it with the pgAdmin wizard it works fine and imports the data completely (even with the special characters left in).

$filename=$_FILES["file"]["tmp_name"];      

     if($_FILES["file"]["size"] > 0)
     {
        ini_set("auto_detect_line_endings", true);
        $file = fopen($filename, "r");
        $flag = true;
        $row = 1;

        while (($getData = fgetcsv($file, 10000, ";")) !== FALSE)
         {
            if($flag) { $flag = false; continue; }

            $sql = "INSERT into public.fulldict (studiennr,studienname,formular,formularmodul,modul_id,titel_der_ableitung,abl_version,feldname,hilfetext,variablenname,pflichtfeld,feldtyp,feldlaenge,min_wert,max_wert,einheit,code,read_only,exeptional_values,passiv) 
                    values ('".pg_escape_string($getData[0])."','".pg_escape_string(($getData[1]))."','".pg_escape_string(($getData[2]))."','".pg_escape_string(($getData[3]))."','".pg_escape_string(($getData[4]))."','".pg_escape_string(($getData[5]))."','".pg_escape_string(($getData[6]))."','".pg_escape_string(($getData[7]))."','".pg_escape_string(($getData[8]))."','".pg_escape_string(($getData[9]))."','".pg_escape_string(($getData[10]))."','".pg_escape_string(($getData[11]))."','".pg_escape_string(($getData[12]))."','".pg_escape_string(($getData[13]))."','".pg_escape_string(($getData[14]))."','".pg_escape_string(($getData[15]))."','".pg_escape_string(($getData[16]))."','".pg_escape_string(($getData[17]))."','".pg_escape_string(($getData[18]))."','".pg_escape_string(($getData[19]))."')";
                $result = $con -> prepare($sql);
                $result -> execute();
            if(!isset($result))
            {
                echo "<script type=\"text/javascript\">
                        alert(\"Invalid File:Please Upload CSV File.\");
                        window.location = \"upload.php\"
                      </script>";   
            }
            else {
                echo "<script type=\"text/javascript\">
                    alert(\"CSV File has been successfully Imported.\");
                    window.location = \"upload.php\"
                </script>"; 
            }

         }
         fclose($file); 
         ini_set("auto_detect_line_endings", false);
     }
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Basti
  • 15
  • 7
  • You need to escape the values correct way. Either use pg_escape_string or use query binding. And make sure your CSV file is a valid CSV file. – Himan Sep 28 '17 at 14:50
  • 1
    Is there any reason you are using PHP to parse the CSV, rather than using Postgres' copy command? – Scoots Sep 28 '17 at 14:52
  • @Himan I tried pg_escape_string, updated that in the post above. I'm getting more lines than before (almost 3000 now as compared to the 2100 before) but still not complete. – Basti Sep 29 '17 at 07:26
  • @scoots I'm building an internal webpage where authorized users should be able to upload data into the database within the page's interface. – Basti Sep 29 '17 at 07:30
  • @scoots how would you execute the Postgresql copy command through php? – Basti Sep 29 '17 at 09:20
  • some servers (almost all hosters) will set a time limit for any php process. If your process takes longer, it will be killed and you end up with incomplete data. (http://php.net/max-execution-time) Are you certain, that this is not the reason here? do you output error messages and if so, are there any? also: you should probably read up on how to use prepared statements. – Jakumi Sep 29 '17 at 10:54
  • @Basti Give the postgres user read access to the tmp directory, move/copy the csv to the tmp directory `copy tablename (/*columns*/) from '/tmp/file.csv'` -- https://www.postgresql.org/docs/current/static/sql-copy.html – Scoots Sep 29 '17 at 11:54
  • Can't be the time limit...I guess. Import should only take 1 to 3 sec at most. When it loops through the error messages it takes way longer and the process isn't killed (I think max execution time is set to 30sec). Yes I put out error messages ( setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION)), but it doesn't show any. – Basti Sep 29 '17 at 12:31

1 Answers1

0

It works now. I had to move the if(!isset($result)){} outside of the loop. The check caused the loop to end prematurely. However I don't see how $resultcould be empty before the csv-file is read completely...

Basti
  • 15
  • 7