using SELECT/WHERE and save the resultset into a text file 2

[zyrag]

hi, i want to save the data entered during the day. this i would want to happen every last office hour of the day, say every 5:00p.

SELECT * from tbl_names WHERE DATE=today

but, how am i going to save the result into a text file?

hope you can share your valuable ideas to a newbie like me...

thanks in advance

 

[sleipnir214]

MySQL's SELECT statement has additional clauses for writing to a file

SELECT * INTO myfile FROM mytable WHERE foo = 3

For more information:

http://www.mysql.com/doc/en/SELECT.html

Want the best answers? Ask the best questions:

http://www.tuxedo.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[KarveR]

common csv format :

SELECT * into outfile '/path/to/file/filename.csv' fields enclosed by '"' terminated by ',' lines terminated by '\n' FROM yourtable WHERE date = curdate(); ______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[zyrag]

thank you very much Karver. is it also possible to output from one or more table into 1 single csv file using the expression? say,

SELECT tbl1.col1, tbl2.col1, tbl2.col2 into outfile '/path/to/file/filename.csv' fields enclosed by '"' terminated by ',' lines terminated by '\n' FROM tbl1, tbl2, WHERE tbl1.col1=tbl2.col1 AND transdate = curdate();

thanks again...

 

[KarveR]

Yes, what you have above should work just fine. ______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[zyrag]

karver thanks a lot...

i tried to execute the command:

SELECT * INTO OUTFILE 'C:\\mysamp.txt'
FIELDS enclosed by '\"' terminated by ','
LINES terminated by '\n\r'
FROM USER;

The output seemed OK except that the newline and carriage return character doesn't seem to work. The output should have 5 rows but instead it contained only 1 text line.

why? what should be the right way to do it?

thanks again...

 

[danielhozac]

It's \r\n not \r\n. //Daniel

 

[KarveR]

Normally I just use \n , never had any probs under linux, sco or windoze ;-) ______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[zyrag]

got it right now... thanks to you guys.

 

[olloemre]

Does it need any special privilege to do this operation? I tried it on a remote Mysql server but it didn't work. why?

 

[KarveR]

FILE privileges must be set for MySQL > 3.22 on th esever config (or possibly just the target directory be world writeable) on the server. Cant remember exactly, so dee FILE in the manual.

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.