CODEWITHSUNDEEP
mysqlsqlcsvexport-to-csv
user2744145
user2744145

Reputation: 57

MySQL export to CSV all in one line & no headers

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

Upvotes: 3

Views: 2395

Answers (2)

Shiva
Shiva

Reputation: 20935

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

Upvotes: 1

Patrick Moore
Patrick Moore

Reputation: 13344

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?

Upvotes: 1

Related Questions