[Resolved] Error importing from .txt file

[jollyollie]

Ok, I'm making progress toward my final page, there's light at the end of the tunnel!

The last step in the process that I've been working on is to import a series of records from a pipe delimited text file into MySQL. The file is on my server. Here's what I've done and to what results.

$dbh=mysql_connect ("localhost", "ID", "Password") or die ('I cannot connect to the database because: ' . mysql_error());

$result = mysql_db_query("database","LOAD DATA INFILE '/tmp/sample.txt' INTO TABLE [i]table[/i] FIELDS TERMINATED BY '|' ESCAPED BY '\' LINES TERMINATED BY '\r\n'",$dbh) or die(mysql_error());

Here's the results:

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 ''' at line 2

Now, this is an improvement from the previous error that my version of MYSQL doesn't allow this, but I'm confused by the fact it doesn't matter what information is actually on Line 2. I can put in html info for a head, still the same error message with the same line number.

Any ideas?

[drew010]

try making
ESCAPED BY '\'
ESCAPED BY '\' instead.

[jollyollie]

Thanks for the suggestion. I managed to get the query to go through only to have 2 possible errors.

Using This query I get:

LOAD DATA INFILE '/tmp/xxx.txt' INTO TABLE xxx FIELDS TERMINATED BY '|' ESCAPED BY '\' LINES TERMINATED BY '\r\n'

The error message is:

#1045 - Access denied for user: 'xxx@localhost' (Using password: YES)

This query:

LOAD DATA LOCAL INFILE '/tmp/xxx.txt' INTO TABLE xxx FIELDS TERMINATED BY '|' ESCAPED BY '\' LINES TERMINATED BY '\r\n'

The error message is:

The used command is not allowed with this MySQL version

Seems like an Hosting issue, I'll take it up with them. Thanks for you help.

[jollyollie]

This was a hosting issue. I resolved it with them. Thanks everyone!