I'm trying to upload a CSV file to save records into database with php. I used the sql method LOAD DATA INFILE
but it didn't work anyway.
Index.php has the form with <input name='csv' type='file' id='csv'/>
.
My file upload has 5 strings columns and 2 integers (last of them), moreover it has only two rows, header and values.
They are the fields NOT NULL
in 'usuarios' database.
So here's the problem, when I trying to add a record (for instance: 'bea') it says that
.....(sooo long)......K8docProps/app.xmlPK Data too long for column 'NombreUsuario' at row 1
Yeah, readfile shows that, so I changed the details of every column (I don't think it was the problem) and put values VARCHAR(200) / INTEGER(200)
, whatever it doesn't let me put more length because I tried Specified key was too long; max key length is 767 bytes
.
And here's my code, I made it with others 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"; }
Maybe it was for the UTF-8 encode?
This is my first question in StackOverFlow, so hello everybody from Spain! And thank you!
Well, I finally finished it! First at all I realized that the separator was
;
and withaddslashes($data[0])
works fine.You can use my code and try it.