Überführung einer Excel-Tabelle in eine MySQL-Tabelle:
Beispiel Belegung des Friedhofes Kriegshaber in eine MySQL-Tabelle
Allgemeines:
Die Erstellung einer großen Excel-Tabelle ist in der Regel
bequemer als dieselben Daten über Admin in eine MySQL-Tabelle
einzugeben.
Nachdem es kein Problem ist, eine Excel-Tabelle in eine
MySQL-Tabelle zu importieren, möchte ich Wichtiges zuerst
erläutern.
Eine MySQL-Tabelle muss ein Feld mit einem Primärschlüssel
haben. Also am besten das erste Feld id bei der Excel-Tabelle id nennen
und mit einer laufenden Nummer definieren.
Die erstellte MySQL-Tabelle muss exakt dieselbe Spaltenanzahl haben.
Es ist sinnvoll, dass das letzte Feld immer einen Wert enthält,
z.B. Feldname z.B. leer nennen und den Alphawert NULL definieren.
Nachdem Windows und Linux unterschiedliche Characterwerte haben,
ist es sinnvoll, in der Exceltabelle nur US-amerikanische Zeichen zu
verwenden.
Also statt ä ae, ö oe, ü ue, ß sz (nicht ss!) ,
Ä Ae, Ö Oe und Ü Ue, natürlich auch keine Akzente.
Dies kann man später teilweise über Massendatenänderung
über vi ändern, Akzente und ausländische Zeichen muss
man später in MySQL ändern.
Beispiel der Doku aus dem Internet:
LOAD DATA LOCAL INFILE "/path/to/boats.csv" INTO TABLE boatdb.boats
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(id, name, type, owner_id, @datevar, rental_price)
set date_made = STR_TO_DATE(@datevar,'%m/%d/%Y');
Probleme:
1. Der Standardexport aus Excel über CSV sieht folgermaßen aus:
Blatt;Feld;Reihe;Nummer;Name des
Grabbesitzers;Vorname;Straße;Nr.;;Ort;Datum Erwerb;Preis;Dauer
bis ;Name des Beerdigten;Vorname;Gerburtsdatum;Beerdigung;Bemerkung
1;2;;;Boeck;Olga;Mathildenstr. G49;8;;Augsburg;01.09.1952;100,00;1967;Boeck;Anna;20.10.1906;23.08.1952;
1;2;;;Boeck;Olga;Mathildenstr. ;8;;Augsburg;01.09.1967;150,00;1982;Boeck;Peter;09.05.1904;04.03.1964;
1;10;1;1;Gruber;Karl;Langenmarckstr. ;36;;Augsburg;01.11.1964;60,00;1979;Gruber;Margarete;19.11.1901;12.10.1964;
3;8;1;1;Fischer;Josefa;Altersheim;;;Seyfreidsberg;01.01.1947;45,00;1959;Fischer;Max;01.01.1876;;
3;8;1;1;Fischer;Josefa;Altersheim;;;Seyfreidsberg;01.01.1947;45,00;1959;Fischer;Josefa;15.12.1865;26.11.1952;Ueberfuehrung
3;8;1;1;Bellherr;Wilhelm;Elsterweg ;24;;Augsburg;01.02.1959;45,00;1974;Bellherr;Anna;26.01.1904;12.08.1967;
3;10;1;3;Kramer;Maria;Ulmer Landstr. ;281;;Stadtbergen;01.09.1964;60,00;1979;Martin;Anna;4.8.1875;12.11.1964;
3;10;1;3;Kramer;Maria;Ulmer Landstr. ;281;;Stadtbergen;01.09.1979;18,00;1982;Kramer;Rudolf;29.3.1897;01.02.1969;
3;10;1;3;Schaefer;Charlotte;East Norwich;;;New York USA;01.09.1964;;;Kramer;Elisabeth;22.11.1898;28.10.1970;
4;4;1;1;Weber;Wilhelm;Habsburgerstr.;;;Augsburg;11.06.1919;30,00;1934;Weber;Wilhelm;1.1.1872;05.01.1917;
4;8;1;21;Gassstatter;Xaver;Markgrafenstr.;9;;Augsburg;01.01.1946;45,00;1960;Gassstatter ;Amanda;01.01.1939;3.081.957;
4;8;1;21;Gruber;Afra;Drosselweg ;11;a;Augsburg;01.09.1954;45,00;1969;Wirth;Afra;13.5.1897;31.08.1954;
4;8;1;21;Gruber;Afra;Drosselweg ;11;a;Augsburg;01.09.1969;80,00;1984;Gruber;Markus;17.11.1908;06.07.1965;
4;10;1;4;Schenk;Robert;Vogesenstr. ;64;;Augsburg;01.12.1964;60,00;1979;Schenk;Stefanie;13.11.1897;02.12.1964;
1.1 Hier fehlt ich der Wert id und die Anzahl der Spalten ist nicht gleich!
1.2 Dezimalzahlen
Wenn man diese Werte mit Dezimalzahlen eingibt, dann den Dezimalpunkt
nicht das Dezimalkomma verwenden, weil man den Standardexport am besten
bei MySQL mit , definiert. Also als erstes die CSV-Datei von ; nach ,
ändern.
Ablauf:
Die bereinigten CSV-Daten nach dem Muster (siehe oben) anpassen.
Beispiel der exportierten CSV-Datei. Hier zum Test mir id und Feld leer:
1;1;2;;;Boeck;Olga;Mathildenstr. G49;8;;Augsburg;01.09.1952;100,00;1967;Boeck;Anna;20.10.1906;23.08.1952;;NULL
2;1;2;;;Boeck;Olga;Mathildenstr. ;8;;Augsburg;01.09.1967;150,00;1982;Boeck;Peter;09.05.1904;04.03.1964;;NULL
3;1;10;1;1;Gruber;Karl;Langenmarckstr. ;36;;Augsburg;01.11.1964;60,00;1979;Gruber;Margarete;19.11.1901;12.10.1964;;NULL
4;3;8;1;1;Fischer;Josefa;Altersheim;;;Seyfreidsberg;01.01.1947;45,00;1959;Fischer;Max;01.01.1876;;;NULL
5;3;8;1;1;Fischer;Josefa;Altersheim;;;Seyfreidsberg;01.01.1947;45,00;1959;Fischer;Josefa;15.12.1865;26.11.1952;Ueberfuehrung;NULL
6;3;8;1;1;Bellherr;Wilhelm;Elsterweg ;24;;Augsburg;01.02.1959;45,00;1974;Bellherr;Anna;26.01.1904;12.08.1967;;NULL
7;3;10;1;3;Kramer;Maria;Ulmer Landstr. ;281;;Stadtbergen;01.09.1964;60,00;1979;Martin;Anna;4.8.1875;12.11.1964;;NULL
8;3;10;1;3;Kramer;Maria;Ulmer Landstr. ;281;;Stadtbergen;01.09.1979;18,00;1982;Kramer;Rudolf;29.3.1897;01.02.1969;;NULL
9;3;10;1;3;Schaefer;Charlotte;East Norwich;;;New York USA;01.09.1964;;;Kramer;Elisabeth;22.11.1898;28.10.1970;;NULL
10;4;4;1;1;Weber;Wilhelm;Habsburgerstr.;;;Augsburg;11.06.1919;30,00;1934;Weber;Wilhelm;1.1.1872;05.01.1917;;NULL
11;4;8;1;21;Gassstatter;Xaver;Markgrafenstr.;9;;Augsburg;01.01.1946;45,00;1960;Gassstatter ;Amanda;01.01.1939;3.081.957;;NULL
12;4;8;1;21;Gruber;Afra;Drosselweg ;11;a;Augsburg;01.09.1954;45,00;1969;Wirth;Afra;13.5.1897;31.08.1954;:NULL
13;4;8;1;21;Gruber;Afra;Drosselweg ;11;a;Augsburg;01.09.1969;80,00;1984;Gruber;Markus;17.11.1908;06.07.1965;;NULL
14;4;10;1;4;Schenk;Robert;Vogesenstr. ;64;;Augsburg;01.12.1964;60,00;1979;Schenk;Stefanie;13.11.1897;02.12.1964;NULL
Tabelle aufbauen:
create table friedhofsbelegungneu (
id int(4) default NULL,
blatt varchar(5) default null,
feld varchar(2) default null,
reihe varchar(2) default null,
nummer varchar(3) default null,
namegb varchar(99) default null,
vorname varchar(99) default null,
strasse varchar(99) default null,
hnr varchar(99) default null,
unbekannt varchar(99) default null,
ort varchar(99) default null,
datum varchar(10) default null,
preis double(4,2),
dauer date,
namevst varchar(99) default null,
vornamevst varchar(99) default null,
gebdat varchar(10) default null,
dastberd varchar(8) default null,
bemerkung varchar(999) default null,
leer varchar(999) default "NULL")
ENGINE=InnoDB DEFAULT CHARSET=utf8;
COMMIT;
Beispiel der CSV-Datei zum Laden der Tabelle:
1,1,2,,,Boeck,Olga,Mathildenstr. G49,8,,Augsburg,01.09.1952,100.00,1967,Boeck,Anna,20.10.1906,23.08.1952,,NULL
2,1,2,,,Boeck,Olga,Mathildenstr. ,8,,Augsburg,01.09.1967,150.00,1982,Boeck,Peter,09.05.1904,04.03.1964,,NULL
3,1,10,1,1,Gruber,Karl,Langenmarckstr. ,36,,Augsburg,01.11.1964,60.00,1979,Gruber,Margarete,19.11.1901,12.10.1964,,NULL
4,3,8,1,1,Fischer,Josefa,Altersheim,,,Seyfreidsberg,01.01.1947,45.00,1959,Fischer,Max,01.01.1876,,NULL
5,3,8,1,1,Fischer,Josefa,Altersheim,,,Seyfreidsberg,01.01.1947,45.00,1959,Fischer,Josefa,15.12.1865,26.11.1952,Ueberfuehrung,NULL
6,3,8,1,1,Bellherr,Wilhelm,Elsterweg ,24,,Augsburg,01.02.1959,45.00,1974,Bellherr,Anna,26.01.1904,12.08.1967,,NULL
7,3,10,1,3,Kramer,Maria,Ulmer Landstr. ,281,,Stadtbergen,01.09.1964,60.00,1979,Martin,Anna,4.8.1875,12.11.1964,,NULL
8,3,10,1,3,Kramer,Maria,Ulmer Landstr. ,281,,Stadtbergen,01.09.1979,18.00,1982,Kramer,Rudolf,29.3.1897,01.02.1969,,NULL
9,3,10,1,3,Schaefer,Charlotte,East Norwich,,,New York USA,01.09.1964,,,Kramer,Elisabeth,22.11.1898,28.10.1970,,NULL
10,4,4,1,1,Weber,Wilhelm,Habsburgerstr.,,,Augsburg,11.06.1919,30.00,1934,Weber,Wilhelm,1.1.1872,05.01.1917,,NULL
11,4,8,1,21,Gassstatter,Xaver,Markgrafenstr.,9,,Augsburg,01.01.1946,45.00,1960,Gassstatter ,Amanda,01.01.1939,3.081.957,,NULL
12,4,8,1,21,Gruber,Afra,Drosselweg ,11,a,Augsburg,01.09.1954,45.00,1969,Wirth,Afra,13.5.1897,31.08.1954,,NULL
13,4,8,1,21,Gruber,Afra,Drosselweg ,11,a,Augsburg,01.09.1969,80.00,1984,Gruber,Markus,17.11.1908,06.07.1965,,NULL
14,4,10,1,4,Schenk,Robert,Vogesenstr. ,64,,Augsburg,01.12.1964,60.00,1979,Schenk,Stefanie,13.11.1897,02.12.1964,,NULL
Es muss aber sichergestellt sein, dass bei den Daten mit Typ numerisch
mit Kommastelle beim Eintrag nicht das Komma (,) sondern ein Punkt (.)
gewählt wird.
Damit es beim Laden der Daten keine Probleme gibt, wurde
sichergestellt, dass auch das letzte Feld einen Wert enthält, hier
also NULL. Dieser varchar-Wert kann später mittels
Massendatenänderung zu einem richitigen Wert NULL geändert
werden.
Beispiel:
LOAD DATA LOCAL INFILE "beispieldatei.csv" INTO TABLE db288973340.friedhofsbelegungneu
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 0 LINES;
Importoptionen:
Import-Ergebnis:
Beispiel für den Import von 14 Sätzen:
INSERT INTO `friedhofsbelegungneu` (`id`, `blatt`,
`feld`, `reihe`, `nummer`, `namegb`, `vorname`, `strasse`, `hnr`,
`unbekannt`, `ort`, `datum`, `preis`, `dauer`, `namevst`, `vornamevst`,
`gebdat`, `dastberd`, `bemerkung`, `leer`) VALUES
(1, '1', '2', '', '', 'Boeck', 'Olga', 'Mathildenstr.G49', '8', '',
'Augsburg', '01.09.1952', 99.99, '0000-00-00', 'Boeck', 'Anna',
'20.10.1906', '23.08.19', '', 'NULL<br>'),
(2, '1', '2', '', '', 'Boeck', 'Olga', 'Mathildenstr.', '8', '',
'Augsburg', '01.09.1967', 99.99, '0000-00-00', 'Boeck', 'Peter',
'09.05.1904', '04.03.19', '', 'NULL<br>'),
(3, '1', '10', '1', '1', 'Gruber', 'Karl', 'Langenmarckstr.', '36', '',
'Augsburg', '01.11.1964', 60.00, '0000-00-00', 'Gruber', 'Margarete',
'19.11.1901', '12.10.19', '', 'NULL<br>'),
(4, '3', '8', '1', '1', 'Fischer', 'Josefa', 'Altersheim', '', '', '',
'Seyfreidsb', 1.01, '0000-00-00', '1959', 'Fischer', 'Max', '01.01.18',
'', 'NULL<br>'),
(5, '3', '8', '1', '1', 'Fischer', 'Josefa', 'Altersheim', '', '',
'Seyfreidsberg', '01.01.1947', 45.00, '0000-00-00', 'Fischer',
'Josefa', '15.12.1865', '26.11.19', 'Ueberfuehrung', 'NULL<br>'),
(6, '3', '8', '1', '1', 'Bellherr', 'Wilhelm', 'Elsterweg', '24', '',
'Augsburg', '01.02.1959', 45.00, '0000-00-00', 'Bellherr', 'Anna',
'26.01.1904', '12.08.19', '', 'NULL<br>'),
(7, '3', '10', '1', '3', 'Kramer', 'Maria', 'UlmerLandstr.', '281', '',
'Stadtbergen', '01.09.1964', 60.00, '0000-00-00', 'Martin', 'Anna',
'4.8.1875', '12.11.19', '', 'NULL<br>'),
(8, '3', '10', '1', '3', 'Kramer', 'Maria', 'UlmerLandstr.', '281', '',
'Stadtbergen', '01.09.1979', 18.00, '0000-00-00', 'Kramer', 'Rudolf',
'29.3.1897', '01.02.19', '', 'NULL<br>'),
(9, '3', '10', '1', '3', 'Schaefer', 'Charlotte', 'EastNorwich', '',
'', 'NewYorkUSA', '01.09.1964', 0.00, '0000-00-00', 'Kramer',
'Elisabeth', '22.11.1898', '28.10.19', '', 'NULL<br>'),
(10, '4', '4', '1', '1', 'Weber', 'Wilhelm', 'Habsburgerstr.', '', '',
'Augsburg', '11.06.1919', 30.00, '0000-00-00', 'Weber', 'Wilhelm',
'1.1.1872', '05.01.19', '', 'NULL<br>'),
(11, '4', '8', '1', '21', 'Gassstatter', 'Xaver', 'Markgrafenstr.',
'9', '', 'Augsburg', '01.01.1946', 45.00, '0000-00-00', 'Gassstatter',
'Amanda', '01.01.1939', '3.081.95', '', 'NULL<br>'),
(12, '4', '8', '1', '21', 'Gruber', 'Afra', 'Drosselweg', '11', 'a',
'Augsburg', '01.09.1954', 45.00, '0000-00-00', 'Wirth', 'Afra',
'13.5.1897', '31.08.19', '', 'NULL<br>'),
(13, '4', '8', '1', '21', 'Gruber', 'Afra', 'Drosselweg', '11', 'a',
'Augsburg', '01.09.1969', 80.00, '0000-00-00', 'Gruber', 'Markus',
'17.11.1908', '06.07.19', '', 'NULL<br>'),
(14, '4', '10', '1', '4', 'Schenk', 'Robert', 'Vogesenstr.', '64', '',
'Augsburg', '01.12.1964', 60.00, '0000-00-00', 'Schenk', 'Stefanie',
'13.11.1897', '02.12.19', '', NULL);
--
-- Indizes der exportierten Tabellen
--
--
-- Indizes für die Tabelle `friedhofsbelegungneu`
--
ALTER TABLE `friedhofsbelegungneu`
ADD PRIMARY KEY (`id`);
COMMIT;
Nun folgt die Nachbereitung:
1. Primary Index für Spalte id definieren
2. Export der Tabelle:
Massendatenänderung für die Umlaute
3. Import der geänderten Exportdatei
Änderungsstand: 28-Jul-2021 09:17
Heinz Wember