help : LOAD DATA LOCAL INFILE

[Dave]

I'm trying to load a local file in to a database and have had no luck.


I have downloaded the table contents (1row) using phpAdmin and changed the id# and one
other field before trying to upload. To test out the script.

That file looks like this (dbdata.txt)

"2","public","company2","contact","street","suite" ,"city","province","postcode","countr
y","phone","fax","region","service","first name","surname","create date"


The script i'm trying to use looks like this.

$loadfile = "LOAD DATA LOCAL INFILE 'dbdata.txt' INTO TABLE tablename FIELDS TERMINATED
BY ',' ENCLOSED BY '\"'";
$result = mysql_query($loadfile) or die (" Invalid DATA LOAD query");

Both files are in the same directory and I am able to connect to the table and query it
within the rest of the script.

Any help you can give on this is appreciated.

Thanks,

[Tony]

Dave wrote:

> I'm trying to load a local file in to a database and have had no luck.

Try asking the MySQL server first - it should help point you in the right
direction.

$result = mysql_query($loadfile)
or die ("Error ".mysql_errno() ." : ".mysql_error() ."");

> I have downloaded the table contents (1row) using phpAdmin and
> changed the id# and one other field before trying to upload. To test
> out the script.
>
> That file looks like this (dbdata.txt)
>
>

"2","public","company2","contact","street","suite" ,"city","province","postco
de","countr

> y","phone","fax","region","service","first name","surname","create
> date"
>
>
> The script i'm trying to use looks like this.
>
> $loadfile = "LOAD DATA LOCAL INFILE 'dbdata.txt' INTO TABLE tablename
> FIELDS TERMINATED BY ',' ENCLOSED BY '\"'";
> $result = mysql_query($loadfile) or die (" Invalid DATA LOAD query");

This is what phpMyAdmin generated for a similar csv file:

$loadfile=
'LOAD DATA LOCAL INFILE
\'/path-to-your-server/dbdata.csv\' INTO TABLE
`tablename`
FIELDS TERMINATED BY \',\' ENCLOSED BY \'"\'
ESCAPED BY \'\\\\\' LINES TERMINATED BY \'\\r\\n\'';

// cue the result or tell us why not..
$result = mysql_query($loadfile)
or die ("Error ".mysql_errno() ." : ".mysql_error() ."");


Note the path-to-your-server may be something like
/home/htdocs/xxxxx/dbdata.txt


Hope that helps

--
Tony

[Andy Hassall]

On Mon, 04 Oct 2004 14:42:57 -0500, [email]me@here.com[/email] (Dave) wrote:

>I'm trying to load a local file in to a database and have had no luck.
>
>
>I have downloaded the table contents (1row) using phpAdmin and changed the id# and one
>other field before trying to upload. To test out the script.
>
>That file looks like this (dbdata.txt)
>
>"2","public","company2","contact","street","suite ","city","province","postcode","countr
>y","phone","fax","region","service","first name","surname","create date"
>
>
>The script i'm trying to use looks like this.
>
>$loadfile = "LOAD DATA LOCAL INFILE 'dbdata.txt' INTO TABLE tablename FIELDS TERMINATED
>BY ',' ENCLOSED BY '\"'";
>$result = mysql_query($loadfile) or die (" Invalid DATA LOAD query");
>
>Both files are in the same directory and I am able to connect to the table and query it
>within the rest of the script.

You haven't said what problems you're having. Use mysql_error() to get an
error message, and post this message.

Actually - put the error message into Google first - you are likely hitting
the security constraints placed on LOAD DATA LOCAL INFILE by MySQL itself.

--
Andy Hassall / <andy@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool

[Dave]

In article <rkk3m05f2m77m86jhfoiphgn21gbvj1bcg@4ax.com>, [email]andy@andyh.co.uk[/email]
says...

>
>On Mon, 04 Oct 2004 14:42:57 -0500, [email]me@here.com[/email] (Dave) wrote:
>

>>I'm trying to load a local file in to a database and have had no luck.
>>
>>
>>I have downloaded the table contents (1row) using phpAdmin and changed the

id# and one

>>other field before trying to upload. To test out the script.
>>
>>That file looks like this (dbdata.txt)
>>
>>"2","public","company2","contact","street","suit e","city","province","postco

de","countr

>>y","phone","fax","region","service","first name","surname","create date"
>>
>>
>>The script i'm trying to use looks like this.
>>
>>$loadfile = "LOAD DATA LOCAL INFILE 'dbdata.txt' INTO TABLE tablename FIELDS

TERMINATED

>>BY ',' ENCLOSED BY '\"'";
>>$result = mysql_query($loadfile) or die (" Invalid DATA LOAD query");
>>
>>Both files are in the same directory and I am able to connect to the table

and query it

>>within the rest of the script.

>
> You haven't said what problems you're having. Use mysql_error() to get an
>error message, and post this message.
>
> Actually - put the error message into Google first - you are likely hitting
>the security constraints placed on LOAD DATA LOCAL INFILE by MySQL itself.
>
>--
>Andy Hassall / <andy@andyh.co.uk> / <http://www.andyh.co.uk>
><http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool

I added the script above and the error i'm getting is.

Error 1148 : The used command is not allowed with this MySQL version

Thanks again