CHARGER des DONNÉES à partir de fichier CSV dans lequel apostrophe a été utilisé comme caractère d'échappement

J'ai un tas de données CSV que j'ai besoin de charger dans une base de données MySQL. Eh bien, CSV-ish, peut-être. (modifier: en fait, on dirait que le truc est décrit dans la RFC 4180)

Chaque ligne est une liste séparée par des virgules doublequoted cordes. Pour échapper à toute doublequotes qui apparaissent dans une colonne valeur, double doublequotes sont utilisés. Les barres obliques inverses sont autorisés à se représenter eux-mêmes.

Par exemple, la ligne:

"", "\wave\", ""hello,"" said the vicar", "what are ""scare-quotes"" good for?", "I'm reading ""Bossypants"""

si analysée en JSON doit être:

[ "", "\\wave\\", "\"hello,\" said the vicar", "what are \"scare-quotes\" good for?", "I'm reading \"Bossypants\"" ]

Je suis en train d'utiliser le LOAD DATA pour lire le fichier CSV, mais je suis en cours d'exécution dans certains comportements étranges.


Considérez, par exemple, si j'ai un simple tableau à deux colonnes

shell% mysql exampledb -e "describe person"
+-------+-----------+------+-----+---------+-------+
| Field | Type      | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------+-------+
| ID    | int(11)   | YES  |     | NULL    |       |
| UID   | char(255) | YES  |     | NULL    |       |
+-------+-----------+------+-----+---------+-------+
shell%

Si le premier non-d'en-tête de ligne de mon fichier d'entrée se termine sur "":

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\"

Je peux me charger tous les non-d'en-tête de ligne mais la première:

mysql> DELETE FROM person;
Query OK, 0 rows affected (0.00 sec)

mysql> 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> 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)

Ou je peux charger toutes les lignes y compris l'en-tête:

mysql> DELETE FROM person;
Query OK, 9 rows affected (0.00 sec)
mysql> 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> 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> 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)

Si pas de lignes de mon entrée à la fin du fichier sur "":

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\"

alors je peux me charge pas de lignes:

mysql> DELETE FROM person;
Query OK, 11 rows affected (0.00 sec)
mysql> 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> SELECT * FROM person;
Empty set (0.00 sec)

Ou je peux charger toutes les lignes, y compris l'en-tête:

mysql> DELETE FROM person;
Query OK, 0 rows affected (0.00 sec)
mysql> 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> 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> 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)

Donc maintenant que j'ai découvert beaucoup de façons de le faire mal, comment puis-je utiliser LOAD DATA pour importer les données à partir de ces fichiers dans ma base de données?

OriginalL'auteur rampion | 2013-06-11