Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-22581

LOAD DATA ENCLOSE BY preserves quotes if input file has lines terminated with '\r\n'

    XMLWordPrintable

    Details

      Description

      I am using LOAD DATA INFILE with ENCLOSED BY to ingest a csv file whose values are all enclosed by quotes (it does not matter whether it is single or double quotes).

      For VARCHAR and TEXT fields, the quotes are properly removed from the values for all columns in the table except the last column, if the input csv file has lines terminated by '\r\n'.

      Here is how to reproduce it:

      CREATE TABLE `test_quotes` (
        `date` int(11) NOT NULL,
        `int_test` int(11) DEFAULT NULL,
        `varchar_test` varchar(5) DEFAULT NULL,
        `varchar_test2` varchar(5) DEFAULT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs ROW_FORMAT=DYNAMIC
      

      echo -e "'20200515','1','foo','bar'\r\n" > test_quotes
       
      LOAD DATA INFILE 'test_quotes' INTO TABLE `test_quotes` FIELDS TERMINATED BY ',' ENCLOSED BY "'";
       
      select * from test_quotes;
      +----------+----------+--------------+---------------+
      | date     | int_test | varchar_test | varchar_test2 |
      +----------+----------+--------------+---------------+
      | 20200515 |        1 | foo          | 'bar'         |
      +----------+----------+--------------+---------------+
      

        Attachments

          Activity

            People

            Assignee:
            Unassigned
            Reporter:
            mhadji@gmail.com Marios Hadjieleftheriou
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: