title: LOAD DATA from CSV file where doublequote was used as the escape character
body: <p>I have a bunch of CSV data that I need to load into a MySQL database.  Well, CSV-ish, perhaps. (<strong>edit</strong>: <a href="http://en.wikipedia.org/wiki/Comma-separated_values#Toward_standardization" rel="noreferrer">actually, it looks like the stuff described in RFC 4180</a>)</p>
  
  <p>Each row is a list of comma-separated doublequoted strings. To escape any doublequotes that appear within a column value, double doublequotes are used.  Backslashes are allowed to represent themselves.</p>
  
  <p>For example, the line:</p>
  
  <pre><code>"", "\wave\", ""hello,"" said the vicar", "what are ""scare-quotes"" good for?", "I'm reading ""Bossypants"""
  </code></pre>
  
  <p>if parsed into JSON should be:</p>
  
  <pre><code>[ "", "\\wave\\", "\"hello,\" said the vicar", "what are \"scare-quotes\" good for?", "I'm reading \"Bossypants\"" ]
  </code></pre>
  
  <p>I'm trying to use the <code>LOAD DATA</code> to read the CSV in, but I'm running into some weird behaviour.</p>
  
  <hr>
  
  <p>As an example, consider if I have a simple two column table </p>
  
  <pre><code>shell% mysql exampledb -e "describe person"
  +-------+-----------+------+-----+---------+-------+
  | Field | Type      | Null | Key | Default | Extra |
  +-------+-----------+------+-----+---------+-------+
  | ID    | int(11)   | YES  |     | NULL    |       |
  | UID   | char(255) | YES  |     | NULL    |       |
  +-------+-----------+------+-----+---------+-------+
  shell%
  </code></pre>
  
  <p>If the first non-header line of my input file ends on <code>""</code>:</p>
  
  <pre><code>shell% cat temp-1.csv
  "ID","UID"
  "9",""
  "0","Steve the Pirate"
  "1","\Alpha"
  "2","Hoban ""Wash"" Washburne"
  "3","Pastor Veal"
  "4","Tucker"
  "10",""
  "5","Simon"
  "6","Sonny"
  "7","Wat\"
  </code></pre>
  
  <p>I can either load every non-header line but the first:</p>
  
  <pre><code>mysql&gt; DELETE FROM person;
  Query OK, 0 rows affected (0.00 sec)
  
  mysql&gt; LOAD DATA
            LOCAL INFILE 'temp-1.csv'
            INTO TABLE person
            FIELDS
              TERMINATED BY ','
              ENCLOSED BY '"'
              ESCAPED BY '"'
            LINES
              TERMINATED BY '\n'
            IGNORE 1 LINES
         ;
  Query OK, 9 rows affected (0.00 sec)
  Records: 9  Deleted: 0  Skipped: 0  Warnings: 0
  
  mysql&gt; SELECT * FROM person;
  +------+------------------------+
  | ID   | UID                    |
  +------+------------------------+
  |    0 | Steve the Pirate       |
  |   10 |                        |
  |    1 | \Alpha                 |
  |    2 | Hoban "Wash" Washburne |
  |    3 | Pastor Veal            |
  |    4 | Tucker                 |
  |    5 | Simon                  |
  |    6 | Sonny                  |
  |    7 | Wat\                   |
  +------+------------------------+
  9 rows in set (0.00 sec)
  </code></pre>
  
  <p>Or I can load all lines including the header:</p>
  
  <pre><code>mysql&gt; DELETE FROM person;
  Query OK, 9 rows affected (0.00 sec)
  
  mysql&gt; LOAD DATA
            LOCAL INFILE 'temp-1.csv'
            INTO TABLE person
            FIELDS
              TERMINATED BY ','
              ENCLOSED BY '"'
              ESCAPED BY '"'
            LINES
              TERMINATED BY '\n'
            IGNORE 0 LINES
         ;
  Query OK, 11 rows affected, 1 warning (0.01 sec)
  Records: 11  Deleted: 0  Skipped: 0  Warnings: 1
  
  mysql&gt; show warnings;
  +---------+------+--------------------------------------------------------+
  | Level   | Code | Message                                                |
  +---------+------+--------------------------------------------------------+
  | Warning | 1366 | Incorrect integer value: 'ID' for column 'ID' at row 1 |
  +---------+------+--------------------------------------------------------+
  1 row in set (0.00 sec)
  
  mysql&gt; SELECT * FROM person;
  +------+------------------------+
  | ID   | UID                    |
  +------+------------------------+
  |    0 | UID                    |
  |    9 |                        |
  |    0 | Steve the Pirate       |
  |   10 |                        |
  |    1 | \Alpha                 |
  |    2 | Hoban "Wash" Washburne |
  |    3 | Pastor Veal            |
  |    4 | Tucker                 |
  |    5 | Simon                  |
  |    6 | Sonny                  |
  |    7 | Wat\                   |
  +------+------------------------+
  11 rows in set (0.00 sec)
  </code></pre>
  
  <p>If no lines of my input file end on <code>""</code>:</p>
  
  <pre><code>shell% cat temp-2.csv
  "ID","UID"
  "0","Steve the Pirate"
  "1","\Alpha"
  "2","Hoban ""Wash"" Washburne"
  "3","Pastor Veal"
  "4","Tucker"
  "5","Simon"
  "6","Sonny"
  "7","Wat\"
  </code></pre>
  
  <p>then I can either load no lines: </p>
  
  <pre><code>mysql&gt; DELETE FROM person;
  Query OK, 11 rows affected (0.00 sec)
  
  mysql&gt; LOAD DATA
            LOCAL INFILE 'temp-2.csv'
            INTO TABLE person
            FIELDS
              TERMINATED BY ','
              ENCLOSED BY '"'
              ESCAPED BY '"'
            LINES
              TERMINATED BY '\n'
            IGNORE 1 LINES
         ;
  Query OK, 0 rows affected (0.00 sec)
  Records: 0  Deleted: 0  Skipped: 0  Warnings: 0
  
  mysql&gt; SELECT * FROM person;
  Empty set (0.00 sec)
  </code></pre>
  
  <p>Or I can load all the lines including the header:</p>
  
  <pre><code>mysql&gt; DELETE FROM person;
  Query OK, 0 rows affected (0.00 sec)
  
  mysql&gt; LOAD DATA
            LOCAL INFILE 'temp-2.csv'
            INTO TABLE person
            FIELDS
              TERMINATED BY ','
              ENCLOSED BY '"'
              ESCAPED BY '"'
            LINES
              TERMINATED BY '\n'
            IGNORE 0 LINES
         ;
  Query OK, 9 rows affected, 1 warning (0.03 sec)
  Records: 9  Deleted: 0  Skipped: 0  Warnings: 1
  
  mysql&gt; show warnings;
  +---------+------+--------------------------------------------------------+
  | Level   | Code | Message                                                |
  +---------+------+--------------------------------------------------------+
  | Warning | 1366 | Incorrect integer value: 'ID' for column 'ID' at row 1 |
  +---------+------+--------------------------------------------------------+
  1 row in set (0.00 sec)
  
  mysql&gt; SELECT * FROM person;
  +------+------------------------+
  | ID   | UID                    |
  +------+------------------------+
  |    0 | UID                    |
  |    0 | Steve the Pirate       |
  |    1 | \Alpha                 |
  |    2 | Hoban "Wash" Washburne |
  |    3 | Pastor Veal            |
  |    4 | Tucker                 |
  |    5 | Simon                  |
  |    6 | Sonny                  |
  |    7 | Wat\                   |
  +------+------------------------+
  9 rows in set (0.00 sec)
  </code></pre>
  
  <p>So now that I've discovered many ways to do it wrong, how can I use <code>LOAD DATA</code> to import the data from these files into my database?</p>