Anfängerfragen: csv Dateien auslesen und in SQL abspeichern

[wrsm.code]

Hallo an das super Python-Board hier,

Ich beginne mit Python quasi seit ein paar Tagen, gewisse "einfache" Grundlagen bekomme ich hin, jedoch hab ich "arbeitsbedingt" ein paar Herausforderungen zu meistern:

jeden Tag bekomme ich von meinem EVU Lastgangdaten in Form von 4 csv Dateien. Darin enthalten sind Zählerstände mit einer 15minütigen Intervall-Ablesung für genau einen Tag.
Das Jahr 2013 ist nun um, und ich möchte diese in eine SQL db eintragen, um Auswertungen und sonstige "Controlling"-Aufgaben damit erledigen.

Folgende Gegebenheiten:
Dateinamen:
Firma_20120502_V01.csv
Firma_20120502_V02.csv
Firma_20120502_V03.csv
Firma_20120502_V04.csv

Inhalt einer Datei:

Code: Alles auswählen

Kundenname:;Stadtverwaltung Realschule;;Stadtverwaltung Realschule;;Stadtverwaltung Realschule;;Stadtverwaltung Realschule;;Stadtverwaltung Realschule;;Stadtverwaltung Realschule;;Stadtverwaltung Realschule;;Stadtverwaltung Realschule
Kanal:;Zählernummer aus 33 Zeichen#1-1:1.29.0;;Zählernummer aus 33 Zeichen#1-1:1.29.0;;Zählernummer aus 33 Zeichen#1-1:2.29.0;;Zählernummer aus 33 Zeichen#1-1:2.29.0;;Zählernummer aus 33 Zeichen#1-1:3.29.0;;Zählernummer aus 33 Zeichen#1-1:3.29.0;;Zählernummer aus 33 Zeichen#1-1:4.29.0;;Zählernummer aus 33 Zeichen#1-1:4.29.0
Datum Uhrzeit;Wert[kWh];Status;Wert[kWh];Status;Wert[kWh];Status;Wert[kWh];Status;Wert[kvarh];Status;Wert[kvarh];Status;Wert[kvarh];Status;Wert[kvarh];Status
02.05.12 00:15;4,59375;;4,59375;;0;;0;;2,68;;2,68;;0;;0;
02.05.12 00:30;4,4575;;4,4575;;0;;0;;2,56125;;2,56125;;0;;0;
02.05.12 00:45;5,38125;;5,38125;;0;;0;;2,0125;;2,0125;;0;;0;
02.05.12 01:00;4,28;;4,28;;0;;0;;2,28125;;2,28125;;0;;0;
02.05.12 01:15;5,57875;;5,57875;;0;;0;;1,7925;;1,7925;;0;;0;
02.05.12 01:30;5,55375;;5,55375;;0;;0;;1,69375;;1,69375;;0;;0;
02.05.12 01:45;5,27875;;5,27875;;0;;0;;1,52125;;1,52125;;0;;0;
02.05.12 02:00;4,935;;4,935;;0;;0;;1,64625;;1,64625;;0;;0;
02.05.12 02:15;5,13625;;5,13625;;0;;0;;1,49625;;1,49625;;0;;0;

Ebenfalls ist mir aufgefallen, dass innerhalb eines Jahres die Kopfzeilen mit der entsprechenden Kanalnummer und den zugehörigen Zählerwerten sich 3-4 mal geändert haben.
Zählerwechsel, Neuer Eintrag für eine Photovoltaik-Anlage, etc.

Dies erschwert natürlich ein einfaches auslesen und eintragen in die db aus meiner Sicht. Insgesamt für ein Jahr habe ich nunmehr 1287 Dateien erhalten. Diese manuell in eine Datenbank einzutragen ist natürlich unendlich langwierig und kann auch kaum ausgeführt werden.

Da die Eintragung automatisiert werden soll, habe ich mir folgende Überlegungen gemacht:

