Erzeugen eines Tabellenschemas durch Überprüfung von Excel(CSV) und Importieren von Daten

Question

Wie kann ich ein MYSQL-Tabellenschema erstellen, indem ich eine Excel- (oder CSV-) Datei untersuche? Gibt es irgendwelche fertigen Python-Bibliotheken für diese Aufgabe?

Die Spaltenüberschriften werden in Spaltennamen umgewandelt. Der Datentyp wird auf der Grundlage des Inhalts der Tabellenkalkulationsspalte geschätzt. Danach werden die Daten in die Tabelle geladen.

Ich habe eine Excel-Datei mit ca. 200 Spalten, die ich normalisieren möchte.

Answer 1

Verwenden Sie die xlrd Modul; hier beginnen . [Haftungsausschluss: Ich bin der Autor]. xlrd klassifiziert Zellen in Text, Zahl, Datum, Boolesche Werte, Fehler, Leerzeichen und leer. Es unterscheidet Datumsangaben von Zahlen, indem es das mit der Zelle verbundene Format prüft (z. B. "tt/mm/jjjj" gegenüber "0,00").

Die Aufgabe, einen Code zu programmieren, der die vom Benutzer eingegebenen Daten durchforstet, um zu entscheiden, welcher DB-Datentyp für die einzelnen Spalten verwendet werden soll, lässt sich nicht einfach automatisieren. Sie sollten in der Lage sein, die Daten in Augenschein zu nehmen und Typen wie Integer, Money, Text, Datum, Datetime, Time usw. zuzuordnen und Code zu schreiben, um Ihre Vermutungen zu überprüfen. Beachten Sie, dass Sie in der Lage sein müssen, mit Dingen wie numerischen oder Datumsdaten umzugehen, die in Textfelder eingegeben werden (die in der grafischen Benutzeroberfläche gut aussehen können). Sie brauchen eine Strategie für den Umgang mit Zellen, die nicht dem "geschätzten" Datentyp entsprechen. Sie müssen Ihre Daten validieren und bereinigen. Stellen Sie sicher, dass Sie Textstrings normalisieren (führende/nachlaufende Leerzeichen entfernen, mehrere Leerzeichen durch ein einzelnes Leerzeichen ersetzen). Excel-Text ist (nur für BMP) Unicode; verarbeiten Sie ihn nicht in ASCII oder "ANSI", sondern in Unicode und kodieren Sie ihn in UTF-8, um ihn in Ihre Datenbank zu übertragen.

Answer 2

Schnelle und schmutzige Abhilfe mit phpmyadmin:

• Erstellen Sie eine Tabelle mit der richtigen Anzahl von Spalten. Achten Sie darauf, dass die Daten in die Spalten passen.
• Importieren Sie die CSV-Datei in die Tabelle.
• Verwenden Sie die Tabellenstruktur vorschlagen .

Answer 3

Soweit ich weiß, gibt es kein Tool, das diesen Prozess automatisieren kann (ich würde mich freuen, wenn mir jemand das Gegenteil beweisen könnte, da ich genau dieses Problem schon einmal hatte). Als ich dies tat, hatte ich zwei Möglichkeiten:
(1) Erstellen Sie die Spalten in der Datenbank manuell mit den entsprechenden Typen und importieren Sie sie dann, oder
(2) Schreiben eines Filters, der "herausfindet", welche Datentypen die Spalten haben sollten. Ich habe mich für die erste Option entschieden, weil ich nicht dachte, dass ich ein Programm schreiben könnte, das die Typinferenz durchführt.
Wenn Sie sich dazu entschließen, ein Werkzeug zur Typinferenz bzw. eine Konvertierung zu schreiben, gibt es einige Probleme, mit denen Sie zu tun haben könnten:
(1) In Excel werden Datumsangaben als die Anzahl der Tage seit dem 31. Dezember 1899 gespeichert; wie kann man daraus schließen, dass es sich bei einer Spalte um Datumsangaben handelt und nicht um numerische Daten (z. B. Bevölkerungszahl)?
(2) Machen Sie bei Textfeldern einfach die Spalten vom Typ varchar(n), wobei n der längste Eintrag in dieser Spalte ist, oder machen Sie daraus ein unbeschränktes char-Feld, wenn einer der Einträge länger als eine Obergrenze ist? Wenn ja, was ist eine gute Obergrenze?
(3) Wie konvertiert man automatisch eine Fließkommazahl in eine Dezimalzahl mit der richtigen Genauigkeit und ohne Verlust von Stellen?
Das heißt natürlich nicht, dass Sie es nicht können (ich bin ein ziemlich schlechter Programmierer). Ich hoffe, Sie schaffen es, denn es wäre ein wirklich nützliches Werkzeug.

Answer 4

Zu meiner Information habe ich unten dokumentiert, was ich getan habe:

1. XLRD ist praktisch, aber ich habe gerade die Excel-Daten als CSV gespeichert, so dass ich die LOAD DATA INFILE
2. Ich habe die Kopfzeile kopiert und begonnen, das Import- und Normalisierungsskript zu schreiben
3. Skript tut: CREATE TABLE mit allen Spalten als TEXT, außer für den Primärschlüssel
4. mysql abfragen: LOAD DATA LOCAL INFILE Laden aller CSV-Daten in TEXT-Felder.
5. basierend auf der Ausgabe von PROCEDURE ANALYSE war ich in der Lage ALTER TABLE um den Spalten die richtigen Typen und Längen zu geben. PROCEDURE ANALYSE 収益 ENUM für jede Spalte mit wenigen eindeutigen Werten, was nicht das ist, was ich brauchte, aber ich fand das später für die Normalisierung nützlich. 200 Spalten zu erkennen war ein Kinderspiel mit PROCEDURE ANALYSE . Die Ausgabe von PhpMyAdmin schlägt vor, dass die Tabellenstruktur Schrott ist.
6. Ich schrieb einige Normalisierung vor allem mit SELECT DISTINCT auf Spalten und INSERT Ergebnisse in separate Tabellen zu übertragen. Ich habe der alten Tabelle zunächst eine Spalte für FK hinzugefügt. Gleich nach der INSERT habe ich seine ID und UPDATE in der Spalte FK. Wenn die Schleife beendet ist, habe ich die alte Spalte gelöscht und nur die FK-Spalte übrig gelassen. Ähnliches gilt für mehrere abhängige Spalten. Es war viel schneller als ich erwartet hatte.
7. Ich habe (django) python manage.py inspctdb kopiert, die Ausgabe in die Datei models.py kopiert und alle diese Dateien hinzugefügt ForeignkeyField s, da FKs auf MyISAM nicht existieren. Schrieb ein wenig Python views.py, urls.py, einige Vorlagen...TADA

Answer 5

Pandas kann ein Schema zurückgeben:

pandas.read_csv('data.csv').dtypes

Referenzen:

• pandas.read_csv
• pandas.DataFrame