Ü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


zurück zur Übersicht

Änderungsstand: 28-Jul-2021  09:17
Heinz Wember