phpmyadmin exporting to csv for excel

Question

I'm having problem using xampp (4.1.6) phpmyadmin export function. When I export, I select csv for excel format, but it will return the data like:

1;"data1";"0";"6828";"2014-03-13 13:54:26";"2014-03-13 13:54:26";"file";;"2014-03-14 14:45:51"

So I tried the syntax:

SELECT * FROM `directory_listing` 
INTO OUTFILE 'C:/test.csv' 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' 
LINES TERMINATED BY '\n'

Now the format is the same as the table view as in mysql table. Problem is, I want to display the column name on 1st row. How can i do that?

By the way, below is the interface of my phpmyadmin export function.

I tried to export it as normal csv, but it still return the same. even worse, ALL the data is put inside 1st row.

Answer 1

You almost got it right, you just have to check the "Put colums names in first row" checkbox and then start the export. That should do the job.

EDIT::

Since your doin it your self you can get the fieldnames with this MYSQL query:

    SELECT `COLUMN_NAME` 
FROM `INFORMATION_SCHEMA`.`COLUMNS` 
WHERE `TABLE_SCHEMA`='yourdatabasename' 
    AND `TABLE_NAME`='yourtablename';