MySQL Dump in CSV

April 17, 2013March 2, 2017Database, SQL

Und nochmals MySQL. Diesmal möchte ich Daten zwecks Umstellung auf ein anderes System in eine CSV exportieren. Einfach einen Query in ein File pipen ist zwar möglich, jedoch fehlen passt mir das ganze Bezüglich Formatierung nicht (Daten mit Tab getrennt). Ich möchte jedoch die Werte in Quotes und durch Komma getrennt.

SELECT * from Table
INTO OUTFILE 'querydump.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'

SELECT * from Table

INTO OUTFILE 'querydump.csv'

FIELDS TERMINATED BY ','

ENCLOSED BY '"'

LINES TERMINATED BY '\n'

Das File wird unter /var/lib/mysql// abgelegt.

Es geht natürlich auch direkt von der Konsole, hier kann zudem ein beliebiger Pfad gewählt werden

mysql -u  -p -e "USE ; SELECT * from
INTO OUTFILE '/tmp/querydump.csv' FIELDS TERMINATED BY '\,' ENCLOSED BY '\"' LINES TERMINATED BY '\n'";

1 2	mysql -u -p -e "USE ; SELECT * from INTO OUTFILE '/tmp/querydump.csv' FIELDS TERMINATED BY '\,' ENCLOSED BY '\"' LINES TERMINATED BY '\n'";

Im obigen Beispiel fehlen jedoch die Header (Tabellennamen), was aber oft oder meist benötigt wird.

Hierzu findet man einige Varianten. Eine davon ist das Auslesen der Header in eine weitere Datei und dann beide Dateien zusammenzufügen. Sowas wäre in etwa die entsprechende Abfrage:

mysql -u  -p -e "SELECT GROUP_CONCAT(COLUMN_NAME SEPARATOR ',') FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema='' and table_name='INTO OUTFILE '/tmp/querydump.csv' FIELDS TERMINATED BY '\,' ENCLOSED BY '\"' LINES TERMINATED BY '\n'";

1	mysql -u -p -e "SELECT GROUP_CONCAT(COLUMN_NAME SEPARATOR ',') FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema='' and table_name='INTO OUTFILE '/tmp/querydump.csv' FIELDS TERMINATED BY '\,' ENCLOSED BY '\"' LINES TERMINATED BY '\n'";

‘”Mein Problem ist jedoch, dass ich gewisse Spalten beim Export zusammenfüge (zb. SELECT vorname, nachname AS fullname). Dann passen natürlich die Anzahl Zeilen hier nicht mehr. Irgenwie wirds mir hier zu blöd, um ellenlange Commands zu tippen oder Skripte hierfür zusammenzubasteln (Beispiele siehe u.a. hier). Ich pipe deswegen die Bildschirm-Ausgabe (welche die Header ja beinhaltet) in ein File und bastle dann halt den Header selbst zurecht… (erste Zeile kopieren, in CSV einfügen und Quotes, Kommas setzen)

SELECT Vorname, Nachname AS fullname, <weitere queries...> FROM

1	SELECT Vorname, Nachname AS fullname, <weitere queries...> FROM

Wieso nicht gleich so ? Weil hier die leeren Spalte nicht angezeigt werden, sprich also auch nicht exportiert werden….

Dieses File kann dann zb mit sed zurechtgestutzt werden. Hier wird nur die erste Zeile mit head ausgegeben, alle Wörter mit sed in Anführungszeichen gesetzt (je nach dem muss das natürlich angepasst werden) und zu guter Letzt werden mit tr die Tabulatoren durch ein Komma ersetzt… geht wahrscheinlich auch mit einem einzelnen sed…aber dann kanns wahrscheinlich kein Mensch mehr lesen 😉

cat  |head -n 1|sed 's/\([A-Za-z_]\+\)/"\1"/g'|tr -s "\t" "\,"

1	cat \|head -n 1\|sed 's/$[A-Za-z_]\+$/"\1"/g'\|tr -s "\t" "\,"

Das ganze kann natürlich bereits beim Export des Dumps gemacht werden

SELECT Vorname, Nachname AS fullname, <weitere queries...> FROM

1	SELECT Vorname, Nachname AS fullname, <weitere queries...> FROM

|head -n 1|sed ‘s/$[A-Za-z_]\+$/”\1″/g’|tr -s “\t” “\,” >

Nun schauts ganz gut aus, jedoch ist der Header am Schluss des Files…nochmals umbauen..

sed -i '1h;1d;$!H;$!d;G'

1	sed -i '1h;1d;$!H;$!d;G'

Mein Fazit zum Ganzen: es wäre angenehm, wenn man direkt definieren könnte, ob die Header mitexportiert werden sollen, würde das ganze wesentlich bequemer machen…