Importing csv into Mysql Error

dapcigar · May 26, 2014

Am trying to import a csv file into mysql. below is the code am using.

<?php

//connect to the database
include('mysql_connect.php');//select the table
//

if ($_FILES[csv] > 0) {

    //get the csv file
    $file = $_FILES[csv][tmp_name];
    $handle = fopen($file,"r");

    //loop through the csv file and insert into database
    do {
        if ($data[0]) {
            mysql_query("INSERT INTO requisition (department, contact_last, contact_email) VALUES
                (
                    '".addslashes($data[0])."',
                    '".addslashes($data[1])."',
                    '".addslashes($data[2])."'
                )
            ");
        }
    } while ($data = fgetcsv($handle,1000,",","'"));
    //

    //redirect
    header('Location: import.php?success=1'); die;

}

?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Import a CSV File with PHP & MySQL</title>
</head>

<body>

<?php if (!empty($_GET[success])) { echo "<b>Your file has been imported.</b><br><br>"; } //generic success notice ?>

<form action="" method="post" enctype="multipart/form-data" name="form1" id="form1">
Choose your file: <br />
<input name="csv" type="file" id="csv" />
<input type="submit" name="Submit" value="Submit" />
</form>

</body>
</html>

...........................................................................................................................................

On page load, it gives some erros

Notice: Use of undefined constant csv - assumed 'csv' in C:\wamp\www\orango\upload_data.php on line 7

Notice: Use of undefined constant size - assumed 'size' in C:\wamp\www\orango\upload_data.php on line 7

Notice: Undefined index: csv in C:\wamp\www\orango\upload_data.php on line 7

Notice: Use of undefined constant success - assumed 'success' in C:\wamp\www\orango\upload_data.php on line 43

I try to ignore the errors to see if it would work but it didn't save in the DB. please help me out

Ch0cu3r · May 26, 2014

You're getting those notices because $_FILES wont be populated with any data until the form has been submitted. To prevent the notices you need wrap your form processing code inside a condition example

if(isset($_FILES['csv']))
{
   // process form data
   // insert data from csv file into the database
}

You can automate the insertion of contents of the CSV file by using the MySQL LOAD DATA INFILE query clause. Example code

if (isset($_FILES['csv']))
{
    // import the contents of the csv file into the database
    $result = mysql_query(" LOAD DATA INFILE {$_FILES['csv']['tmp_name']}  # the tmp csv file
                            INTO requisition                               # the table to insert the data to
                            FIELDS TERMINATED BY ',' ENCLOSED BY '\''      # describe how the fields are separated and what character the values are enclosed by
                            IGNORE 1 LINES                                 # ignore the first line - which is usually used for the column headings
              ");

    // check the query did not fail
    if(!$result)
    {
        trigger_error('DB Error: Cannot import csv contents to database - ' . mysql_error());
    }

    // check that query did add the data to the table
    if(mysql_affected_rows())
    {
        header('Location: import.php?success=1'); exit;
    }
    // for some reason no data was added to the table
    else
    {
        echo 'Data was not added to the table!';
    }
}

Barand · May 26, 2014

Also

Use a while loop and not do..while() otherwise the first record isn't read until the end of the first iteration.
Use mysql_real_escape_string() instead of addslashes() (Better still, use mysqli or PDO)

Swarnima_Yagyasaini · July 15, 2015

sir i want to import a csv file to mysql. my program is right for only a particular csv file not for all. I want to do it for all the file that i select.

I used $filename = $_FILES['file']['tmp_name'] but it also didnt work.

this is my code

<?php 
if(isset($_POST['submit']))
{   
$connect = mysql_connect('127.0.0.1','root','');
if (!$connect) {
 die('Could not connect to MySQL: ' . mysql_error());
 }
$cid =mysql_select_db('ts_order_track',$connect);


        $q="LOAD DATA LOCAL INFILE '"trendspry.csv"' REPLACE INTO TABLE tsd_orders
        FIELDS TERMINATED BY ',' ENCLOSED BY '\"'
        LINES TERMINATED BY '\n' 
        IGNORE 1 LINES
        ( order_id, customer_id, firstname, lastname, email, telephone, mobile, shipping_firstname, shipping_lastname, shipping_mobile, shipping_address_1, shipping_address_2, shipping_city, shipping_zone, shipping_postcode, shipping_country, shipping_method, payment_method, payment_code, weight, date_added, date_modified, import_order_id, sub_total, shipping_charges, tax_charges, total, order_status, courier, awb_code, coupon_amount, coupon_code, product_name, product_sku, product_model, product_quantity, product_price )";
        $s=mysql_query($q, $connect );
       echo "$q";
echo "File data successfully imported to database!!"; 
mysql_close ( $connect );
}
?>

I didnt understand how to use $_FILES['file']['tmp_name'] here.

plz help me..

thanx in advance.

Ch0cu3r · July 16, 2015

See reply #2, above

Sign In

Importing csv into Mysql Error

Recommended Posts

dapcigar

Link to comment

Share on other sites

Ch0cu3r

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

Swarnima_Yagyasaini

Link to comment

Share on other sites

Ch0cu3r

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information