PHP - importing csv into database data for too long

Question

I am trying to upload a CSV file to save records to a database using php. I used the sql LOAD DATA INFILE method, but it still didn't work.

Index.php has the form with <input name='csv' type='file' id='csv'/> .

My file upload has 5 lines and 2 integers (the last of them), and it has only two lines, a header and values.

These are NOT NULL fields in the 'usuarios' database. So here's the problem, when I try to add an entry (for example: "bea"), it says that

..... (sooo long) ...... K8docProps / app.xmlPK Data is too long for column "NombreUsuario" in row 1

Yes, the readfile shows this, so I changed the details of each column (I don’t think it was a problem) and put the VARCHAR(200) / INTEGER(200) values, regardless of the fact that it does not give me more length, because I tried Specified key was too long; max key length is 767 bytes Specified key was too long; max key length is 767 bytes .

And here is my code, I did this with other examples:

subirCSV.php

     require ('../cabses.php');
     require ('../conecta.php');
     if (isset ($ _ POST ['submit'])) {
         if (is_uploaded_file ($ _ FILES ['csv'] ['tmp_name'])) {
             echo "File".  $ _FILES ['csv'] ['name']. "Uploaded successfully.";
             echo "Displaying contents:";
             readfile ($ _ FILES ['csv'] ['tmp_name']);
         }
         $ handle = fopen ($ _ FILES ['csv'] ['tmp_name'], "r");
         $ flag = true;
         while (($ data = fgetcsv ($ handle, 1000, ""))! == FALSE) {
             if ($ flag) {$ flag = false;  continue;  }
             $ import = "INSERT INTO usuarios (NombreUsuario, PassUsuario, EmailUsuario, Nombre, Apellidos, IdPropietario, IdRol) VALUES 
                             (
                                 '".trim ($ data [0],'" '). "',
                                 '".trim ($ data [1],'" '). "', 
                                 '".trim ($ data [2],'" '). "',
                                 '".trim ($ data [3],'" '). "',
                                 '".trim ($ data [4],'" '). "', 
                                 '".trim ($ data [5],'" '). "',
                                 '".trim ($ data [6],'" '). "'
                             )
                         ";
             $ oConni-> query ($ import) or die (mysqli_error ($ oConni). "____________". $ import);
         }
         fclose ($ handle);
         print "Import done";
     } else {
         print "Not working";
     }

Perhaps it was for UTF-8 encoding?

This is my first question at StackOverFlow, so hello everyone from Spain! And thanks!

Answer 1

Ok, I finally finished! First, I realized that a separator ; and addslashes($data[0]) works fine.

You can use my code and try it.

     require ('../conecta.php');
     if (isset ($ _ POST ['submit'])) {
             if (is_uploaded_file ($ _ FILES ['csv'] ['tmp_name'])) {
                 $ handle = fopen ($ _ FILES ['csv'] ['tmp_name'], "r");
                 $ flag = true;
                 while (($ data = fgetcsv ($ handle, 1000, ";"))! == FALSE) {
                     if ($ flag) {$ flag = false;  continue;  }
                     $ sql = "INSERT INTO usuarios (NombreUsuario, PassUsuario, EmailUsuario, Nombre, Apellidos, IdPropietario, IdRol) VALUES 
                                     (
                                         '".addslashes ($ data [0])."',
                                         '".addslashes (md5 ($ data [1]))."',
                                         '".addslashes ($ data [2])."',
                                         '".addslashes ($ data [3])."',
                                         '".addslashes ($ data [4])."',
                                         '".addslashes ($ data [5])."',
                                         '".addslashes ($ data [6])."'
                                     )
                                 ";
                     $ oConni-> query ($ sql);
                 }
                 fclose ($ handle);
                 header ('Location: ../ index.php');
             } else {
                 print "No funciona";
             }
     }

Answer 2

Try

 $type=$_FILES['file']['type']; $filename=$_FILES["file"]["tmp_name"]; $filename_csv = explode(".", $_FILES["file"]["name"]); $extension = end($filename_csv); if($extension=="csv") { if($_FILES["file"]["size"] > 0) { $file = fopen($filename, "r"); while (($emapData = fgetcsv($file, 10000, ",")) !== FALSE) { $sql = mysql_query("insert into upload_data(spent,click,filename,date) values('$emapData[0]','$emapData[1]','$emapData[2]','$emapData[3]')") or die (mysql_error()); mysql_query($sql); } fclose($file); echo $error1=ucwords('<div style="margin-left:60px;position: absolute;width: 400px; color: #006400;">CSV File has been successfully Imported</div>'); } } else { echo $error1=ucwords('<div style="margin-left:60px;position: absolute;width: 400px; color: #CC0000;">Invalid File:Please Upload CSV File</div>'); // echo 'Invalid File:Please Upload CSV File'; }