2 Replies - 3519 Views - Last Post: 05 February 2011 - 03:04 AM Rate Topic: -----

#1 guest101001  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 04-February 11

.csv file contents to mysql table

Posted 04 February 2011 - 12:39 PM

hi

and big hello to the community. :)

I'm trying to implement a test harness script which uploads the contents of a .csv file into a MySQL database table (tbl_products). Ive had a look around the net and found a lot of stuff that doesn't work! The contents of the.csv file are stored in a MS excel document like so. So the individual field values are stored in their own cells. Ive taken the headers (prod_id, prod_name, etc...) out for simplicity. Ive managed to successfully import this in phpmyadmin - but that's not what I'm trying to achieve here! Ive tried a couple of scripts (script1, script2, say), with one coming from a third party:

<?php #db_connect.php
$mysqli = new mysqli("localhost", "root", ";;;;;;;;;;", "catalogue");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$db=$mysqli->select_db("catalogue");

if(!$db) {  
	die('Cant select db at this time!');
	}




<?php #script1.php
include('db_connect.php');
	
$mysqli->query("LOAD DATA INFILE 'tbl_products.csv'
INTO TABLE tbl_products
FIELDS TERMINATED BY ','
FIELDS ENCLOSED BY '\"',
FIELDS ESCAPED BY '\"'
LINES TERMINATED BY 'auto' //I'm thinkin values from phpmyadmin here!

				(
				product_id,
				product_name,
				product_category,
				product_description,
				product_price
				)", $db)

 or die("Import Error: " . mysqli_error($mysqli));



<?php #script2.php

$file = file_get_contents('tbl_products.csv', FILE_USE_INCLUDE_PATH);
$lines = explode("\n", $file); //array(18)

$fields = explode(",",$lines[0]);
echo '<pre>';
print var_dump($fields); // array(5)
echo '</pre>';


$query = "INSERT INTO tbl_products (prod_id, prod_name, 
				prod_category, prod_description, prod_price
					) VALUES ";
$i = 0;
$arr = array();
for($arr = explode(",", $lines[$i]); $i <= count($lines); $i++) {
  $arr = array_walk($arr=array(), "mysqli_real_escape_string($arr)");
  list($field1, $field2, $field3, $field4,$field5) = explode(",",$arr);
  $query .= "('$field1', '$field2', '$field3','$field4', '$field5'),";
  }


$query = rtrim(", ", $query); //remove trailing comma

var_dump($query);


Now, if I'm honest with you, script1.php appeals to me more, the code is simpler and I'm told it's quicker! Script2.php is better for validation, so it's a question of preference. Truth is, i can't get either of them to work!

I've tried experimenting with script1, with the following parameters:

FIELDS TERMINATED BY '%'
FIELDS ENCLOSED BY '%',
FIELDS ESCAPED BY '%'
LINES TERMINATED BY '%'




where (%) is any of the following: (';'), ('), (") etc...

From script2, query would eventually be inserting stuff into the db - i'm echoing it here as a test!

OK, questions:

Running script2

Warning: array_walk() expects parameter 2 to be a valid callback, function 'mysqli_real_escape_string(Array)' not found or invalid function name in C:\Program Files\Apache Software Foundation\Apache2.2\htdocs\PHP\csvUploaderScript\explode.php on line 18




please, can someone make mysqli_real_escape_string be a valid callback?

I'm going to think some more about this....brb

This post has been edited by guest101001: 04 February 2011 - 12:40 PM


Is This A Good Question/Topic? 0
  • +

Replies To: .csv file contents to mysql table

#2 init.d.httpd  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 31
  • View blog
  • Posts: 132
  • Joined: 02-February 11

Re: .csv file contents to mysql table

Posted 04 February 2011 - 12:47 PM

What error are you getting from script one?

Are you sure your host/version of mysql is allowing load data infile command? Some don't.


also try
lines terminated by '\r'"

Was This Post Helpful? 0
  • +
  • -

#3 guest101001  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 04-February 11

Re: .csv file contents to mysql table

Posted 05 February 2011 - 03:04 AM

View Postinit.d.httpd, on 04 February 2011 - 12:47 PM, said:

What error are you getting from script one?

Import Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FIELDS ENCLOSED BY '"', FIELDS ESCAPED BY '\' LINES TERMINATED BY ' ' (' at line 4


Quote

Are you sure your host/version of mysql is allowing load data infile command? Some don't.


Reveals MySQL 5.0.75.

Quote

also try
lines terminated by '\r'"


No joy!
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1