I’m trying to do a select into outfile to a CSV. My table values can contain characters like r, n, comma, “(double quotes)

Now MySQL does not seem to handle all the above characters efficiently

As a first query. I ran –

SELECT *
FROM (VALUES
  ROW('Thisr is rn test "data', 123), 
  ROW('def" r', 456),
  ROW('n newline"', 789)) AS tbl
INTO OUTFILE '/path/file1.csv' 
CHARACTER SET utf8
FIELDS TERMINATED BY ','
ENCLOSED BY '\'
ESCAPED BY ''
LINES TERMINATED BY 'rn';

Here I have ESCAPED BY ‘\’. The output CSV when opened in Excel is as follows which is not right as it adds extra to the data as shown below. The output is also not quite right

Next, I ESCAPED BY ‘”‘. This handles the double quotes in the values but still the data is not represented correctly in the CSV as shown below

Thirdly, I tried not escaping with anything at all with ESCAPED BY ”. This is the output

Now, if I remove all r from the data in the query and ESCAPED BY ‘”‘, the data issues seem to get resolved, which points to r being the main issue here

Is there anyway we can handle all formatting using the outfile itself?