How to format results from a stored procedure?

Asked Jul 25 '14 at 20:13

Active Jul 25 '14 at 20:17

Viewed 98 times

I have the following table within my database. I need to write a store procedure that will send all the data to a text file. The following works, however, I would like to format in such way that every row in the database should correspond to a line in the text file. Here is my stored procedure and a picture of my database:

CREATE DEFINER=`root`@`localhost` PROCEDURE `outPutTweets`()
BEGIN

SELECT tweetID, accountName, tweet, DATE_FORMAT(Time,'%H:%i') INTO OUTFILE 'C:/xampp/htdocs/Dropbox/SportsMedia/ScrollbarProject/results.txt'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
From tweets ORDER by Time desc;
END

enter image description here

edited Jul 25 '14 at 20:17

cwallenpoole

79,954
26
128
166

asked Jul 25 '14 at 20:13

Alexander

and what is wrong with file you're getting with this stored proc? – Iłya Bursov Jul 25 '14 at 20:15
What do you mean, correspond to a line in the text file? You mean adding a line feed at the end? Can you show us an example of what you want. – Kermit Jul 25 '14 at 20:15
@Lashane everything is printed within the same line – Alexander Jul 25 '14 at 20:15
No, maybe your text editor does not understand `\n`, but row in database = line in file – Iłya Bursov Jul 25 '14 at 20:16
2

I thik your code is correct! If you are using windows try this `LINES TERMINATED BY '\r\n' ` – Athafoud Jul 25 '14 at 20:16
possible duplicate of [MySQL into outfile formatting](http://stackoverflow.com/questions/13750970/mysql-into-outfile-formatting) – Athafoud Jul 25 '14 at 20:17
@Lashane you are totally right, I just tested in a different text editor and it works the way I want – Alexander Jul 25 '14 at 20:18
@Athafoud The problem was my text editor – Alexander Jul 25 '14 at 20:18
@Athafoud I just added \r and it works in both of my text editors. And yeah my pc is a windows. thank you – Alexander Jul 25 '14 at 20:23

How to format results from a stored procedure?

0 Answers0