I've been attempting to make a csv export properly into my test folder with a variable file name -- I'm getting close but I think I've got it down to one problem before it works. Here's the code so far:
SET @q1 := CONCAT(
"SELECT *
FROM table
INTO OUTFILE '/SQLOut/test"
, DATE_FORMAT(NOW(),'%Y%m%d')
,".csv'"
, "FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'");
PREPARE s1 FROM @q1;
EXECUTE s1;
DROP PREPARE s1;
As you can see, I have to use prepared statements in order to get the filename to include today's date. That part works -- but if I want to make it a csv, I need to include the 'fields terminated' stuff afterwards, which is what I think is breaking it.
I believe this would function as intended if the OPTIONALLY ENCLOSED BY '"' didn't contain " -- the double quotes after it are closing my original " after CONCAT( and the final line of 'LINES TERMINATED BY '\n'"); is getting cut off and MySQL is getting confused.
Is there any other way to do this?
Try:
SET @`qry` := CONCAT('
SELECT *
FROM `table`
INTO OUTFILE \'/SQLOut/test', DATE_FORMAT(NOW(), '%Y%m%d'), '.csv\'
FIELDS TERMINATED BY \',\' OPTIONALLY ENCLOSED BY \'"\'
LINES TERMINATED BY \'\\n\'
');
PREPARE `stmt` FROM @`qry`;
EXECUTE `stmt`;
DROP PREPARE `stmt`;