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.

Solution

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';

Comments

[user2960754]

hi. i'm not using the built-in export function from phpmyadmin. instead I type the syntax myself to produce the format I want, it's just that I don't know how to add the column header...

[user2960754]

hi, what I want is to export the table data together with the contents into readable csv for excel. the syntax I run (as on 1st post) did export the db data, but without the header. What I need to include the header(column name)...

[sam_rox]

@ThatMSG In INTO OUTFILE 'C:/test.csv' does this test.csv automatically gets created or shoud we have created a blank spreadsheet with the name test.csv for the data to be exported to

[ThatMSG]

If mysql/apache or what have you are using to export the data has the right to write to the selected directory, then the file will be created automatically