CSV Dateien in die Datenbank laden
Fr., 2026-02-06 17:12 —
oli
Kürzlich wollte ich für eine persönliche kleine Spielerei die Wohnorte der Vereinsmitlieder meines Vereins auf einer Karte darstellen (IGOC Mitglieder). Die Adressen der Vereinsmitglieder waren mir bekannt. Nicht aber die Koordinaten der Wohnorte.
Also machte ich mich auf die Suche nach den Koordinaten und wurde beim Bundesamt für Landestopografie (swisstopo) fündig.
Die Daten werden dort als CSV Datei zur Verfügung gestellt. Details hier: Schweizer Ortschafts-Koordinaten.
Wie lädt man jetzt diese Daten in eine Datenbank?
Laden der Daten mit MariaDB/MySQL
MariaDB und MySQL haben dafür den Befehl 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
Anschliessend kann man die Daten in der Datenbank abfragen:
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
Bzw. etwas präziser:
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)
Das ausmisten der Duplikate überlasse ich der geneigten Lesering oder dem geneigten Leser… :-)
Soweit so gut, jetzt zu den Feinheiten:
Unterschiede zwischen MariaDB und MySQL
Das oben beschriebene Verfahren funktioniert einwandfrei bei MariaDB 11.4 und 11.8. Bei MySQL 8.4 gibt es kleine Unterschiede:
Die erste Fehlermeldung, die das Laden verhindert ist diese:
ERROR 3948 (42000): Loading local data is disabled; this must be enabled on both the client and server sides
Sie kann relativ einfach umgangen werden mit dem Befehl:
SQL> SET GLOBAL local_infile = ON;
Beim nächsten Versuch wird man wie folgt scheitern:
ERROR 2068 (HY000): LOAD DATA LOCAL INFILE file request rejected due to restrictions on access.
Dieses Problem kann gelöst werden, indem man den MySQL Client wie folgt startet:
$ mysql --local-infile=1 --user=root test
Quellen
- MariaDB: LOAD DATA INFILE
- MySQL: LOAD DATA Statement
Laden der Daten mit PostgreSQL
PostgreSQL hat hierfür den Befehl 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
Auch hier erhalten wir das Resultat wie erwartet in der für PostgreSQL üblichen Form:
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)
Quellen
- PostgreSQL: COPY
Kleine Unterschiede zwischen MariaDB/MySQL und PostgreSQL
Grundsätzlich lautet der Ladebefehl in den beiden Datenbankwelten gänzlich unterschiedlich.
Bei MariaDB und PostgreSQL laufen die Befehle ‘out-of-the-box’. MySQL hat hier noch zwei zusätzliche Sicherheitshürden eingebaut.
PostgreSQL kennt keine UNSIGNED Integer Datentypen, daher muss der nächstgrössere Datentyp (INT) verwendet werden, was ein klein wenig weniger platzsparender ist als bei MariaDB/MySQL.
Bemerkungen
Als wir den selben Test vor ein paar wenigen Tagen gemacht hatten, gab es noch eine Fehler beim Laden. Es scheint also, dass sich die Datenquelle ebenfalls minimal geändert hat…
Ich habe auf die Schnelle nicht herausgefunden, ob es zu diesen Lade-Befehlen einen SQL-Standard gibt und wenn ja, ob MariaDB/MySQL oder PostgreSQL hier Standard konform sind.
Und natürlich gibt es noch andere Möglichkeiten, wie man seine CSV Daten in die Datenbank rein kriegt…
Die Tools mariadb-import/mysqlimport verwendet man, wenn man das von der Kommandozeile aus machen möchte. Die CSV Storage Engine kann ebenfalls dazu missbraucht werden (Details siehe hier). Eine offiziell unterstütze Variante ist ist die MariaDB CONNECT Storage Engine mit dem CSV Type (siehe hier):
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;
So wie es aussieht, wird die CONNECT Storage Engine von MariaDB leider nicht mehr weiter supportet?!? Und auch das Tool mydumper/myloader scheint mit CSV Dateien umgehen zu können.
Und natürlich kann das ganze auch applikatorisch gelöst werden…
Bei PostgreSQL gibt es folgende Möglichkeiten:
postgres=# \copy wgs84 FROM '/tmp/AMTOVZ_CSV_WGS84/AMTOVZ_CSV_WGS84.csv' DELIMITER ';' CSV HEADER
dann von der Shell aus:
$ psql --user=dba -c "\copy wgs84 FROM '/tmp/AMTOVZ_CSV_WGS84/AMTOVZ_CSV_WGS84.csv' DELIMITER ';' CSV HEADER"
Und die Variante über den Foreign Data Wrapper (FWD). Habe ich aber nicht ausprobiert:
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')
;
