Creating a CSV Upload to Mysql

Question

I have a table in MYSQL where I wish to have the option that my customer can upload a CSV file. By default I want this to add information which isn't there and automatically update it if there is information there.

These are the columns I have:

id  customer_name   customer_name_letterhead    customer_notes  systype status  signaltype  verification    address postcode    telephone   mobile  mobiletwo   email   mainarea    installation    Contract    expiration  SPA nservice    maintenance monitoring  MS  certdate

I already know that I need to have an excel document with all of these in the headers of the rows. E.g A1-V1 has those headers.

edit: I have made this:

<?PHP    if(isset($_POST['SUBMIT']))
    {
         $fname = $_FILES['sel_file']['name'];

         $chk_ext = explode(".",$fname);

         if(strtolower($chk_ext[1]) == "csv")
         {

             $filename = $_FILES['sel_file']['tmp_name'];
             $handle = fopen($filename, "r");

             while (($data = fgetcsv($handle, 1000, ",")) !== FALSE)
             {
                $sql = "INSERT into Customers(id,customer_name,customer_name_letterhead,customer_notes,systype,status,signaltype,verification,address,postcode,telephone,mobile,mobiletwo,email,mainarea    installation,Contract,expiration,SPA,nservice,maintenance,monitoring,MS,certdate) values('$data[0]','$data[1]','$data[2]''$data[3]','$data[4]','$data[5]','$data[6]','$data[7]','$data[8]','$data[9]','$data[10]','$data[11]','$data[12]','$data[13]','$data[14]','$data[15]','$data[16]','$data[17]','$data[18]','$data[19]','$data[20]','$data[21]','$data[22]','$data[23]','$data[24]')";
                mysql_query($sql) or die(mysql_error());
             }

             fclose($handle);
             echo "Successfully Imported";
         }
         else
         {
             echo "Invalid File";
         }   
    }?>
<form action='<?php echo $_SERVER["PHP_SELF"];?>' method='post'>

    Import File : <input type="file" name="sel_file" id="sel_file">
    <input type='submit' name='submit' value='submit'>

</form>

Upon clicking submit, nothing happens.

Here is a picture of my csv file: enter image description here

MySQL includes a feature called `LOAD DATA INFILE` which is perfect for importing CSV files. No processing required at all at the PHP end. — SDC, Jan 29 '13 at 14:09
possible duplicate of [how-to-upload-and-parse-a-csv-file-in-php][1] [1]: http://stackoverflow.com/questions/5593473/how-to-upload-and-parse-a-csv-file-in-php hope you can find your answer there. — nTony, Jan 29 '13 at 14:30
How many rows are in a typical file that you need to process? — peterm, Jan 29 '13 at 20:58

Bradly Spicer · Accepted Answer · 2013-01-29T21:16:59.840

I ended up searching for a video on YouTube which really did help. It ended up giving me this:

<?PHP   

if(isset($_POST['submit'])) 
{ 
    $file = $_FILES['file']['tmp_name']; 

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

    while(($fileop = fgetcsv($handle,1000, ",")) !== false) 
    { 

    $customer_name             = $fileop[0]; 
    $customer_name_letterhead     = $fileop[1]; 
        $customer_notes         = $fileop[2]; 
        $systype             = $fileop[3]; 
        $status             = $fileop[4]; 
        $signaltype             = $fileop[5]; 
        $verification             = $fileop[6]; 
        $address             = $fileop[7]; 
        $postcode            = $fileop[8]; 
        $telephone             = $fileop[9]; 
        $mobile             = $fileop[10]; 
        $mobiletwo             = $fileop[11]; 
        $email                 = $fileop[12]; 
        $mainarea             = $fileop[13]; 
        $installation            = $fileop[14]; 
        $Contract             = $fileop[15]; 
        $expiration             = $fileop[16]; 
        $SPA                 = $fileop[17]; 
        $nservice            = $fileop[18]; 
        $maintenance             = $fileop[19]; 
        $monitoring             = $fileop[20]; 
        $MS                 = $fileop[21]; 
        $certdate             = $fileop[22]; 


        $sql = mysqli_query($GLOBALS["___mysqli_ston"], "INSERT INTO Customers ( 
            customer_name, 
                    customer_name_letterhead, 
                    customer_notes, 
                    systype, 
                    status, 
                    signaltype, 
                    verification, 
                    address, 
                    postcode, 
                    telephone, 
                    mobile, 
                    mobiletwo, 
                    email, 
                    mainarea, 
                    installation, 
                    Contract, 
                    expiration, 
                    SPA, 
                    nservice, 
                    maintenance, 
                    monitoring, 
                    MS, 
                    certdate 
                    ) VALUES ( 
                   '$customer_name', 
           '$customer_name_letterhead', 
           '$customer_notes', 
           '$systype', 
           '$status', 
           '$signaltype', 
           '$verification', 
           '$address', 
           '$postcode', 
           '$telephone', 
           '$mobile', 
           '$mobiletwo', 
           '$email', 
           '$mainarea', 
           '$installation', 
           '$Contract', 
           '$expiration', 
           '$SPA', 
           '$nservice', 
           '$maintenance', 
           '$monitoring', 
           '$MS', 
           '$certdate' 
                   )"); 

                      if($sql) 
                      { 

                        echo 'Uploaded '. $sql . ' entries'; 
                      }      
    } 
} 

?> 
<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post" enctype="multipart/form-data">  
    <input type="file" name="file"> 
    <br> 
    <input type="submit" name="submit" value="Upload CSV File">  
</form>

Please, don't use mysql_* functions for new code. They are [deprecated as of PHP 5.5.0](http://php.net/manual/en/intro.mysql.php). Instead learn about [prepared statements](http://goo.gl/vn8zQ) and use either [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli). If you care to learn, here is good [PDO tutorial](http://goo.gl/vFWnC). — peterm, Jan 29 '13 at 20:59

score -1 · Answer 2 · answered Jan 29 '13 at 14:00

-1

not used it myself but have you tried this,

http://php.net/manual/en/function.str-getcsv.php

you could also try breaking the csv up into lines using explode on \n and then splitting it up further based on the ,.

once you have all the chunks push it into the database.

answered Jan 29 '13 at 14:00

Millard

1,157
1
9
19

If I'm correct I believe that is for printing out and viewing information from a CSV. I will have a read though thanks. Just saw your edit: I haven't got all that much experience with PHP, would you mind explaining that a bit further? Thanks! – Bradly Spicer Jan 29 '13 at 14:02

Creating a CSV Upload to Mysql

2 Answers2