Not the answer you need?
Register and ask your own question!

LOAD DATA INFILE loading incorrect field value for string with space

prince85prince85 ContributorCurrent User Role Novice
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.

Dqe0aytIEI7mYLwhm/dLIlnksyp1yKMD0Yp2EcUlCj4za1/hY3M4/Q1x3llS8Bst


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.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.