phpmyadmin exporting to csv for excel

2020-04-02 08:12发布

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.

phpmyadmin export tab

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

phpmyadmin export for normal csv

2条回答
不美不萌又怎样
2楼-- · 2020-04-02 09:04

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';
查看更多
forever°为你锁心
3楼-- · 2020-04-02 09:08

Just check the Put columns names in the first row checkbox above the Go button.

查看更多
登录 后发表回答