Clever Geek Handbook

Uploading csv file to mysql via php

I know how to load the csv file into mysql database, but only via cmd. I want to know how to upload a csv file to mysql database using php form and ignore some excel information and only start importing from a certain line.? kindly help me.

+3


source to share


5 answers


(PHP 4, PHP 5)

fgetcsv



See php manual http://php.net/manual/en/function.fgetcsv.php

<?php
$row = 1;
if (($handle = fopen("test.csv", "r")) !== FALSE) {
    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
        $num = count($data);
        echo "<p> $num fields in line $row: <br /></p>\n";
        $row++;
        for ($c=0; $c < $num; $c++) {
            echo $data[$c] . "<br />\n";
        }
    }
    fclose($handle);
}
?>
+6


source


Try to make it work well, you can add as many values ​​as possible depending on the number of columns you have in the CSV file. Then, in your HTML code, put the loading syntax in a tag.

** 



$fname = $_FILES['csv_file']['name'];     
$chk_ext = explode(".",$fname);             

        $filename = $_FILES['csv_file']['tmp_     name'];   
$handle = fopen($filename, "r");      
if(!$handle){
die ('Cannot open file for reading');
}      
              while (($data = fgetcsv($handle,     10000, ",")) !== FALSE)
{
          $query = "INSERT INTO tablename       (col1_csv, col2_csv)
values ('$data[0]', '$data[1]');
mysql_query($query) or die(mysql_error    ());
}
 fclose($handle);
?>

**

+2


source


You can use MySQL statement LOAD DATA INFILE

to bulk input thousands of records at once. PHP can handle file upload. The PHP code will look like:

$query = sprintf("
    LOAD DATA LOCAL INFILE '%s'
    INTO TABLE `table1`
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'
    LINES TERMINATED BY '\\r\\n'
    IGNORE 1 LINES
    ",
    mysql_real_escape_string($FILES["file1"]["tmp_name"])
);

The keyword LOCAL

should allow you to bypass some security restrictions. Modify the FIELDS TERMINATED BY

and parameters LINES TERMINATED BY

to match the delimiters used by excel during export. IGNORE 1 LINES

tells MySQL to skip header lines.

Note. Excel doesn't seem to use the escape character; but it will (i) include fields containing ,

and "

, with "

(ii) use ""

to avoid single "

internal data. I believe MySQL will understand this encoding correctly and import data.

+1


source


You can use the LOAD DATA INFILE statement with the IGNORE ... LINES option, which you can use from both the command line and PHP.

0


source


try this:

 $filename=$_FILES["upload_file"]["name"];
 $extension = end(explode(".",$filename));
 if ($extension=='csv') {
     $tmp_file=$_FILES["upload_file"]["tmp_name"];
     $handle = @fopen($tmp_file, "r");
     //specify your own database connection parameter
     $db = new PDO('mysql:host=localhost;dbname=demo','user','password');
     $stmt = $db->prepare("INSERT INTO writers (writer_name, writer_email) VALUES (?, ?)");
     if ($handle) {
        while (($buffer = fgets($handle, 4096)) !== false) {
            $array=explode(",",$buffer);
            $count=1;
            foreach ($array as $value) {
                $stmt->bindParam($count, $value);
                $count++;
            }
            $stmt->execute();
        }
        if (!feof($handle)) {
            echo "Error: unexpected fgets() fail\n";
        }
        fclose($handle);
        }
        $db = null;
        echo "<p>Success</p>";
    }
    else {
        $error="<p style='color:red;'>Invalid file type</p>";
    }

Refer to http://pradipchitrakar.com.np/programming/upload-csv-mysql-php/

0


source






More articles:


All Articles