Charger des fichiers CSV dans la base de données
ven., 2026-02-06 18:04 —
Shinguz
Récemment, j’ai voulu représenter sur une carte les lieux de résidence des membres de mon association (membres IGOC) pour un petit projet personnel. Je connaissais les adresses des membres de l’association, mais pas les coordonnées de leurs lieux de résidence.
Je me suis donc mis à la recherche des coordonnées et j’ai trouvé ce que je cherchais auprès de l’Office fédéral de topographie (swisstopo).
Les données y sont mises à disposition sous forme de fichier CSV. Détails ici : Coordonnées des localités suisses.
Comment charger ces données dans une base de données ?
Chargement des données avec MariaDB/MySQL
MariaDB et MySQL disposent pour cela de la commande LOAD DATA INFILE:
SQL> DROP TABLE IF EXISTS wgs84;
SQL> -- SET GLOBAL local_infile = ON; -- Only needed with MySQL
SQL> CREATE TABLE wgs84 (
ortschaftsname VARCHAR(32)
, plz4 SMALLINT
, zusatzziffer SMALLINT
, zip_id SMALLINT UNSIGNED
, gemeindename VARCHAR(32)
, bfs_nr SMALLINT
, kantonskuerzel CHAR(2)
, adressenanteil varchar(8)
, e DOUBLE
, n DOUBLE
, sprache VARCHAR(8)
, validity VARCHAR(12)
);
SQL> -- TRUNCATE TABLE wgs84;
SQL> LOAD DATA LOCAL INFILE '/tmp/AMTOVZ_CSV_WGS84/AMTOVZ_CSV_WGS84.csv'
INTO TABLE wgs84
FIELDS TERMINATED BY ';'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
;
Query OK, 5713 rows affected
Records: 5713 Deleted: 0 Skipped: 0 Warnings: 0
On peut ensuite consulter les données dans la base de données:
SQL> SELECT * FROM wgs84 ORDER BY ortschaftsname LIMIT 5;
+----------------+------+--------------+--------+--------------+--------+----------------+----------------+-------------------+--------------------+---------+------------+
| ortschaftsname | plz4 | zusatzziffer | zip_id | gemeindename | bfs_nr | kantonskuerzel | adressenanteil | e | n | sprache | validity |
+----------------+------+--------------+--------+--------------+--------+----------------+----------------+-------------------+--------------------+---------+------------+
| Aadorf | 8355 | 0 | 4672 | Aadorf | 4551 | TG | 96.802 % | 8.903193007810433 | 47.491079014637265 | de | 2008-07-01 |
| Aadorf | 8355 | 0 | 4672 | Elgg | 294 | ZH | 3.198 % | 8.89206766645808 | 47.4933781685032 | de | 2008-07-01 |
| Aarau | 5000 | 0 | 2913 | Aarau | 4001 | AG | 99.713 % | 8.048148371736266 | 47.38973523857376 | de | 2008-07-01 |
| Aarau | 5000 | 0 | 2913 | Suhr | 4012 | AG | 0.287 % | 8.059410934099922 | 47.383298214804334 | de | 2008-07-01 |
| Aarau | 5004 | 0 | 2932 | Aarau | 4001 | AG | 100 % | 8.060698546432551 | 47.400587704180744 | de | 2008-07-01 |
+----------------+------+--------------+--------+--------------+--------+----------------+----------------+-------------------+--------------------+---------+------------+
5 rows in set
Ou plus précisément :
SQL> SELECT ortschaftsname AS city, plz4 AS city_code, e AS lon, n AS lat
FROM wgs84 WHERE plz4 IN (8280, 4663, 6043);
+-------------+-----------+-------------------+--------------------+
| city | city_code | lon | lat |
+-------------+-----------+-------------------+--------------------+
| Aarburg | 4663 | 7.904271716719409 | 47.321443418782955 |
| Aarburg | 4663 | 7.889249714098425 | 47.313536073562474 |
| Aarburg | 4663 | 7.880309179095798 | 47.31255194439023 |
| Adligenswil | 6043 | 8.364849060491428 | 47.07037816052481 |
| Kreuzlingen | 8280 | 9.173740257895282 | 47.64491046067056 |
| Kreuzlingen | 8280 | 9.159171428030783 | 47.654149879509134 |
| Kreuzlingen | 8280 | 9.204470741840725 | 47.639949130372145 |
+-------------+-----------+-------------------+--------------------+
7 rows in set (0.003 sec)
Je laisse le soin au lecteur ou à la lectrice de supprimer les doublons… :-)
Jusqu’ici tout va bien, passons maintenant aux subtilités:
Différences entre MariaDB et MySQL
La procédure décrite ci-dessus fonctionne parfaitement avec MariaDB 11.4 et 11.8. Avec MySQL 8.4, il existe de légères différences:
Le premier message d’erreur qui empêche le chargement est le suivant:
ERROR 3948 (42000): Loading local data is disabled; this must be enabled on both the client and server sides
Il peut être contourné assez facilement à l’aide de la commande:
SQL> SET GLOBAL local_infile = ON;
Lors de la tentative suivante, vous échouerez comme suit:
ERROR 2068 (HY000): LOAD DATA LOCAL INFILE file request rejected due to restrictions on access.
Ce problème peut être résolu en démarrant le client MySQL comme suit:
$ mysql --local-infile=1 --user=root test
Sources
- MariaDB: LOAD DATA INFILE
- MySQL: LOAD DATA Statement
Chargement des données avec PostgreSQL
PostgreSQL dispose pour cela de la commande COPY ... FROM:
postgres=# DROP TABLE IF EXISTS wgs84;
postgres=# CREATE TABLE wgs84 (
ortschaftsname VARCHAR(32)
, plz4 SMALLINT
, zusatzziffer SMALLINT
, zip_id INT
, gemeindename VARCHAR(32)
, bfs_nr SMALLINT
, kantonskuerzel CHAR(2)
, adressenanteil varchar(8)
, e DOUBLE PRECISION
, n DOUBLE PRECISION
, sprache VARCHAR(8)
, validity VARCHAR(12)
);
postgres=# -- TRUNCATE TABLE wgs84;
postgres=# COPY wgs84
FROM '/tmp/AMTOVZ_CSV_WGS84/AMTOVZ_CSV_WGS84.csv'
DELIMITER ';'
CSV HEADER
;
COPY 5713
Ici aussi, nous obtenons le résultat attendu sous la forme habituelle pour PostgreSQL:
postgres=# SELECT ortschaftsname AS city, plz4 AS city_code, e AS lon, n AS lat
FROM wgs84 WHERE plz4 IN (8280, 4663, 6043);
city | city_code | lon | lat
-------------+-----------+-------------------+--------------------
Aarburg | 4663 | 7.904271716719409 | 47.321443418782955
Aarburg | 4663 | 7.889249714098425 | 47.313536073562474
Aarburg | 4663 | 7.880309179095798 | 47.31255194439023
Adligenswil | 6043 | 8.36487538940682 | 47.07037794822416
Kreuzlingen | 8280 | 9.173740257895282 | 47.64491046067056
Kreuzlingen | 8280 | 9.159171428030783 | 47.654149879509134
Kreuzlingen | 8280 | 9.204470741840725 | 47.639949130372145
(7 rows)
Sources
- PostgreSQL: COPY
Petites différences entre MariaDB/MySQL et PostgreSQL
En principe, la commande de chargement est complètement différente dans les deux univers de bases de données.
Avec MariaDB et PostgreSQL, les commandes fonctionnent « prêtes à l’emploi ». MySQL a intégré deux obstacles de sécurité supplémentaires.
PostgreSQL ne connaît pas les types de données entières UNSIGNED, il faut donc utiliser le type de données immédiatement supérieur (INT), ce qui est un peu moins économe en espace que avec MariaDB/MySQL.
Remarques
Lorsque nous avons effectué le même test il y a quelques jours, une erreur s’est produite lors du chargement. Il semble donc que la source de données ait également subi une modification minime…
Je n’ai pas pu déterminer rapidement s’il existe une norme SQL pour ces commandes de chargement et, si oui, si MariaDB/MySQL ou PostgreSQL sont conformes à cette norme.
Et bien sûr, il existe d’autres moyens d’importer vos données CSV dans la base de données…
Les outils mariadb-import/mysqlimport sont utilisés si vous souhaitez effectuer cette opération à partir de la ligne de commande. Le moteur de stockage CSV peut également être utilisé à cette fin (pour plus de détails, voir ici). Une variante officiellement prise en charge est le moteur de stockage MariaDB CONNECT avec le type CSV (voir ici):
SQL> INSTALL SONAME 'ha_connect';
SQL> CREATE TABLE wgs84_fdw
ENGINE = CONNECT
table_type = CSV
file_name='/tmp/AMTOVZ_CSV_WGS84/AMTOVZ_CSV_WGS84.csv'
header = 1
sep_char = ';'
quoted = 0;
SQL> INSERT INTO wgs84 SELECT * FROM wgs84_fdw;
Il semblerait que le moteur de stockage CONNECT de MariaDB ne soit malheureusement plus pris en charge? Et l’outil mydumper/myloader semble également pouvoir traiter les fichiers CSV.
Et bien sûr, tout cela peut également être résolu de manière applicative…
Avec PostgreSQL, les possibilités suivantes existent:
postgres=# \copy wgs84 FROM '/tmp/AMTOVZ_CSV_WGS84/AMTOVZ_CSV_WGS84.csv' DELIMITER ';' CSV HEADER
puis depuis le shell:
$ psql --user=dba -c "\copy wgs84 FROM '/tmp/AMTOVZ_CSV_WGS84/AMTOVZ_CSV_WGS84.csv' DELIMITER ';' CSV HEADER"
Et la variante via le Foreign Data Wrapper (FWD). Mais je ne l’ai pas essayée:
postgres=# CREATE EXTENSION postgres_fdw;
postgres=# CREATE SERVER foreign_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (
datasource 'CSV:/tmp/AMTOVZ_CSV_WGS84/AMTOVZ_CSV_WGS84.csv',
format 'CSV'
)
;
postgres=# CREATE USER MAPPING FOR local_user
SERVER foreign_server
OPTIONS (user 'foreign_user', password 'password')
;
postgres=# CREATE FOREIGN TABLE foreign_table (
id integer NOT NULL,
data text
)
SERVER foreign_server
OPTIONS (schema_name 'some_schema', table_name 'some_table')
;
Addendum
Le type de données MariaDB/MySQL DOUBLE s’appelle DOUBLE PRECISION dans PostgreSQL.
Cette page a été traduite à l’aide de deepl.com.
