1

I know how to export multiple columns to a csv file. using:

SELECT `col1`, `col2`
FROM table
INTO OUTFILE 'C:/filename.csv'
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';

When I open the file using excel, everything is fine, each record is in a line. But, when I open it using notepad, I find all records are printed next to each other without line separator. How can I make the output of the query to be printed as one record per line and each column separated by comma ??

Jury A
  • 19,192
  • 24
  • 69
  • 93

2 Answers2

5

\n is a line break under linux, but windows uses \r\n

Accordingly, this code does what you want

SELECT `col1`, `col2`
FROM table
INTO OUTFILE 'C:/filename.csv'
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n';

Many other tools like Excel, Notepad++ or WordPad can read file both with \n and \r\n, so they are not affected by this change and display the file correctly both with your original code and this modification.

Jannis Froese
  • 1,347
  • 2
  • 10
  • 23
0

'\n' is used as Line separator in Unix/Linux, Line separator for Windows should be '\r\n'.

Jacky
  • 58
  • 7