mysql select into outfile without any line termination character

Question

I try to print all the data from one field from a table into a file witout any line/field termination character. I would simply like to have all the rows concatenated and printed as one line in the output line. Here how I am doing it:

CREATE TABLE tbl (txt VARCHAR(100));
INSERT INTO tbl VALUES ("text1");
INSERT INTO tbl VALUES ("text2");
INSERT INTO tbl VALUES ("text3");

SELECT txt FROM tbl;

SELECT txt INTO OUTFILE 'test.txt' LINES TERMINATED BY '' FROM tbl;

Unfortunely in the output I get tabs:

text1   text2   text3

if I add

FIELDS TERMINATED BY ''

then all the 100 characters are printed into file (VARCHAR(100)

text1                                                                                               text2                                                                                               text3

What should I do to get everything contcatenated as:

text1text2text3

Many thanks in advance!

score 2 · Answer 1 · answered Oct 07 '13 at 16:27

2

Assuming there are no spaces in text column itself, try SELECT REPLACE(GROUP_CONCAT(txt),',','') AS txt INTO OUTFILE 'test.txt' FROM tbl ;

If there are spaces in data then wrap one more REPLACE( ,' ','') around

if you run into limit (how many rows are to be GROUP_CONCATinated then it's best to use following if you are using Unix like OS

cat 'test.txt' | tr -d '\t'

answered Oct 07 '13 at 16:27

Parag

51
1

Hi Parag, thank you for your help! I actually was able to solve my problem thanks to your suggestion. I am going to post my sollution below in a second. I didn't undertstand your comment about SPACES in the text column. Why would SPACES matter if the default separator used by group_by is COMMA ',' and even if it was SPACE then why would wrapping it into one more REPLACE help? I need to do everything on the database level, so using tr is not an option. Anyway I am on Windows, so I couldn't even use it if I wanted to. Thank you once more for your help! – Mateusz Fenske Oct 08 '13 at 10:47

score 1 · Answer 2 · answered Oct 08 '13 at 11:09

Thanks to @Parag I was able to solve my problem, by using:

SELECT GROUP_CONCAT(txt SEPARATOR '') INTO OUTFILE 'test.txt' LINES TERMINATED BY '' FROM tbl;

This way I didn't have to wrap everything around with REPLACE and didn't bother if the text contains the default separator used by GROUP_CONCAT, which is COMMA ',' or not.

One important thing worth remembering is that GROUP_CONCAT() has a limit of maximum length, which you probalby run into when your fields are long. In my case the txt fields are over 900 characters, so I had to use:

SET SESSION group_concat_max_len = 1000000

This did the trick, but I wonder if there is any better sollution?

score 0 · Answer 3 · answered Sep 24 '14 at 15:05

0

Use a DUMPFILE:

CREATE TABLE tbl (txt VARCHAR(100));
INSERT INTO tbl VALUES ("text1");
INSERT INTO tbl VALUES ("text2");
INSERT INTO tbl VALUES ("text3");

SELECT txt FROM tbl;

SELECT txt INTO DUMPFILE 'test.txt';

answered Sep 24 '14 at 15:05

StackG

2,730
5
29
45

mysql select into outfile without any line termination character

3 Answers3