Intelligente Lösungen
in neuer Dimension

CSV-Dateien und SQL

Gelegentlich muß ich mit CSV-Dateien arbeiten und Auswertungen über diese erstellen oder die Sortierung ändern oder ein anderes Dateiformat erzeugen (beispielsweise Markdown).

Bislang habe ich dazu verwendet:

  • Q
  • eigene Shell-Skripte

Andere potentielle Lösungen/Hilfsmittel, die ich mir vorgemerkt aber noch nicht ausprobiert habe:

Nun scheint es so zu sein, dass man das auch sehr elegant mit SQLITE hinbekommen kann. Hier meine Erfahrungen damit!

Erste Tests

Zuerst habe ich einige grundlegende Tests ausgeführt. Die Erkenntnisse sind vielversprechend!

Testdatei

002.csv

1
2
3
4
5
6

email,start,end,dauer-gruen,dauer-gelb,dauer-rot,dauer   
g2453@gmx.de,2021-11-01_07:09:24,2021-11-01_21:08:14,07:29:25,00:08:45,06:20:09,13:58:50
g2453@gmx.de,2021-11-02_07:03:42,2021-11-02_19:07:13,10:00:17,02:03:07,00:00:00,12:03:30
g2453@gmx.de,2021-11-03_06:37:06,2021-11-03_23:57:23,12:30:54,01:12:24,03:37:25,17:20:16
g2453@gmx.de,2021-11-04_07:08:03,2021-11-04_22:49:48,11:53:51,03:02:31,00:47:39,15:41:44
...

Simple Abfragen

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

$ sqlite3 :memory: -cmd '.mode csv' -cmd '.import 002.csv timetable' 'SELECT * FROM timetable'
g2453@gmx.de,2021-11-01_07:09:24,2021-11-01_21:08:14,07:29:25,00:08:45,06:20:09,13:58:50
g2453@gmx.de,2021-11-02_07:03:42,2021-11-02_19:07:13,10:00:17,02:03:07,00:00:00,12:03:30
g2453@gmx.de,2021-11-03_06:37:06,2021-11-03_23:57:23,12:30:54,01:12:24,03:37:25,17:20:16
...

$ sqlite3 :memory: -cmd '.mode csv' -cmd '.import 002.csv timetable' 'SELECT * FROM timetable ORDER BY start DESC'
...
g2453@gmx.de,2021-11-03_06:37:06,2021-11-03_23:57:23,12:30:54,01:12:24,03:37:25,17:20:16
g2453@gmx.de,2021-11-02_07:03:42,2021-11-02_19:07:13,10:00:17,02:03:07,00:00:00,12:03:30
g2453@gmx.de,2021-11-01_07:09:24,2021-11-01_21:08:14,07:29:25,00:08:45,06:20:09,13:58:50

$ sqlite3 :memory: -cmd '.mode csv' -cmd '.import 002.csv timetable' -cmd '.header on' 'SELECT * FROM timetable ORDER BY start DESC'
email,start,end,dauer-gruen,dauer-gelb,dauer-rot,"dauer   "
...
g2453@gmx.de,2021-11-03_06:37:06,2021-11-03_23:57:23,12:30:54,01:12:24,03:37:25,17:20:16
g2453@gmx.de,2021-11-02_07:03:42,2021-11-02_19:07:13,10:00:17,02:03:07,00:00:00,12:03:30
g2453@gmx.de,2021-11-01_07:09:24,2021-11-01_21:08:14,07:29:25,00:08:45,06:20:09,13:58:50

Generierung von Markdown-Tabellen

Die Generierung von Markdown-Tabellen funktioniert mit sqlite3 von Ubuntu-20.04 leider nicht, sie ist zu alt. Die Version von Ubuntu-22.04 bereitet (vermutlich) keine Probleme!

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

