OUTPUT RESULTS TO A FILE 1

[PrgrmsAll]

Is there any way in MySQL to direct output from a query to a textfile? I did a search on this and found the following code snippet here in tek-tips, however, I have been unable to make it work:

SELECT * INTO OUTFILE MyOutput.TXT
FIELDS TERMINATED BY ","
FROM MyFile
WHERE Condition = MyCondition

Any thoughts/advice would be appreciated. Thank you in advance!

 

[ethorn10]

What happens when you run that? Error messages? Unexpected values in the text file?

 

[PrgrmsAll]

Replies with error message, complaining about syntax 'near' MyOutput.txt. Should this be a literal string?

 

[ethorn10]

Yeah...it should be something like:

SELECT * INTO OUTFILE 'MyOutput.TXT'
    FIELDS TERMINATED BY ","
    FROM MyFile
    WHERE Condition = MyCondition

that will dump the file into the current directory of the mysql server. if you want it elsewhere, it needs to be either the full or relative path as well.

 

[ethorn10]

keep us posted of any progress you make.

 

[PrgrmsAll]

Thanks! I am closer but now I am getting a "ERROR 1045 (28000): Access denied for user 'ctedbadmin'@'%' (using password: YES)" message.

 

[ethorn10]

according to the documentation, the user must have the FILE privilege to use this syntax.

 

[PrgrmsAll]

how do i set those?

 

[ethorn10]

from the command line:

GRANT FILE ON databasename.* TO ctedbadmin@'%';

or if you have a password for ctedbadmin, then you could type:

GRANT FILE ON databasename.* TO ctedbadmin@'%' IDENTIFIED BY 'password';

 

[ethorn10]

any luck?

 

[PrgrmsAll]

What I actually ended up doing was downloading the MyODBC drivers from mysql.com. With the odbc drivers installed, I was able to connect to MySQL from a dts package on SQLServer and import that data directly into my database. This is ultimately what I wanted to do, I guess I was making it more difficult for myself. Thank you for your posts, though, they were much appreciated.