LOAD DATA INFILE loading incorrect field value for string with space

MySQL & MariaDB Percona Server for MySQL 5.7
1

HI,

I’m using Server version: 5.7.24-27 Percona Server (GPL), Release 27, Revision bd42700.

I’m migrating data from MSSQL to MySQL by generating CSV files and then importing to MySQL using below command.

LOAD DATA LOCAL INFILE ‘AuditTrail.csv’ INTO TABLE AuditTrail FIELDS TERMINATED BY ‘|’ ENCLOSED BY ‘’ ESCAPED BY ‘\0’ LINES TERMINATED BY ‘\r\n’;

But After the data import I can see if the field is having space then it’s considering the string after space as new line.
for example. in MSSQL the field is having below value.

Dqe0aytIEI7mYLwhm/dLIlnksyp1yKMD0Yp2EcUlCj4za1/hY3M4/Q1x3llS8Bst tta78A9uhgZBzHT7lOYo5R50vPfcDsPeBoQlr6ugrGyBUDZXni/Sq1vsB8xWIw6d

but in MySQL it’s only importing upto below value. [TABLE]
[TR]
[TD]Dqe0aytIEI7mYLwhm/dLIlnksyp1yKMD0Yp2EcUlCj4za1/hY3M4/Q1x3llS8Bst[/TD]
[/TR]
[/TABLE]
My field length is 300 in both MSSQL and MySQL so no question of field truncation.

Please HELP.

For example. I’ve attached one CSV file and the table as below.
CREATE TABLE AuditTrail (
ID int(11) NOT NULL,
Entity varchar(50) DEFAULT NULL,
EntityId int(11) DEFAULT NULL,
Action varchar(50) DEFAULT NULL,
Description varchar(300) DEFAULT NULL,
Param1 varchar(300) DEFAULT NULL,
Param2 varchar(300) DEFAULT NULL,
Param3 varchar(300) DEFAULT NULL,
CreatedBy int(11) NOT NULL,
CreatedDt datetime(6) NOT NULL,
UpdatedBy int(11) NOT NULL,
UpdatedDt datetime(6) NOT NULL,
PRIMARY KEY (ID),
KEY I_AuditTrail_Entity (Entity,EntityId),
KEY I_AuditTrail_Action (Entity,EntityId,Action)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

You can see the CSV file is having two rows but it will truncate field and insert 3 rows.

AuditTrail.txt (641 Bytes)