error in importing csv file to mysql server

Question

I want to import csv file to mysql database and I am getting an error in it that I am not able to fix. please help me to find what is the issue..

my code is

    <?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);

$file = $_FILES['file']['tmp_name'];
if ($_FILES["file"]["error"] > 0)
{
echo "Error: " . $_FILES["file"]["error"] . "<br />";
}

else {


echo "hi";
        $q="LOAD DATA INFILE '$file' 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 "File data successfully imported to database!!"; 
   } 



mysql_close ( $connect );
}
?>

And the error is

Parse error: syntax error, unexpected '' ' (T_CONSTANT_ENCAPSED_STRING) in C:\xampp\htdocs\try.php

please help me to find the error and what should be the code here..

this is my csv file

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
3605600753,68,Deepali,Agni,deepali@trendspry.com,8800311993,8800311993,Deepali,Agni,8800311993,sushant Lok,Shopping Arc,GGN,Haryana,122001,India,Free shipping,Cash on Delivery,cod,0.50kg,28-05-2015,28-05-2015,bhg9,1299,0,0,1299,Pending,NA,NA,0,NA,A Beautiful  Black And Yellow Georgette Saree,BR19891153,Georgette and Jacquard Embroidery and Printed Casual and Office ,1,1299

score 0 · Accepted Answer · edited May 23 '17 at 11:43

0

You need to escape the newline character \n and the double quotation " in your query:

$q="LOAD DATA INFILE '$file' INTO TABLE tsd_orders
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'
    LINES TERMINATED BY '\\n' 
    IGNORE 1 LINES
    ...

Have a look at this SO post for a syntactically-correct way to use LOAD DATA inside a PHP script.

edited May 23 '17 at 11:43

Community

1
1

answered Jul 08 '15 at 05:06

Tim Biegeleisen

502,043
27
286
360

hey it works.. but couldnt import data – Swarnima Gupta Jul 08 '15 at 05:31
`but couldnt import data` This doesn't let me help you. Please give a description of the new problem. – Tim Biegeleisen Jul 08 '15 at 05:31
my code execute completelyn results "file imported succesfully" but when i check my database it shows "MySQL returned an empty result set (i.e. zero rows). (Query took 0.0014 seconds.)" – Swarnima Gupta Jul 08 '15 at 05:35
Can you post a portion of the input file? – Tim Biegeleisen Jul 08 '15 at 05:36
when i add this code if($_FILES['file']['type'] != 'text/csv'){ die('Unsupported filetype uploaded.'); } it gives me an error of unsupported csv file – Swarnima Gupta Jul 08 '15 at 05:44
How about `echo $_FILES['file']['type']`? – hd1 Jul 08 '15 at 05:59
OKK.. its application/vnd.ms-excel – Swarnima Gupta Jul 08 '15 at 06:05
but my file is "trendspry.csv". how can this happen?? – Swarnima Gupta Jul 08 '15 at 06:07
Can you email the csv file to me? TimBeagle@hotmail.com ... I suspect there may be strange delimeters or other characters in there which is throwing off MySQL. – Tim Biegeleisen Jul 08 '15 at 07:32
@SwarnimaGupta I just emailed you an updated version of the CSV file. I think your previous version became corrupted somehow. Let me know if it works for you. – Tim Biegeleisen Jul 08 '15 at 09:52
no, it also results.. unsupported file uploaded. – Swarnima Gupta Jul 08 '15 at 10:11
Use this: `FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'` ... Your csv file does _not_ have fields which are enclosed by quotes. Why are you specifying double quotes? – Tim Biegeleisen Jul 08 '15 at 10:32

error in importing csv file to mysql server

1 Answers1