Importing tab delimted data into mysql using php
Posted: Thu Dec 14, 2006 11:51 pm
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);
?>