- die csv Dateien werden bereits automatisch in einen bestimmten Ordner abgespeichert. Dateinamen wie oben beschrieben.

Jetzt die Python-Aufgabe:
- einlesen aller Dateinamen und prüfen, ob diese bereits in abgearbeitet.csv enthalten sind.
- wenn nicht, alle Dateien in db schreiben ohne die Kopfzeilen (SQL db wurde bereits erstellt)
- Kopfzeilen auslesen (Kundenname und Zählernummer vergl. und passende Einträge in die db schreiben)
- geschriebene Dateinamen in die Liste abgearbeitet.csv aufnehmen.

- Script soll "händisch" gestartet werden

--> Nun meine Frage an die Profis:

- ist die Python Aufgabenstellung so in Ordnung, oder würdet ihr mir eine andere Abarbeitung empfehlen?
- wie soll ich die Daten einlesen? - per csvreader oder mit anderen Mitteln? - wie beschrieben, handelt es sich "einmalig" um etwa 1287 Dateien. Danach wird das Script eher "sporadisch" ausgeführt.

Daten kommen per Mail -> 2-3x im Monat wird per VBA der Anhang gespeichert - dann könnte man das Script ausführen. Vorausgesetzt, man ist in der Fa. und mit dem SQL Server verbunden.
Daten werden alle "ZENTRAL" gespeichert.

Ich wäre daher froh, wenn mir jemand einen Lösungsweg aufzeigen kann, wie man an die Sache herangehen sollte.
Komplette Lösungen will ich nicht erhalten, sondern ich will auf diesem Wege Python erlernen. Learning by Doing ist die beste Methode.

Vielen Dank,
J.

[Sirius3]

Das Vorgehen, das Du da beschreibst, hört sich doch schonmal gut an. Zum Lesen von csv-Dateien nimmt man natürlich das csv-Modul. Nur warum willst Du erst die Daten in die DB schreiben und dann die Kopfzeilen lesen? Ich denke doch, Du brauchst die Kopfzeile umd zu wissen, um welchen Kunden es sich handelt, bevor Du irgendetwas in eine Datenbank schreiben kannst. Es sieht für mich auch so aus, als ob es sehr einfach wäre, zu prüfen, ob die Datei schon in die Datenbank geschrieben wurde, so dass eine extra "abgearbeitet"-Datei unnötig scheint.

[wrsm.code]

Du hast natürlich Recht!
Ich habe mich da wohl falsch ausgedrückt. Erst muss ich natürlich den Header mit den Zählernummern in der db vergleichen und dann die Daten dort eintragen.

Es sieht für mich auch so aus, als ob es sehr einfach wäre, zu prüfen, ob die Datei schon in die Datenbank geschrieben wurde, so dass eine extra "abgearbeitet"-Datei unnötig scheint.

wie kann ich dies erreichen?

Meine Idee ist eben: Die Dateien sollen nicht alle jedesmal geöffnet werden, und geprüft werden, dafür hatte ich die extra abgearbeitet Datei vorgesehen.
vor dem Öffnen prüfen, ob schon verarbeitet wurde, danach nur die Dateien öffnen und abarbeiten, die wirklich noch anstehen. Ich denke mir, so ist es effektiver, als jedesmal mit allen Dateien zu starten, zumal, es immer mehr werden.

mfg
J.

EDIT://

Noch eine Frage:

Ist es sinnvoller mit Python 3 zu starten, oder besser auf Version 2.7 bleiben? - Jedenfalls hab ich Version 2.7 installiert, in Verbindung mit eclipse als GUI.

[pillmuncher]

Ich verstehe das Datenformat nicht. Stehen die Zeilen 1. bis 3. wirklich so in der Datei? Nichts für ungut, aber wer denkt sich denn sowas aus? Normalerweise lässt man bei CSV-Dateien die Feldnamen entweder komplett weg (weil man im Rahmen der selbstgeschriebenen Anwendung weiß, welche Spalte welches Datum enthält), oder man schreibt genau eine Zeile mit aussagekräftigen Spaltennamen an den Anfang der Datei, wobei diese Namen eindeutig sein sollten (damit man z.B. csv.DictReader bequem benutzen kann).

