exporting data to CSV file using SELECT mysql query

Question

Usually, to export CSV reports from a mysql server, I connect & run a query on the server, save the results into a text file, and import the file to excel by specifying to separate the columns using the pipe delimited character.

I need to run a report on a table with a MEDIUMTEEXT column type, which contains comma characters, and line breaks (\n).

The line breaks and commas symbols are breaking the table layout.

SELECT `number`,REPLACE(`description`, ',', ''),  mr.`dateInserted` FROM 
`mr`  WHERE mr.dateInserted >= '2012-01-01' AND mr.dateInserted <= '2012-01-31'

I solved the comma issues by using the MySQL REPLACE function, however, how can I remove the line breaks from the results?

thenetimp · Accepted Answer · 2012-02-06 19:48:00Z

2

You can do this with an query, here's an example.

SELECT
  id,
  name,
  email
INTO
  OUTFILE '/tmp/result.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY ‘\\’
LINES TERMINATED BY '\n'
FROM users WHERE 1

answered Feb 6, 2012 at 19:48

thenetimp

9,9205 gold badges31 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

aporat Over a year ago

I think i tried that, the problem with that approach is that it saves the file locally, and it's an amazon RDS instance

jon · Accepted Answer · 2012-02-07 08:39:06Z

1

Char(13) is a carriage return

Char(10) is a line feed

Try nested replace functions for path of least resistance :)

SELECT 
    `number`, 
    REPLACE(REPLACE(REPLACE(`description`, ',', ''), CHAR(13),''), CHAR(10),''),
    mr.`dateInserted`
FROM
    `mr`
WHERE
    mr.dateInserted >= '2012-01-01' 
AND 
    mr.dateInserted <= '2012-01-31'

edited Feb 7, 2012 at 8:39

answered Feb 6, 2012 at 20:22

jon

6,2865 gold badges30 silver badges35 bronze badges

Comments

Yuriy Zubarev · Accepted Answer · 2012-02-06 19:49:51Z

1

You can also use a combination of a "view" and "mysqldump".

http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html

answered Feb 6, 2012 at 19:49

Yuriy Zubarev

2,88121 silver badges24 bronze badges

1 Comment

thenetimp Over a year ago

VERY NICE! I didn't know that!

Collectives™ on Stack Overflow

exporting data to CSV file using SELECT mysql query

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related