MySQL: Dynamically export CSV file with headers

Question

Is there anyway to export data in MySQL table with column headers?

I find a way to do this by hard-coding the headers into query but if there are about 60 or even 100 columns in a table then it is impossible.

I tried the query below but I can't get the result as the first part of the query return a concatenated string of all headers in the table. It doesn't give me a desired result:

(select concat(group_concat(COLUMN_NAME separator ','), "\n")
    from information_schema.COLUMNS
    where table_name = '<table name>'
    and table_schema = '<DB name>'
    order by ORDINAL_POSITION) 
union all
(select * from <table name> into outfile "E:\\test.csv" fields terminated by "," lines terminated by "\n");

ns15 · Accepted Answer · 2018-06-27 15:36:51Z

There are few ways to fetch the field names of table in mysql, but below method works the best for outfile.

The filename of the csv file is dynamically created based on the datestamp, and for this prepared statement is used.

-- ensure mysql user has write permission on below location
SET @effectiveFileName = CONCAT('/home/myhome/temp-dev/', 'mytable','_', DATE_FORMAT(NOW(), '%Y-%m-%d'), '.csv');
-- group concat default is 1024, to avoid field names getting truncated we increase this value
SET SESSION group_concat_max_len = 10000;

SET @queryStr = (
    SELECT
    CONCAT('SELECT * INTO OUTFILE \'',
        @effectiveFileName,
        '\' FIELDS TERMINATED BY \',\' OPTIONALLY ENCLOSED BY \'"\' LINES TERMINATED BY \'\n\' FROM (SELECT ',
        GROUP_CONCAT(CONCAT('\'', COLUMN_NAME, '\'')),
        'UNION ALL SELECT * FROM myschema.mytable WHERE myschema.mytable.myfield <=\'',
        CURDATE(),
        '\') as tmp')
        FROM
            INFORMATION_SCHEMA.COLUMNS
        WHERE
            TABLE_NAME = 'mytable' AND
            TABLE_SCHEMA = 'myschema'
        ORDER BY ORDINAL_POSITION
    );
PREPARE stmt FROM @queryStr;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Below is simple version of above query with static file name.

-- group concat default is 1024, to avoid field names getting truncated we increase this value
SET SESSION group_concat_max_len = 10000;
SELECT 
CONCAT('SELECT * INTO OUTFILE \'/home/myuser/myfile.csv\' FIELDS TERMINATED BY \',\' OPTIONALLY ENCLOSED BY \'"\' ESCAPED BY \'\' LINES TERMINATED BY \'\n\' FROM (SELECT ',
        GROUP_CONCAT(CONCAT('\'', COLUMN_NAME, '\'')),
        ' UNION select * from YOUR_TABLE) as tmp')
FROM
    INFORMATION_SCHEMA.COLUMNS
WHERE
    TABLE_NAME = 'YOUR_TABLE'
    AND TABLE_SCHEMA = 'YOUR_SCHEMA'
ORDER BY ORDINAL_POSITION;

Jit Dhar · Accepted Answer · 2015-07-17 12:11:17Z

2

(SELECT 'Order Number','Order Date','Status')
UNION 
(SELECT orderNumber,orderDate, status
FROM orders
INTO OUTFILE 'C:/tmp/orders.csv'
FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"'
LINES TERMINATED BY '\r\n');

answered Jul 17, 2015 at 12:11

Jit Dhar

19210 bronze badges

5 Comments

Trung Bún Over a year ago

if there are 100 fields in the table, what would you do?

Jit Dhar Over a year ago

i think this is the only way.

Jit Dhar Over a year ago

and you need to add all the field in this way.

Vatev Over a year ago

If you want to SELECT ... INTO OUTFILE... you will need to have all the fields in the query. If you don't want to list them by hand you can use a script to generate the query.

Trung Bún Over a year ago

I think there are no other ways to do with dynamic exporting without using script to generate query. If running inside MySQL workbench then hard-coding is the only way..

Collectives™ on Stack Overflow

MySQL: Dynamically export CSV file with headers

2 Answers 2

Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related