Upload CSV into database using php

Question

I am working on a little project of mine and I came to the conclusion that being able to automatically upload a excel sheet to my database would be very, very useful,the problem is that I don´t have an idea of where to start, I have researched a bit and decieded to use a CSV file created from a excel sheet to upload the data into the table of my DB.

Most of the examples I have seem look like a mess with the PHP code into the html instead of dividing the logic in different files like what I have been doing in this last 2 months.

What I have right now is the upload form in html:

       <form enctype="multipart/form-data" method="post" id="uploadForm">
          <input name="filesfiles" id="upload" type="file" accept=".csv" class="left" />
          <input type="submit" value="Cargar" />
       </form>

And a small sample of how the CSV file looks in text:

Cedula;Nombre;Apellido1;Apellido2;Correo;IdRol;Estado
1657890;Dominico;Scarlatti;Viera;[email protected];2;0
5657890;Franz;Listz;Linerman;[email protected];3;0

Or in some other excel versions:

Cedula,Nombre,Primer Apellido,Segundo Apellido,Correo,IDRol,Estado
126548791,Franz ,Ritter ,von Liszt,[email protected],3,0
174657109,Sofia ,Asgatovna ,Gubaidulina ,[email protected],3,0

The first row is the name of the columns (which should be ignored when adding the info) of the table I want to upload the file into.

The problem is that I don´t know how to link the upload file once the submit button is clicked to a PHP code in my includes that inserts the CSV into the table.

Thanks a lot in advance

EDIT:

JSFiddle of the upload form

EDIT4:

I am a stroke of pure genius and skill Maduka was able to help me solve this behemoth of problem. I can't thank him enough, the following is the code used in hopes that it may serve someone someday and save them the grief of failure.

<?php

error_reporting(E_ALL & ~E_NOTICE & ~E_DEPRECATED & ~E_WARNING & ~E_STRICT);

mysql_connect('localhost', 'root', '');
mysql_select_db("proyecto") or die(mysql_error());

if (isset($_FILES['csvupload'])) {
    $errors = array();
    $allowed_ext = array('.csv');

    $file_name = $_FILES['csvupload']['name'];
    $file_ext = strtolower(end(explode('.', $file_name)));
    $file_size = $_FILES['csvupload']['size'];
    $file_tmp = $_FILES['csvupload']['tmp_name'];

    if (in_array($allowed_ext) === false) {
        $errors[] = 'La extensión del archivo no es valida.';
    }
    if ($file_size > 10485760) {
        $errors[] = 'El archivo sobrepasa el limite de 10MB';
    }
    if (empty($errors)) {

        $handle = fopen($file_tmp, "r");

        while (!feof($handle)) {
    $value = (fgetcsv($handle, 0, ','));
    if ($i > 0) {
        if ($value[0] != '') {
            $inserts[] = "('" . mysql_real_escape_string($value[0]) . "','"
                    . mysql_real_escape_string($value["1"]) . "','"
                    . mysql_real_escape_string($value["2"]) . "','"
                    . mysql_real_escape_string($value["3"]) . "','"
                    . mysql_real_escape_string($value["4"]) . "','"
                    . mysql_real_escape_string($value["5"]) . "','"
                    . mysql_real_escape_string($value["6"]) . "')";
        }
    } elseif ($i == 0) {
        $fields = $value;
    }
    $i++;
}

mysql_query("INSERT INTO `usuarios` (`cedula`,`nombre`,`apellido1`,`apellido2`,`correo`,`idRol`,`estado`) VALUES " . implode(",", $inserts));
        fclose($handle);
        if ($sq1) {
            echo '¡Los usuarios han sido agregados exitosamente!';
        }
    }
}
?>

@tadman The upload form is on a php file that calls a include files with all the php functions, in between I use jQueries to make the connection between both. Sorry if this isn´t what you mean. — Code Grasshopper
– Code Grasshopper, Commented Apr 24, 2014 at 17:26
I'm saying if you can save the upload to a temporary file, then you can call LOAD DATA INFILE on that path. Done. — tadman
– tadman, Commented Apr 24, 2014 at 19:17
@tadman Well I will have to admit I didn´t know of such method thanks for telling me — Code Grasshopper
– Code Grasshopper, Commented Apr 24, 2014 at 21:37

Maduka Jayalath · Accepted Answer · 2014-04-25 06:09:02Z

Here is the basic code which you need to do your task,

$file = fopen($_FILES['csvUpload']['tmp_name'], "r");
$i = 0;
while (!feof($file)) {
    $value = (fgetcsv($file, 0, ';'));
    if ($i > 0) {
        if ($value[0] != '') {
            $inserts[] = "(" . $value[0] . ","
                    . $value["1"] . ","
                    . $value["2"] . ","
                    . $value["3"] . ","
                    . $value["4"] . ","
                    . $value["5"] . ","
                    . $value["6"] . ")";
        }
    } elseif ($i == 0) {
        $fields = $value;
    }
    $i++;
}

mysql_query("INSERT INTO `MyTable` (`" . $fields[0] . "`,`" . $fields[1] . "`,`" . $fields[2] . "`,`" . $fields[3] . "`,`" . $fields[4] . "`,`" . $fields[5] . "`) VALUES " . implode(",", $inserts));

fclose($file);

You have to implement validation, check file type and size limit. Then insert your data to the table. I have use MySQL bulk insert to handle large amount of data. Hope this helps!

EDIT 1:

Please replace your code with this code and see if it is working correctly.

<?php
error_reporting(E_ALL & ~E_NOTICE & ~E_DEPRECATED & ~E_WARNING);

mysql_connect('localhost', 'root', '');
mysql_select_db("proyecto") or die(mysql_error());