Natürlich kannst du trotzdem csv.reader() benutzen. So, wie die Daten jetzt vorliegen, ist es halt etwas fummelig.

Du schreibst, dass "Kopfzeilen mit der entsprechenden Kanalnummer und den zugehörigen Zählerwerten sich 3-4 mal geändert haben". Heißt das, dass von einer zur nächsten (oder gar innerhalb einer einzigen ) Datei dieselben Spalten (also mit derselben Anzahl ; davor) verschiedene Daten enthalten, die in der DB in verschiedene Spalten/Tabellen geschrieben werden sollen? Wie entscheidet man, was wohin kommt? Insbesondere da ja die Spaltennamen nicht eindeutig sind. Oder tauchen irgendwo in der Mitte der Datei neue Spaltennamen auf? Ja, sowas habe ich schon gesehen.

Ansonsten erscheint mir der von dir beschrieben Ablauf nicht unvernünftig.

Allerdings habe ich folgende Anekdote beizusteuern. Vor vielen Jahren habe ich mal von einem Abteilungsleiter der Firma, bei der ich zuvor mal gearbeitet hatte, einen Riesen-Anschiss kassiert. Die Firma war eine größere Hardware-Entwicklungsfirma, die Fernseh- und Videobearbeitungs-Steckkarten und dergleichen für PCs baute. Genauer gesagt, das Bauen übernahm eine Fertigungsfirma. Diese sollte dann irgendwann auch die Lieferung an unsere Kunden übernehmen, statt dass die fertigen Boards an uns geschickt wurden, nur, damit wir sie dann wieder weiterschicken würden. Der Fertiger bekam regelmäßig emails, was er wann zu fertigen und auszuliefern hatte. Die Boards bekamen Barcode-Sticker mit ID- und Seriennummern, die dort unmittelbar vor dem Versand eingepiepst und mit einem von mir geschriebenen VB-Programm in eine DB geschubst wurden. Jeden Freitag vor Betriebsschluss drückte dort jemand auf einen Button und dann wurde ein DB-Dump gemacht und per email an unsere Firma geschickt. Genauer gesagt, an einen bestimmten Mitarbeiter. Diese Person hatte ebenfalls einen VB-Button, der dann den Dump in unsere DB gesaugt hat.

Meine Einwände, dass das alles nicht besonders fehlertolerant ist, insbesondere, weil die genannte Person ja auch mal Urlaub machen würde, wurden abgebügelt. Man habe das ja im Griff, und man würde rechtzeitig eine Urlaubsvertretung einweisen. Aber die Vertretung habe ja keinen Zugriff auf die mailbox desjenigen, der das normalerweise macht? Na, der würde halt ein PostIt mit seinen Zugangsdaten auf den Monitor kleben. An dem Punkt habe ich dann kapituliert und einfach nach Anweisung gebaut, was gewünscht wurde.

Eineinhalb Jahre später habe ich dann einen sehr wütenden und sehr lauten Anruf bekommen, was mein Programm für ein Scheiß sei, und dass ich offensichtlich keine Ahnung vom Programmieren habe, etc. Ich habe zu dem Zeitpunkt schon bei einer anderen Firma gearbeitet, und mir fehlte u.a. auch deswegen die Einsicht, mich derart beschimpfen zu lassen. Meine sehr ruhige Reaktion war ein Angebot, dass ich das Programm gerne fixen würde, und mein Stundensatz betrage soundoviel DM. Das wurde akzeptiert.

Ich kam also. Und sah. Und siegte. Dachte ich. Die Person, die für das Knopfdrücken zum Einsaugen der Dumps zuständig war, war seit vier Wochen im Urlaub. Eine Vertretung gab es nicht, weil gerade Sommerpause war und wenig Betrieb.

