white_rabbit hat geschrieben: ↑So 20. Nov 2022, 16:17
Was hat es damit auf sich bzw wie hast Du das dort eingefügt?
Hallo,
vRECHNUNGEN ist eine Ansicht, Rechtsklick,
in SQL-Ansicht bearbeiten ..., dann siehst du was dahinter steckt.
Tja, und wenn die .csv-Datei immer so ankommt wie oben angegeben, dann wird es schwierig.
Anbei mal ein Beispiel, wie man eine .csv Datei als TEXT TABLE in eine HSQL-Datenbank zur Laufzeit importieren kann.
Dazu habe ich folgende SQL-Befehle ausgeführt:
Code: Alles auswählen
CREATE TEXT TABLE "T_DATA_IMPORT"("ID" INTEGER NOT NULL PRIMARY KEY,"Nachname" VARCHAR(50),"Rufname" VARCHAR(50),"1as" VARCHAR(50),"1bi" VARCHAR(5),"1ch" VARCHAR(5),"1de" VARCHAR(5),"1ek" VARCHAR(5),"1en" VARCHAR(5),"1fr" VARCHAR(5),"1ge" VARCHAR(5),"1gr" VARCHAR(5),"1if" VARCHAR(5),"1ku" VARCHAR(5),"1la" VARCHAR(5),"1ma" VARCHAR(5),"1mu" VARCHAR(5),"1nw" VARCHAR(5),"1ph" VARCHAR(5),"1pl" VARCHAR(5),"1po" VARCHAR(5),"1pr" VARCHAR(5),"1re" VARCHAR(5),"1rk" VARCHAR(5),"1sn" VARCHAR(5),"1sp" VARCHAR(5),"1st" VARCHAR(5),"1tg" VARCHAR(5),"1wm" VARCHAR(5))
Nachdem die TEXT-Tabelle definiert ist, muss man die .csv verbinden:
Code: Alles auswählen
SET TABLE "T_DATA_IMPORT" SOURCE "DataImport.csv;fs=\semi;ignore_first=true;all_quoted=false;quoted=false;encoding=UTF-8"
möchte man die Verbindung wieder aufheben, geht das mit
Mit folgender Ansicht,
vKONTAKTE_KURSE, kann man die Daten in die gewünschte Reihenfolge bringen:
Code: Alles auswählen
SELECT "ID" AS FK_ID_KONTAKT, "1as" AS FK_ID_KURS FROM "T_DATA_IMPORT" WHERE "1as" IS NOT NULL
UNION SELECT "ID", "1bi" FROM "T_DATA_IMPORT" WHERE "1bi" IS NOT NULL
UNION SELECT "ID", "1ch" FROM "T_DATA_IMPORT" WHERE "1ch" IS NOT NULL
UNION SELECT "ID", "1de" FROM "T_DATA_IMPORT" WHERE "1de" IS NOT NULL
UNION SELECT "ID", "1ek" FROM "T_DATA_IMPORT" WHERE "1ek" IS NOT NULL
UNION SELECT "ID", "1en" FROM "T_DATA_IMPORT" WHERE "1en" IS NOT NULL
UNION SELECT "ID", "1fr" FROM "T_DATA_IMPORT" WHERE "1fr" IS NOT NULL
UNION SELECT "ID", "1ge" FROM "T_DATA_IMPORT" WHERE "1ge" IS NOT NULL
UNION SELECT "ID", "1gr" FROM "T_DATA_IMPORT" WHERE "1gr" IS NOT NULL
UNION SELECT "ID", "1if" FROM "T_DATA_IMPORT" WHERE "1if" IS NOT NULL
UNION SELECT "ID", "1ku" FROM "T_DATA_IMPORT" WHERE "1ku" IS NOT NULL
UNION SELECT "ID", "1la" FROM "T_DATA_IMPORT" WHERE "1la" IS NOT NULL
UNION SELECT "ID", "1ma" FROM "T_DATA_IMPORT" WHERE "1ma" IS NOT NULL
UNION SELECT "ID", "1mu" FROM "T_DATA_IMPORT" WHERE "1mu" IS NOT NULL
UNION SELECT "ID", "1nw" FROM "T_DATA_IMPORT" WHERE "1nw" IS NOT NULL
UNION SELECT "ID", "1ph" FROM "T_DATA_IMPORT" WHERE "1ph" IS NOT NULL
UNION SELECT "ID", "1pl" FROM "T_DATA_IMPORT" WHERE "1pl" IS NOT NULL
UNION SELECT "ID", "1po" FROM "T_DATA_IMPORT" WHERE "1po" IS NOT NULL
UNION SELECT "ID", "1pr" FROM "T_DATA_IMPORT" WHERE "1pr" IS NOT NULL
UNION SELECT "ID", "1re" FROM "T_DATA_IMPORT" WHERE "1re" IS NOT NULL
UNION SELECT "ID", "1rk" FROM "T_DATA_IMPORT" WHERE "1rk" IS NOT NULL
UNION SELECT "ID", "1sn" FROM "T_DATA_IMPORT" WHERE "1sn" IS NOT NULL
UNION SELECT "ID", "1sp" FROM "T_DATA_IMPORT" WHERE "1sp" IS NOT NULL
UNION SELECT "ID", "1st" FROM "T_DATA_IMPORT" WHERE "1st" IS NOT NULL
UNION SELECT "ID", "1tg" FROM "T_DATA_IMPORT" WHERE "1tg" IS NOT NULL
UNION SELECT "ID", "1wm" FROM "T_DATA_IMPORT" WHERE "1wm" IS NOT NULL
Beispiel anbei, .csv und .odb müssen im gleichen Ordner liegen.
Gruß R