Correct ‘Lines Terminated By’ for MySQL Query

csvexcelMySQLmysql-workbench

I am trying to query two tables of data and send them to a csv file for upload to a website. However i am having trouble getting the formatting to play ball. I want headings to separate into columns and then the relavant data to line up with the columns in the body of the data.

I am currently closest to achieving my aims with –

FIELDS TERMINATED BY ',' ENCLOSED BY '' LINES TERMINATED BY ''

Which returns –

Data returned from query in mysql workbench

However you can see my issue. Any part numbers that have a '\' render into the wrong columns and the data does not line up with the heading.

Now please bear in mind, the tables are always updating every second, and i have no control over how the data is uploaded. I cannot remove the '\' from the part#'s.
I need a workaround that ignores the '\'.

This is quite hard to explain so…

TLDR:- I need the data to all line up like the majority of the rows in the picture, what are the 'Fields/lines terminated by' parameters?

Best Answer

See the mydata.csv file at bottom of the answer.

I created a table xy

CREATE TABLE `xy` 
(
  `fred` int(11) DEFAULT NULL,
  `mary` int(11) DEFAULT NULL,
  `billy` varchar(50) DEFAULT NULL
);

From the documentation here, I tried this

LOAD DATA INFILE 'mydata.txt' INTO TABLE tbl_name
  FIELDS TERMINATED BY ',' ENCLOSED BY '"'
  LINES TERMINATED BY '\r\n'

But that failed (only one line imported). I changed \r\n to \n (Unix), that was better (all lines imported, but no \ in the VARCHAR field - they were simply deleted).

Further Googling led me here

So I tried this (from the answer)

LOAD DATA INFILE 'mydata.csv' INTO TABLE xy
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
ESCAPED BY ''
LINES TERMINATED BY '\n';

(note ESCAPED BY '' - i.e. nothing - the \ is the default escape character).

And that worked (sample of four lines in table xy below)

|    2 |    3 | asf\dsadfs |
|    2 |    3 | asfdsadfs  |
|    2 |    3 | asfdsadfs  |
|    2 |    3 | asf\dsadfs |

My sample mydata.csv file

2,3,asfdsadfs
2,3,asfdsadfs
2,3,asfdsadfs
2,3,asfdsadfs
2,3,asfdsadfs
2,3,asfdsadfs
2,3,asfdsadfs
2,3,asf\dsadfs
2,3,asfdsadfs
2,3,asfdsadfs
2,3,asf\dsadfs
2,3,asfdsadfs
2,3,asfdsadfs
2,3,a\sfdsadfs
2,3,asfdsadfs
2,3,asfdsadfs
Related Question