if (isset($_FILES['csvUpload'])) {
    $errors = array();
    $allowed_ext = array('.csv');

    $file_name = $_FILES['csvUpload']['name'];
    $file_ext = strtolower(end(explode('.', $file_name)));
    $file_size = $_FILES['csvUpload']['size'];
    $file_tmp = $_FILES['csvUpload']['tmp_name'];

    if (in_array($allowed_ext) === false) {
        $errors[] = 'La extensión del archivo no es valida.';
    }
    if ($file_size > 10485760) {
        $errors[] = 'El archivo sobrepasa el limite de 10MB';
    }
    if (empty($errors)) {


        $handle = fopen($file_tmp, "r");
        while (($fileop = fgetcsv($handle, ";") && fgetcsv($handle, ",")) !== false) {
            $cedula = mysql_real_escape_string($fileop[0]);
            $nombre = mysql_real_escape_string($fileop[2]);
            $apellido1 = mysql_real_escape_string($fileop[3]);
            $apellido2 = mysql_real_escape_string($fileop[4]);
            $correo = mysql_real_escape_string($fileop[5]);
            $idRol = mysql_real_escape_string($fileop[6]);
            $estado = mysql_real_escape_string($fileop[9]);


            $sq1 = mysql_query("INSERT INTO `usuarios` (cedula,nombre,apellido1,apellido2,correo,idRol,estado) VALUES ('$cedula','$nombre','$apellido1','$apellido2','$correo','$idRol','$estado')");
        }
        fclose($handle);
        if ($sq1) {
            echo '¡Los usuarios han sido agregados exitosamente!';
        }
    }
}
?>



<form enctype="multipart/form-data" method="post" id="uploadForm">
    <input name="csvUpload" id="upload" type="file" accept=".csv" class="left" />
    <input type="submit" value="¡Cargar!" />
</form>

There's no escaping at all here and you're using the awful mysql_query interface.
Well, I dunno that looks quite similar to the one above, and because it is more of a personal and small project I am not afraid of risking injection
@tadman Hope you can read and understand my "English", I told this is basic code and implement validation, check file type and size limit
@CodeGrasshopper Run the code and see if it fits to your requirement. Please let me know if you need to clear anything regarding this.
@Maduka Jayalath, I was unsuccessful in my attends, I am editing my post to add the code and the error.

user2757801 · Accepted Answer · 2014-04-24 17:37:56Z

Upload form

    <form enctype="multipart/form-data" action="uploader.php" method="POST">
    <ul><li>
     <input name="file" type="file" /><br /></li><li>
    <br><input type="submit" name="submit" value="Upload" /></li>
    </ul>
    </form>

uploader.php

            <?php
    if (isset($_FILES['file'])) {
    $errors = array();
    $allowed_ext = array('csv');

    $file_name = $_FILES['file']['name'];
    $file_ext = strtolower(end(explode('.', $file_name)));
    $file_size = $_FILES['file']['size'];
    $file_tmp = $_FILES['file']['tmp_name'];

    if (in_array($file_ext, $allowed_ext) === false) {
        $errors[] ='Extension not allowed';
    }
    if ($file_size > 10485760) {
       $errors[] = 'File size must be under 10mb';
    }
    if (empty($errors)) {





        $handle = fopen($file_tmp,"r");
        while(($fileop = fgetcsv($handle,",")) !== false) 
        {
        $companycode =  mysql_real_escape_string($fileop[0]);
        $pdtcode = mysql_real_escape_string($fileop[2]);
        $Item = mysql_real_escape_string($fileop[3]);
        $pack = preg_replace('/[^A-Za-z0-9\. -]/', '', $fileop[4]);
        $lastmonth = mysql_real_escape_string($fileop[5]);
        $ltlmonth = mysql_real_escape_string($fileop[6]);
        $op = mysql_real_escape_string($fileop[9]);
        $pur = mysql_real_escape_string($fileop[10]);
        $sale = mysql_real_escape_string($fileop[12]);
        $bal = mysql_real_escape_string($fileop[17]);
        $bval = mysql_real_escape_string($fileop[18]);
        $sval = mysql_real_escape_string($fileop[19]);

        $sq1 = mysql_query("INSERT INTO `sas` (companycode,pdtcode,Item,pack,lastmonth,ltlmonth,op,pur,sale,bal,bval,sval) VALUES ('$companycode','$pdtcode','$Item','$pack','$lastmonth','$ltlmonth','$op','$pur','$sale','$bal','$bval','$sval')");
    }
    fclose($handle);
    if($sq1){
        echo 'Stock and Sales successfully updated. Please check the values.<br><br>'; 



            }
        } 
    ?>

The above code is simple. I am using for my project.

It looks like it is similar to what I am looking, judging by the php tags I assume you´re using that code in the same file as the form. Also in my case I have less columns, their types being varchar(20) save for the last two which are int and char(1). Also for the syntax my Excel is saving its CSV with ; instead of , what would I need to change? Thanks a lot in advance and for providing me with a example.
In the 2nd set of codes, replace while(($fileop = fgetcsv($handle,",")) !== false) with while(($fileop = fgetcsv($handle,";")) !== false) and Try......... Im not sure whether it works out
Answers with the ancient, deprecated mysql_query interface are really not a great idea. That works, but a simple mistake could expose you to a huge SQL injection bug.
@Indra I tried changing what you said and of course removing the variables and replacing them with my own, but I am getting a parse syntax error on the end of my document, where the closing html tag is

Collectives™ on Stack Overflow

Upload CSV into database using php

2 Answers 2

8 Comments

7 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

7 Comments

Linked

Related