insert data from csv file to mysql with php

Question

i want to insert data from a csv file into my mysql database with php. But i dont know what i doing wrong.

This is my php code

if ($_FILES[csv][size] > 0){
$csv_file = $_FILES[csv][tmp_name]; // Name of your CSV file
$csvfile = fopen($csv_file, 'r');
$theData = fgets($csvfile);


$i = 0;
while (!feof($csvfile)) {
$csv_data[] = fgets($csvfile, 1024);
$csv_array = explode(",", $csv_data[$i]);

$insert_csv = array();
$insert_csv['id'] = $csv_array[0];
$insert_csv['name'] = $csv_array[1];
$insert_csv['email'] = $csv_array[2];

if(!empty($insert_csv['email'])){
$query = "INSERT INTO contacts(id,name,email)
VALUES('','".$insert_csv['name']."','".$insert_csv['email']."')";
$n=mysqli_query($database->connection,$query);
}

$i++; 
}

fclose($csvfile);

}

This is my csv looks like.

id---- name ------- email

1 ---- [email protected]

2 ---- user2 --------blah

3------ user 3 ------ blah

When i run this code my mysql results are

in email table = ##0.00 "TL")$# en in my name table= also ##0.00 "TL")$#;

What do i wrong?

Not sure if you need to do all that looping and manipulation in php - why not use the native functionality of LOAD DATA INFILE? dev.mysql.com/doc/refman/5.1/en/load-data.html You can then test the statement on your database isntance first to make sure it works — Steph Locke
– Steph Locke, Commented Dec 10, 2013 at 11:42

user188654 · Accepted Answer · 2013-12-10 11:57:35Z

1

You might want to use MySQL to do the whole loading process with the LOAD DATA INFILE statement.

if($_FILES['csv']['error'] === UPLOAD_ERR_OK && $_FILES['csv']['size'] > 0) {
    $query = "LOAD DATA INFILE '" . $_FILES['csv']['tmp_name'] 
    . "' INTO TABLE contacts FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n' (id, name, email);";

    if(!mysqli_query($query)){
        die('Oops! Something went wrong!');
    }
}

If required you can tweak the loading parameters (FIELDS TERMINATED BY, ENCLOSED BY, LINES TERMINATED BY).

Do take note that if you use this approach your temporary file needs to be stored in a place where its accessible by the MySQL server (like /tmp).

answered Dec 10, 2013 at 11:57

user188654

Sign up to request clarification or add additional context in comments.

Comments

MarcoS · Accepted Answer · 2013-12-10 11:39:44Z

0

To start with, I think you should remove the first

$data = fgetcsv($getfile, 1000, ",");

line, outside of the while loop...

answered Dec 10, 2013 at 11:39

MarcoS

17.8k27 gold badges101 silver badges189 bronze badges

Comments

Siraj Khan · Accepted Answer · 2013-12-10 11:58:34Z

Please try like this as a example , it should work for you as you want

I think you missed qoutes in "

$query = "INSERT INTO contacts(id,name,email)
VALUES('".$col1."','".$col2."','".$col3."')";

"

<?php

    $csv_file = 'C:\wamp\www\stockmarket\test.csv'; // Name of your CSV file with path
    if (($getfile = fopen($csv_file, "r")) !== FALSE) { 
            $data = fgetcsv($getfile, 1000, ",");
            while (($data = fgetcsv($getfile, 1000, ",")) !== FALSE) {
             $num = count($data); 
             for ($c=0; $c < $num; $c++) {
                 $result = $data; 
                 $str = implode(",", $result); 
                 $slice = explode(",", $str);
                 $col1 = $slice[0]; 
                 $col2 = $slice[1];
                 $col3 = $slice[2]; 

    // SQL Query to insert data into DataBase

    $query = "INSERT INTO contacts(id,name,email)
    VALUES('".$col1."','".$col2."','".$col3."')";

    $s=mysql_query($query, $connect ); 
         }
       } 
      }
    ?>

Collectives™ on Stack Overflow

insert data from csv file to mysql with php

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related