Page 1 of 1

Importing tab delimted data into mysql using php

Posted: Thu Dec 14, 2006 11:51 pm
by SoreE yes
Hi, I'd like to import a tab delimited file into mysql. The following code appears to work (it deletes the old data) until it hits the import sequence, whence I get the error message 'Invalide DATA LOAD Query'. What's up doc? By the way, this is a great site. I haven't needed to use it that often, fortunately, but the responses are usually spot on. I like the layout too, which is easy to use, and I like the way the formatting works.

Code: Select all

<?

# MySQL database User ID, Password and DB name
$sql_id = "root";
/*$sql_pwd = "";
$sql_db = "testimport";*/

# Connect to the database
	$db=mysql_connect ("localhost") or die(mysql_error());
	mysql_select_db("testimport") or die(mysql_error());

# Delete the current content of the table
$query = "DELETE FROM importtable";
$result=mysql_query($query) or die ("Invalid DELETE query");

# Optimize the current table (recover empty space)
$query = "OPTIMIZE TABLE importtable";
$result=mysql_query($query) or die ("Invalid OPTIMIZE query");

# Load local comma separated, fields enclosed by quotes text database - File has to be in the same directory of this file
$query = "LOAD DATA LOCAL INFILE 'testfile.txt' INTO TABLE importtable FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'";
$result=mysql_query($query) or die ("Invalid DATA LOAD query");


# Get how many records are present in the table now

$result = mysql_db_query('$sql_id',"SELECT * from importtable") or die ("Invalid SELECT query");
$rows_count = mysql_num_rows($result);

echo "Records: $rows_count"; mysql_free_result($result);

?>

Posted: Fri Dec 15, 2006 12:33 am
by Kieran Huggins
Remember: "Invalid DATA LOAD query" is YOUR error message, not PHP's or MySQL's.

One way to help debug this yourself is to use the MySQL_error() function:

Code: Select all

//  instead of
$result=mysql_query($query) or die ("Invalid DATA LOAD query"); 

//  use
$result=mysql_query($query) or die ("Invalid DATA LOAD query BECAUSE:<hr/>".MySQL_error($result)."<hr/>");
If you're still flummoxed, paste the resulting error here.

Cheers,
Kieran

Posted: Fri Dec 15, 2006 4:46 am
by SoreE yes
Thanks, that's a good tip to use mysql own error messengering system. The new error message was
  • Invalid DATA LOAD query BECAUSE:
    --------------------------------------------------------------------------------
    The used command is not allowed with this MySQL version
but I don't know what this refers to. Any ideas?

Posted: Fri Dec 15, 2006 10:15 am
by Kieran Huggins
Third result on Google for "mysql load data" says:
The "LOAD DATA LOCAL INFILE" statement will only work from a MySQL prompt on our local system. It will not work from any web-based tool such as phpMyAdmin, and will never pull a file in directly off your own computer.
There's also an example script that might work for you there:

http://www.modwest.com/help/kb6-253.html

Cheers,
Kieran

Posted: Fri Dec 15, 2006 7:01 pm
by SoreE yes
Hi, yes I saw that 3rd result from Google, but check out the following, which appears to contradict it, and which is where I got the php code from.

http://www.blue105.com/internet/support ... fline.html.

Regards, Mike

Jcart | I've corrected link

Posted: Fri Dec 15, 2006 9:04 pm
by Kieran Huggins
That link didn't work for me... I got non-loading images and no relevant content :-(

Did you try the example code on the Google result page I quoted?

Cheers,
Kieran