$ sqlite3 :memory: -cmd '.mode csv' -cmd '.import 002.csv timetable' -cmd '.mode markdown' 'SELECT * FROM timetable ORDER BY start DESC'
Error: mode should be one of: ascii column csv html insert line list quote tabs tcl
g2453@gmx.de,2021-11-30_07:48:06,2021-11-30_17:28:27,09:15:06,00:25:17,00:00:00,09:40:20
g2453@gmx.de,2021-11-29_06:34:03,2021-11-29_22:52:56,11:01:53,05:09:30,00:08:20,16:18:52
g2453@gmx.de,2021-11-28_09:12:10,2021-11-28_11:20:13,01:52:41,00:00:00,00:15:34,02:08:02
...

$ sqlite3 --version
3.31.1 2020-01-27 19:55:54 3bfa9cc97da10598521b342961df8f5f68c7388fa117345eeb516eaa837balt1

$ /tmp/sqlite-tools-linux-x86-3390100/sqlite3 :memory: -cmd '.mode csv' -cmd '.import 002.csv timetable' -cmd '.mode markdown' 'SELECT * FROM timetable ORDER BY start DESC'
|    email     |        start        |         end         | dauer-gruen | dauer-gelb | dauer-rot | dauer    |
|--------------|---------------------|---------------------|-------------|------------|-----------|----------|
| g2453@gmx.de | 2021-11-30_07:48:06 | 2021-11-30_17:28:27 | 09:15:06    | 00:25:17   | 00:00:00  | 09:40:20 |
| g2453@gmx.de | 2021-11-29_06:34:03 | 2021-11-29_22:52:56 | 11:01:53    | 05:09:30   | 00:08:20  | 16:18:52 |
| g2453@gmx.de | 2021-11-28_09:12:10 | 2021-11-28_11:20:13 | 01:52:41    | 00:00:00   | 00:15:34  | 02:08:02 |
...

$ /tmp/sqlite-tools-linux-x86-3390100/sqlite3 --version
3.39.1 2022-07-13 19:41:41 7c16541a0efb3985578181171c9f2bb3fdc4bad6a2ec85c6e31ab96f3eff201f

Tiefergehende Tests

Mehrere CSV-Dateien

Die Verarbeitung mehrerer CSV-Dateien funktioniert problemlos! Man kann auch sehr leicht tabellenübergreifende SQL-Abfragen ausführen!

002.csv

1
2
3
4
5
6

email,start,end,dauer-gruen,dauer-gelb,dauer-rot,dauer   
g2453@gmx.de,2021-11-01_07:09:24,2021-11-01_21:08:14,07:29:25,00:08:45,06:20:09,13:58:50
g2453@gmx.de,2021-11-02_07:03:42,2021-11-02_19:07:13,10:00:17,02:03:07,00:00:00,12:03:30
g2453@gmx.de,2021-11-03_06:37:06,2021-11-03_23:57:23,12:30:54,01:12:24,03:37:25,17:20:16
g2453@gmx.de,2021-11-04_07:08:03,2021-11-04_22:49:48,11:53:51,03:02:31,00:47:39,15:41:44
...

003.csv

1
2
3

email,vorname,nachname
g2453@gmx.de,Uli,Heller
h9876@gmx.de,Heinz,Becker

Kombinierte Abfrage:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

$ sqlite3 :memory: -cmd '.mode csv' -cmd '.import 002.csv timetable'  -cmd '.import 003.csv names'  -cmd '.mode markdown' 'SELECT * FROM timetable, names where timetable.email=names.email'
|    email     |        start        |         end         | dauer-gruen | dauer-gelb | dauer-rot | dauer    |    email     | vorname | nachname |
|--------------|---------------------|---------------------|-------------|------------|-----------|----------|--------------|---------|----------|
| g2453@gmx.de | 2021-11-01_07:09:24 | 2021-11-01_21:08:14 | 07:29:25    | 00:08:45   | 06:20:09  | 13:58:50 | g2453@gmx.de | Uli     | Heller   |
| g2453@gmx.de | 2021-11-02_07:03:42 | 2021-11-02_19:07:13 | 10:00:17    | 02:03:07   | 00:00:00  | 12:03:30 | g2453@gmx.de | Uli     | Heller   |
| g2453@gmx.de | 2021-11-03_06:37:06 | 2021-11-03_23:57:23 | 12:30:54    | 01:12:24   | 03:37:25  | 17:20:16 | g2453@gmx.de | Uli     | Heller   |
...

