MySQL Forums
Forum List  »  Newbie

Adding column names to OUTFILE
Posted by: Darren White
Date: October 28, 2013 12:12PM

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

Options: ReplyQuote


Subject
Written By
Posted
Adding column names to OUTFILE
October 28, 2013 12:12PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.