3

I am running the following query but not receiving the results I would like

SELECT * FROM `test`.`sales_flat_order_grid`  
 WHERE `created_at` BETWEEN DATE_SUB(CURDATE(), INTERVAL 14 DAY) 
   AND CURDATE()   
  INTO OUTFILE '/tmp/sales.csv' 
       FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY 'n';

This query displays the results in one excel row and it does not contain the MySQL table headers. Any ideas on how to fix this query?

Thanks

Mahmut Ali ÖZKURAN
  • 1,120
  • 2
  • 23
  • 28
user2744145
  • 57
  • 5
  • For one thing, you need to terminate by `'\n'` (EOL character) rather than `n` (the letter N). There is no MySQL magic to include column names as first row, this is manual and you need to build it in. See second answer of this question which may simplify things for you: http://stackoverflow.com/questions/5941809/include-headers-when-using-select-into-outfile – Patrick Moore Jan 27 '14 at 22:14

2 Answers2

1

You really are asking 2 questions here.

1) Why is my exported data all in 1 line.

Answer:

You are missing a \ in LINES TERMINATED BY 'n';

So it should be 

LINES TERMINATED BY '\n';

2) How do I get column headers in the 1st row.

Answer:

Either build it manually as suggested by Set Sail Media,
or
2.1) Try piping your query to the commandline client as shown here => https://stackoverflow.com/a/263000/325521
or
2.2) Use this little script described here => https://stackoverflow.com/a/9976449/325521

Community
  • 1
  • 1
Shiva
  • 20,575
  • 14
  • 82
  • 112
1

For one thing, you need to terminate by '\n' (EOL character) rather than n (the letter N).

Second, there is no MySQL magic to include column names as first row, this is manual and you need to build it in. See second answer of this question which may simplify things for you: Include headers when using SELECT INTO OUTFILE?

Community
  • 1
  • 1
Patrick Moore
  • 13,251
  • 5
  • 38
  • 63