$ sqlite3 :memory: -cmd '.mode csv' -cmd '.import 002.csv timetable'  -cmd '.import 003.csv names'  -cmd '.mode markdown' 'SELECT vorname,nachname,start,end FROM timetable, names where timetable.email=names.email'
| vorname | nachname |        start        |         end         |
|---------|----------|---------------------|---------------------|
| Uli     | Heller   | 2021-11-01_07:09:24 | 2021-11-01_21:08:14 |
| Uli     | Heller   | 2021-11-02_07:03:42 | 2021-11-02_19:07:13 |
| Uli     | Heller   | 2021-11-03_06:37:06 | 2021-11-03_23:57:23 |
...

Trenner in Eingangsdateien

Der Trenner in Eingangsdateien kann sehr leicht angepasst werden, einfach mittels .separator NEUER-TRENNER einen neuen Trenner vorgeben!

004.csv

1
2
3

email;vorname;nachname
g2453@gmx.de;Uli;Heller
h9876@gmx.de;Heinz;Becker

Abfrage:

1
2
3
4
5
6
7
8
9
10
11

$ sqlite3 :memory: -cmd '.mode csv' -cmd '.import 004.csv names'  -cmd '.mode markdown' 'SELECT * FROM names'
|  email;vorname;nachname   |
|---------------------------|
| g2453@gmx.de;Uli;Heller   |
| h9876@gmx.de;Heinz;Becker |

$ sqlite3 :memory: -cmd '.mode csv' -cmd '.separator ;' -cmd '.import 004.csv names'  -cmd '.mode markdown' 'SELECT * FROM names'
|    email     | vorname | nachname |
|--------------|---------|----------|
| g2453@gmx.de | Uli     | Heller   |
| h9876@gmx.de | Heinz   | Becker   |

Trenner in Ausgabedateien

Der Trenner in Eingangsdateien kann sehr leicht angepasst werden, einfach mittels .separator NEUER-TRENNER einen neuen Trenner vorgeben!

1
2
3
4
5
6
7
8
9

$ sqlite3 :memory: -cmd '.mode csv' -cmd '.import 003.csv names' -cmd '.header on' 'SELECT * FROM names'
email,vorname,nachname
g2453@gmx.de,Uli,Heller
h9876@gmx.de,Heinz,Becker

$ sqlite3 :memory: -cmd '.mode csv' -cmd '.import 003.csv names' -cmd '.header on' -cmd '.separator ;' 'SELECT * FROM names'
email;vorname;nachname
g2453@gmx.de;Uli;Heller
h9876@gmx.de;Heinz;Becker

Markdown als Eingangsformat

Funktioniert nicht so richtig!

005.md

1
2
3
4

|    email     | vorname | nachname |
|--------------|---------|----------|
| g2453@gmx.de | Uli     | Heller   |
| h9876@gmx.de | Heinz   | Becker   |

Abfrage:

1
2
3
4
5
6
7
8

$ sqlite3 :memory: -cmd '.mode markdown' -cmd '.import 005.md names' -cmd '.mode csv' -cmd '.header on' 'SELECT * FROM names'
Columns renamed during .import 005.md due to duplicates:
"?" to "?_1",
"?" to "?_5"
?_1,"    email     "," vorname "," nachname ",?_5
"",--------------,---------,----------,""
""," g2453@gmx.de "," Uli     "," Heller   ",""
""," h9876@gmx.de "," Heinz   "," Becker   ",""

Links

Historie

  • 2022-08-14: Neues Repo für CSV-Dateien: gitea –> github
  • 2022-07-16: Erste Version