Adding column names to OUTFILE
ok I am using OUTFILE to dump a table to a CSV file. I would like to add column names in the first row........ah not so easy.
I am nearly there I think.
1) I have a command that returns a comma delimited string of the column names
2) I have the data written ok to a CSV file
3) My plan was to put the column names in a variable and select it then use a union to the data. Got stuck here though.
Anyone with some skills able to help?
call export_csv('log_streamer_01','2013-10-01','2013-10-02');
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `export_csv`(sTable CHAR(20), sStart datetime, sStop datetime)
BEGIN
declare t varchar(1000);
SET t := concat("SELECT CONCAT(GROUP_CONCAT(COLUMN_NAME SEPARATOR ';'), '\n')
FROM INFORMATION_SCHEMA.COLUMNS",
"WHERE TABLE_SCHEMA = 'scantrol_db' AND TABLE_NAME = ", sTable, "
GROUP BY TABLE_NAME");
SET @myCommand := concat(
"SELECT * into OUTFILE 'D:/HMI_Server_Data/Database/", sTable,"_", -- edit fields (*) or (column1, column2) -- edit the location -- edit table name
DATE_FORMAT(now(),'%Y.%m.%d_%H.%i.%s'), ".csv'",
"FIELDS TERMINATED BY ';'",
"LINES TERMINATED BY '\n'",
"FROM " , sTable,
" WHERE time between '", sStart , "' and '" , sStop,"'"); -- edit table name
PREPARE stmt FROM @myCommand;
EXECUTE stmt;
deallocate prepare stmt;
END