Ich habe das dann gefixt (vier mal einen email-Angang speichern, die Zugangsdaten bekam ich vom Admin, und viermal auf einen Button drücken). Ich habe das dann dem Abteilungsleiter erklärt, der sich aber nicht beruhigen wollte. Mit hochrotem Kopf hat er mich angebrüllt. Ich habe ihm dann ganz nüchtern erklärt, dass ich alles das, was er mir gerade vorwarf, schon immer gesagt hatte, dass er damals aber nichts davon habe wissen wollen. Nun er war Manager und war weitaus beschlagener als ich, was derartige Diskussionen anging. Er besiegte mich mit einem unschlagbaren Manager-Zauberwort, dem Wort, mit dem ein erfahrener Manager jede Diskussion zu seinen Gunsten wenden kann. Er brüllte: Trotzdem!

[wrsm.code]

@ pillmuncher:

Ich gebe dir vollkommen Recht zudem, was du hier schreibst! - selbiges leidige Thema habe ich ebenfalls geführt. Sobald aber die Importfunktionalität sauber über die Bühne geht, soll dies alles auf dem zentralen Server "selbsttätig" erledigt werden. Bis dahin soll ich erstmal einen Weg dahin aufzeigen, das es geht!

Wenn ich mich einmal in ein Thema verbissen habe, bastel ich solang, bis es klappt. Von daher mache ich mir über den Erfolg keine Sorgen. Nur über die Dauer

Leider sind die Daten vom EVU (RWE) leider wirklich so grottenschlecht.
Hier mal ein Link zur (leicht veränderten Datei:) CSV File

Und hier mal ein Screenshot vom Datenvergleich CSV Datenvergleich Header

Die Zähler werden als OBIS Zähler OBIS-Kennzahlen ausgelesen. Diese Daten werden uns dann als csv Datei zur Verfügung gestellt. Es gibt noch weit schlimmere Formate, welche wir dankend abgelehnt haben.
Für eine db ist es eigentlich stilvoll, immer die selben Daten an der selben Stelle zu finden, bzw. mit dem selben Suchmuster aufzuspüren.

Das allein Datum und Uhrzeit in einer Spalte/Zeile stehen, gefällt mir schon nicht. Dies wollte ich theoretisch auch noch durchparsen, das ich dadurch 2 Spalten erhalte.

sollte ich evtl. noch mit in die ToDo-Liste packen.

mfg
J.

[Sirius3]

@pillmuncher: Dass sich der Header über drei Zeilen hinzieht ist für jemanden, der die Datei mit Excel lesen will wohl noch die eleganteste Lösung. Und in Python sind das drei zusätzliche Zeilen Code und das Thema ist erledigt.

@wrsm.code: Deine Abneigung gegen ein DatumZeit-Feld verstehe ich nicht. Was ist der Vorteil davon immer mit zwei Spalten herumhantieren zu müssen, wenn ich einen Zeitpunkt suchen will.
Nur aus Interesse: Wie sieht denn Dein Datenbankdesign aus?

[wrsm.code]

@Sirius:

Zur Datenbank sei noch gesagt:

Das gesamte "Konstrukt" - was MYSQL basierend ist - wird tagesweise geschrieben. Also eine db pro Tag, um die Datensätze nicht endlos wachsen zu lassen.
In der db befinden sich alle relevanten Daten, die die Gebäudeautomation hergibt. Wir sprechen hier über eine Gebäudeleittechnik, die alle Daten aller Liegenschaften in einer db zusammenfasst.
Dies ist die einzige Vorgabe, die ich habe. Die Daten werden mittels Datum und Uhrzeit voneinenander getrennt erfasst, also DATE und TIME separat. Vorgabe der Leittechnik.

Ob sich dadurch die Visualisierung von Verbräuchen, Energiemengen, Schaltzeiten, etc. besser darstellen lässt, entzieht sich meiner Kenntnis.
Eine db mit einem Kompletten Tagessatz entspricht z.Zt. ca. 5MB - was wirklich praktikabel ist, wenn es um Backups und generelle Datensicherung geht.

