Einstellung von Trennzeichen und Datenformaten beim Import

Wenn Daten aus verschiedenen Systemen kommen, kann ihr Format unterschiedlich sein. Eine Datei nutzt vielleicht Kommas als Trennzeichen, eine andere Semikolons und in einer dritten ist Tab das Haupttrennzeichen zwischen Spalten. Falsche Einstellung der Trennzeichen beim Import kann zu Fehlern oder falscher Interpretation der Daten führen.

Außerdem kann es im echten Leben passieren, dass du Daten im Textformat ohne Standard-CSV-Header importieren musst, leere Werte behandeln und Fälle berücksichtigen sollst, in denen leere Zeilen als NULL interpretiert werden. Deshalb ist die Einstellung von Trennzeichen und Formaten ein fester Bestandteil beim Massenimport von Daten.

Haupttrennzeichen: Wie geht man damit um?

In PostgreSQL bietet der COPY-Befehl Flexibilität bei der Einstellung von Trennzeichen. Lass uns anschauen, wie das funktioniert.

Trennzeichen festlegen

Standardmäßig erwartet COPY, dass die Spalten in einer CSV-Datei durch Kommas getrennt sind. Das ist aber nicht immer praktisch oder möglich: Manche nutzen Semikolons (;), Pipes (|) oder sogar Tabs (\t).

So kannst du das Trennzeichen mit dem Parameter DELIMITER angeben:

COPY students FROM '/path/to/students.csv'
DELIMITER ',' 
CSV HEADER;

Wenn stattdessen Semikolons verwendet werden:

COPY students FROM '/path/to/students.csv'
DELIMITER ';'
CSV HEADER;

Du kannst sogar eine Datei mit Tabs importieren:

COPY students FROM '/path/to/students.tsv'
DELIMITER E'\t' 
CSV HEADER;

Hier gibt E'\t' an, dass das Trennzeichen ein Tabulator ist.

Dateien mit ungewöhnlichem Trennzeichen importieren

Praxisbeispiel: Du hast eine Datei mit Kursdaten, in der Pipes (|) als Trennzeichen genutzt werden. Die Daten sehen so aus:

kurs_id|kurs_name|credits
1|SQL Grundlagen|3
2|Fortgeschrittenes SQL|4
3|PostgreSQL Masterclass|5

So kannst du diese Daten in die Tabelle courses importieren:

COPY courses(course_id, course_name, credits)
FROM '/path/to/courses.csv' 
DELIMITER '|' 
CSV HEADER;

Hier sagst du PostgreSQL explizit, dass das Trennzeichen die Pipe ist.

Einstellung von Datenformaten beim Import

Trennzeichen sind nur ein Teil der Aufgabe. Das Datenformat in der Datei spielt auch eine wichtige Rolle. Schauen wir uns die wichtigsten Einstellungen für Datenformate an.

Leere Zeilen ignorieren und NULL setzen

In großen Datenmengen gibt es oft leere Zeilen oder Spalten ohne Werte. PostgreSQL sieht sie als leere Strings, wenn nichts anderes angegeben ist. Um solche Werte als NULL zu interpretieren, kannst du den Parameter NULL AS nutzen:

Beispiel. Angenommen, deine Datei enthält Zeilen mit leeren Werten:

id,vorname,nachname,email
1,John,Doe,
2,Jane,Smith,jane.smith@example.com
3,,Brown,james.brown@example.com

Datenimport mit Interpretation leerer Werte in der Spalte email als NULL:

COPY students(id, first_name, last_name, email)
FROM '/path/to/students.csv'
DELIMITER ',' 
CSV HEADER 
NULL AS '';

Dadurch werden leere Werte in der Datei als NULL in der Tabelle gespeichert.

Leere Zeilen ignorieren

Manchmal enthält die Datei leere Zeilen, die du nicht importieren willst. PostgreSQL kann solche Zeilen automatisch ignorieren.

Beispiel. Datei mit einer leeren Zeile:

id,vorname,nachname,email
1,John,Doe,john.doe@example.com

2,Jane,Smith,jane.smith@example.com

Nutze den Parameter IGNORE_BLANK_LINES:

COPY students(id, first_name, last_name, email)
FROM '/path/to/students.csv'
DELIMITER ',' 
CSV HEADER 
NULL AS '' 
IGNORE_BLANK_LINES;

Jetzt werden leere Zeilen beim Import ignoriert.

Umgang mit ungewöhnlichem Datenformat

Manchmal musst du Daten importieren, die im Textformat statt als CSV vorliegen. Zum Beispiel sind die Zeilen durch Pipes | getrennt und es gibt keinen Header.

Beispiel für eine Datei:

1|John|Doe|john.doe@example.com
2|Jane|Smith|jane.smith@example.com

In diesem Fall kannst du folgenden Befehl nutzen:

COPY students(id, first_name, last_name, email)
FROM '/path/to/students.txt' 
DELIMITER '|' 
NULL AS '' 
CSV;

Wenn die Datei keinen Header hat, lass einfach den Parameter HEADER weg.

Praxisbeispiel für die Einstellung von Datenformaten

Szenario: Du hast eine Datei grades.tsv mit Noten der Studenten. Die Daten sehen so aus:

student_id  kurs_id   note
1   101     85
1   102     90
2   101     78
2   102     88
3   101     95
3   102 

Du sollst:

1. Die Datei so importieren, dass leere Werte als NULL interpretiert werden.
2. Sicherstellen, dass die Daten korrekt importiert wurden.

Lösung:

1. Erstelle die Tabelle grades:

CREATE TABLE grades (
    student_id INTEGER NOT NULL,
    course_id INTEGER NOT NULL,
    grade INTEGER
);

2. Importiere die Daten aus der Datei:

COPY grades(student_id, course_id, grade)
FROM '/path/to/grades.tsv' 
DELIMITER E'\t' 
NULL AS '' 
CSV HEADER;

3. Prüfe die importierten Daten:

SELECT * FROM grades;

Erwartetes Ergebnis:

Tipps und typische Fehler

Fehler: Falsches Trennzeichen. Wenn du das richtige Trennzeichen nicht angibst, gibt PostgreSQL einen Fehler aus oder importiert die Daten falsch. Zum Beispiel: Wird in der Datei ein Semikolon genutzt, du vergisst aber DELIMITER ';', dann sieht COPY die ganze Zeile als eine Spalte.

Fehler: Falsche Interpretation von NULL. Wenn du NULL AS '' nicht angibst, werden leere Werte als leere Strings interpretiert, was oft zu Fehlern bei Berechnungen oder Filtern führt.

Fehler: Falsches Datenformat. Falsche Einstellung des Trennzeichens oder Fehler in der Datei (z.B. Tab statt Leerzeichen) kann zu einem Fehler wie ERROR: null value in column violates not-null constraint führen.