MySQL: LOAD DATA LOCAL INFILE adds extra character '\r'

Question

I have a table like this:

    CREATE TABLE `tblinquiries` (
  `UID` varchar(50) DEFAULT NULL,
  `ReviewDate` date NOT NULL,
  `InquiryId` varchar(50) DEFAULT NULL,
  `AuditStatus` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`InquiryId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

I have a csv file with data:

UID,ReviewDate,InquiryId,AuditStatus

UID1,2018-07-06,109814969,Check

UID2,2018-07-06,109866072,Check

UID3,2018-07-06,109911408,Check

UID4,2018-07-06,109798278,Check

I use below command to to upload the data:

   $location = '../uploads/';
   $name = $_FILES["file"]["name"];
   $filePath = $location.$name;
   $table = 'tblinquiries';

   LOAD DATA LOCAL INFILE "'.$filePath.'"
   INTO TABLE '.$table.'
   FIELDS TERMINATED by \',\' OPTIONALLY ENCLOSED BY \'"\'
   LINES TERMINATED BY \'\n\'
   IGNORE 1 LINES

It uploads the data but adds extra character "\r" from the second line. I exported the data and got like below:

('UID4', '2018-07-06', '109798278', 'Check'),
('UID1', '2018-07-06', '109814969', 'Check\r'),
('UID2', '2018-07-06', '109866072', 'Check\r'),
('UID3', '2018-07-06', '109911408', 'Check\r');

After running:

SELECT AuditStatus, LENGTH(AuditStatus) FROM `tblinquiries`

got:

AuditStatus LENGTH(AuditStatus)
Check          5
Check          6
Check          6
Check          6

How can I solve this?

You might try changing the line terminated to \r\n with proper escaping of course. — Sloan Thrasher, Jul 16 '18 at 11:45

score 0 · Answer 1 · answered Jul 16 '18 at 11:38

I would assume that your source data has those \r control characters, because LOAD DATA doesn't typically add data to the source file (unless you tell it to do so, which does not appear to be the case). We can try running RTRIM on the AuditStatus column:

LOAD DATA LOCAL INFILE "'.$filePath.'"
INTO TABLE '.$table.'
FIELDS TERMINATED by \',\' OPTIONALLY ENCLOSED BY \'"\'
LINES TERMINATED BY \'\n\'
IGNORE 1 LINES
(UID, ReviewDate, InquiryId, @AuditStatus)
SET AuditStatus = RTRIM(@AuditStatus);

score 0 · Accepted Answer · answered Jul 17 '18 at 11:01

As @Sloan suggested I changed the line terminators and that solved the problem. Here is the final code.

        LOAD DATA LOCAL INFILE "'.$filePath.'"
        INTO TABLE '.$table.'
        FIELDS TERMINATED by \',\' OPTIONALLY ENCLOSED BY \'"\'
        LINES TERMINATED BY \'\r\n\'
        IGNORE 1 LINES

MySQL: LOAD DATA LOCAL INFILE adds extra character '\r'

2 Answers2