csv file import to mysql database using php facing error to import -
importing cvs file (microsoft office excel comma separated values file (.csv)) in phpmyadmim using php script. when importing csv file not importing database. line terminate "," please suggest me how make line terminate "auto", attaching sample file.
i sharing csv file link http://www.soniinfotech.com/product-data.csv
after aome suggestions , correction getting issue. actual cvs file size 106700 rows , insert 81499 rows. had insert files sever times it's total rows 81499 insert. please me fix issue how can insert rows database.
<?php set_time_limit(2400); $mysqli = new mysqli("localhost" , "user" , "pass" , "db"); // check connection if (mysqli_connect_errno()) { echo "failed connect mysql: " . mysqli_connect_error(); } $table_name= "store"; $csv_file = "import.csv"; // name of csv file $csvfile = fopen($csv_file, 'r'); $field_csv = array(); $i = 0; while (($csv_data = fgetcsv($csvfile, 0, ",")) !== false) { if($i==0) { $i++; continue; } // exclude first line in csv file. $field_csv['productid'] = $csv_data[0]; // 1 $field_csv['title'] = $csv_data[1]; // 2 $field_csv['description'] = $csv_data[2]; // 3 $field_csv['imageurlstr'] = $csv_data[3]; // 4 $field_csv['mrp'] = $csv_data[4]; // 5 $field_csv['price'] = $csv_data[5]; // 6 $field_csv['producturl'] = $csv_data[6]; // 7 $field_csv['categories'] = $csv_data[7]; // 8 $field_csv['productbrand'] = $csv_data[8]; // 9 $field_csv['deliverytime'] = $csv_data[9]; // 10 $field_csv['instock'] = $csv_data[10]; // 11 $field_csv['codavailable'] = $csv_data[11]; // 12 $field_csv['emiavailable'] = $csv_data[12]; // 13 $field_csv['offers'] = $csv_data[13]; // 14 $field_csv['discount'] = $csv_data[14]; // 15 $field_csv['cashback'] = $csv_data[15]; // 16 $field_csv['size'] = $csv_data[16]; // 17 $field_csv['color'] = $csv_data[17]; // 18 $field_csv['sizeunit'] = $csv_data[18]; // 19 $field_csv['sizevariants'] = $csv_data[19]; // 20 $field_csv['colorvariants'] = $csv_data[20]; // 21 $field_csv['stylecode'] = $csv_data[21]; // 22 $query = "insert $table_name set productid = '".$field_csv['productid']."',title = '".$field_csv['title']."',description = '".$field_csv['description']."',imageurlstr = '".$field_csv['imageurlstr']."',mrp = '".$field_csv['mrp']."',price = '".$field_csv['price']."',producturl = '".$field_csv['producturl']."',categories = '".$field_csv['categories']."',productbrand = '".$field_csv['productbrand']."',deliverytime = '".$field_csv['deliverytime']."',instock = '".$field_csv['instock']."',codavailable = '".$field_csv['codavailable']."',emiavailable = '".$field_csv['emiavailable']."',offers = '".$field_csv['offers']."',discount = '".$field_csv['discount']."',cashback = '".$field_csv['cashback']."',size = '".$field_csv['size']."',color = '".$field_csv['color']."',sizeunit = '".$field_csv['sizeunit']."',sizevariants = '".$field_csv['sizevariants']."',colorvariants = '".$field_csv['colorvariants']."',stylecode = '".$field_csv['stylecode']."' "; mysqli_query($mysqli,$query); } fclose($csvfile); echo "csv data imported table!!"; // close connection $mysqli->close(); ?>
there number of issues code provided:
1. not specify maximum line width
specify length parameter (second parameter) fgetcsv() if know enough read whole line. otherwise use 0 (zero) let php automatically detect line breaks. (it intelligently use new lines break lines avoiding line breaks might happen within csv quoted values)
fgetcsv($csvfile, 0, ",") 2. escape strings before inserting db
when inserting string values mysql, need escape them properly. if don't that, , string contains quote, may break sql query resulting in failed inserts.
using non-escaped strings poses risk of sql-injection attack.
to escape strings, use mysqli_real_escape_string() function.
i.e.:
$field_csv['productid'] = mysqli_real_escape_string( $mysqli, $csv_data[0] ); // 1 $field_csv['title'] = mysqli_real_escape_string( $mysqli, $csv_data[1] ); // 2 $field_csv['description'] = mysqli_real_escape_string( $mysqli, $csv_data[2] ); // 3 $field_csv['imageurlstr'] = mysqli_real_escape_string( $mysqli, $csv_data[3] ); // 4 // ... etc. 3. use mysqli_error() debug possible errors sql queries
if don't check errors on sql queries, you're running blind. if wrong happens, might not notice it.
always check return value of mysqli_query(). (it return boolean false on failed query) , print out error, using mysqli_error()
if ( ! mysqli_query( $mysqli,$query ) ) { printf( "error: %s\n", mysqli_error( $mysqli ) ); }
Comments
Post a Comment