CODEWITHSUNDEEP
mysqlcsv
Trung Bún
Trung Bún

Reputation: 1147

MySQL: Dynamically export CSV file with headers

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");

Upvotes: 4

Views: 13839

Answers (2)

ns15
ns15

Reputation: 8734

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;

Upvotes: 4

Jit Dhar
Jit Dhar

Reputation: 192

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

Upvotes: 2

Related Questions