[BlackJack]

@wrsm.code: Also das Datum und Uhrzeit in *einer* Spalte stehen ist gut, denn das ist ja schliesslich auch *ein* Datum (im Sinne von Einzahl Daten). Das wirst Du in der Datenbank doch hoffentlich nicht getrennt haben‽ Ich fänd das zusammenbasteln aus zwei Spalten nerviger. Und parsen muss man es ja sowieso um ein `Timestamp`-Objekt für die DB-API oder ein `datetime.datetime`-Objekt für SQLAlchemy zu erstellen. Irgendwie muss man das ja in die Datenbank bekommen.

Eine DB pro Tag klingt auch komisch. Damit hat man eine variablen Anteil in Tabellennamen, wobei Tabellennamen eigentlich in jedem „normalen” DB-Entwurf eine statische Grösse sind. Datenbanken sind doch gerade dafür da mit *grossen* Datenmengen umgehen zu können. Mit 5 MiB grossen Datenpaketen kann man auch ohne DB noch gut klar kommen.

[wrsm.code]

Zum Thema Datenbank:
Dies ist wie schon beschrieben die einzige Vorgabe, die ich habe. Und die Datenbanken sind "momentan" noch so klein, da das Ganze ebenfalls noch im Aufbaustadium steht. Soll heißen, es sind noch nicht alle Liegenschaften daran gebunden.

Ich hab nur mal so quick and dirty angefangen, und hab versucht, per Python eine Datei zu kopieren, quasi zeilenweise auslesen und in neue Datei schreiben:

Code: Alles auswählen

import csv
import os

pfad = 'D:/--- == Arbeit == ---/Lastgangdaten/'   # testweise ohne funktion


with open ('test_20130810_V01.csv','r' ) as csvfile:
  readfile = csv.reader(csvfile, delimiter=';')
#  for row in readfile:
   # print ' '.join(row)
    #print(row)

with open ('test.csv','w' ) as csvfile:
  writeit = csv.writer(csvfile, delimiter=';')
  for row in readfile:
    writeit.writerow(row)

das klappt schonmal nicht, da gibt's ne Fehlermeldung.

Code: Alles auswählen

Traceback (most recent call last):
  File "X:\Eclipse\Projekte\csv2sql\csvreader.py", line 20, in <module>
    for row in readfile:
ValueError: I/O operation on closed file

andere Variante:

Code: Alles auswählen

import csv
import sys

f = csv.reader (open('test_20130810_V01.csv', 'r'), delimiter = ';')
w = csv.writer (open('test.csv','w'), delimiter =';')

try:
    reader = csv.reader(f)
    writer = csv.writer(w)
    for row in reader:
      print row    #Testausgabe
      writer.writerow(row)
finally:
  f.close()
  w.close()
print "Fertig"

klappt ebenfalls nicht.

Code: Alles auswählen

Traceback (most recent call last):
  File "X:\Eclipse\Projekte\csv2sql\test.py", line 20, in <module>
    f.close()
AttributeError: '_csv.reader' object has no attribute 'close'

Irgendwie verstehe ich das mit dem csv Modul nicht

[Sirius3]

@wrsm.code: wie sollen sinnvollerweise Statistiken erstellt werden, die über einen Tag hinaus gehen, wenn die Daten separat gespeichert werden? Wenn Speicher ein Problem für Dich ist, dann ist wohl eine Datenbank der falsche Container (vielleicht wäre HDF das richtige).

csv-Reader braucht, wenn Du aus einer Datei lesen willst, ein offenes Filehandle. Da in Deinem ersten Beispiel die Datei nach dem »with«-Block wieder geschlossen wird, kannst Du weiter unten nichts mehr daraus lesen. Da ein Reader-Objekt kein File-Objekt ist, gibt es auch kein »close«. Durch raten wirst Du auch nicht weit kommen. Zum Glück steht das alles in der Dokumention.