Search code examples
mysqllibreofficeinto-outfile

LOAD DATA INFILE when fields real escaped fields contain \n and \"

Schema for my sms table is - sms(id, mobileNumber, text)

where text is of type text.

One of the row has below text -

Foo bar\nLorem ipsum dolor sit amet.\n\nconsectetur adipisicing elit, said:\n\n\" sed do eiusmod tempor incididunt ut labore et dolore magna aliqua don\'t \"

When I run this query - 

select 'SMS ID', 'Mobile Number', 'SMS Text' 
union 
select id, ifnull(mobileNumber, 'Not Available'), text 
from sms
into outfile '/tmp/SMSUsage.csv' 
fields terminated by ',' enclosed by '"' 
lines terminated by '\n';

and open the CSV file in Libreoffice, I see the text in more than one cells like -

Foo bar\\\\nLorem ipsum dolor sit amet.\\\\n\\\\nconsectetur adipisicing elit, said:\n\n\

in one column and then each word after above sentence into a separate column.

Also Why does it put three extra '\' before '\n'? How do I get the text in a single cell?

Solution

  • Try this:

    SELECT 'SMS ID', 'Mobile Number', 'SMS Text' 
UNION 
    SELECT id, IFNULL(mobileNumber, 'Not Available'), text 
    FROM sms
INTO OUTFILE '/tmp/SMSUsage.csv' 
FIELDS TERMINTATED by ',' 
OPTIONALLY ENCLOSED BY '"' 
ESCAPED BY '"' 
LINES TERMINATED BY '\n';