Live-Forum - Die aktuellen Beiträge
Datum
Titel
26.04.2024 08:08:24
Pivot Vormonatsveränderung
26.04.2024 07:54:11
nur den ersten Eintrag in spalte zählen
25.04.2024 23:47:55
Datum ausweisen, wenn definierte Summe erreicht ist
25.04.2024 21:40:53
Excel Datenquelle aus dem Web mit Anmeldung?
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1868to1872
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1868to1872
1868to1872
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

CSVs 1. transformieren 2. kombinieren

CSVs 1. transformieren 2. kombinieren
22.02.2022 00:40:51
Oliver
Hallo Helferlein
Ich möchte eine Datenabfrage mit dem Power Query Editor meiner Messdaten der Photovoltaik-Anlage durchführen, die im CSV-Format vorliegen. Ich wähle dazu Daten - Daten abrufen - Aus Datei - Aus Ordner und setze die erforderlichen Filter, bis die richtigen Dateien angezeigt werden.
Zur Zeit habe ich 3 Dateien der letzten Monate, die alle gleich aufgebaut sind, aber noch transformiert werden müssen, BEVOR die Daten zusammengesetzt werden können. So müssen z. B. aus jeder Datei die ersten 8 Zeilen und die letzten 4 Spalten gelöscht werden. Jetzt kann ich immer nur die erste Datei transformieren, oder beim Kombinieren/Aneinanderhängen der Dateien werden die überflüssigen Zeilen nicht gelöscht und der Header wiederholt sich immer - Excel erkennt also nicht die Wiederholung der ersten 9 Zeilen.
Später soll aber durch eine einzige Abfrage alle CSV-Dateien des Ordners auf einmal importiert werden, die monatlich anwachsen.
Vielen Dank für eure Mühe
Gruß Oliver

24
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: CSVs 1. transformieren 2. kombinieren
22.02.2022 07:17:16
Luschi
Hallo Oliver,
was erwartetest Du von den Helfern?
- theoretische Lösungsbeschreibungen?
- Erstellen eigener Demo-CSV-Dateien zum Lösen Deiner Probleme?
Hier solltest Du selbst aktiv werden und mindestens 2 anonymisierte CSV-Dateien mit wenigen aussagekräftigen Datensätzen per Upload in diesem Forum bereitstellen.
Dann werden sich auch die Helfer auf die Problemlösung stürzen.
Gruß von Luschi
aus klein-Paris
AW: CSVs 1. transformieren 2. kombinieren
22.02.2022 17:44:33
Oliver
Hallo Luschi aus klein-Paris!
Sorry, sollte keine Bequemlichkeit sein. Dachte nur, dass es sich mit einem Satz und ohne Beispiel leicht beantworten lässt, mein Fehler!
Ich möchte die beigefügten, monatlichen CSV-Energiedateien zweier Wechselrichter einer PV-Anlage über Power Query ohne SVERWEIS konsolidieren, die sich alle in einem Ordner befinden. Über Filter in der Ordnerliste kann ich die Dateinamen separieren (SN1 bzw. SN2) und habe dann dabei 2 Probleme:
1. Jede CSV-Datei muss erst transformiert werden (ersten 7 Zeilen und alle Spalten außer 1 & 2 löschen, 8. Zeile als Kopfzeile nehmen) und dann erst mit den gleichartigen Dateien zusammengefügt werden. Wenn ich alle Dateien gleich kombiniere, ohne zu transformieren, erkennt Power Query die sich wiederholenden ersten 8 Zeilen (Header) nicht als redundant und fügt sie jedes Mal mit in die Tabelle ein. Wenn ich transformiere, wird nur die 1. Datei genommen.
2. Ein Highlight wäre es, wenn ich mit der gleichen Abfrage die Daten des 2. Wechselrichters (SN2) über das Datum (1. Spalte) matchen könnte, so dass die tagesgleichen Energiewerte in einer Zeile nebeneinanderstünden.
Ich hoffe, damit mein Problem verständlich dargestellt zu haben. Die CSV Dateien habe ich in TXT umbenannt, um sie hochladen zu können.
https://www.herber.de/bbs/user/151294.txt
https://www.herber.de/bbs/user/151295.txt
https://www.herber.de/bbs/user/151296.txt
https://www.herber.de/bbs/user/151297.txt
Vielen Dank und Gruß von Oliver
Anzeige
AW: CSVs 1. transformieren 2. kombinieren
22.02.2022 19:16:03
Luschi
Hallo Oliver,
hier mal meine Lösungsvariante, die ausführlich getestet wurde - den entscheidenden Schritt habe ich im M-Code gekennzeichnet. Dieser ist aber nicht per PQ-Editor-Menü erreichbar, sondern habe ich im I-Net gefunden und entsprechend angepaßt:
https://www.herber.de/bbs/user/151300.xlsx
Gruß von Luschi
aus klein-Paris
AW: CSVs 1. transformieren 2. kombinieren
23.02.2022 20:43:15
Oliver
Hallo Luschi,
zunächst einmal vielen Dank für deine äußerst schnelle Problemlösung.
In der Exceldatei musste ich ein paar Änderungen vornehmen: Unter Hilfsprogrammabfragen - Beispieldatei habe ich = Folder.Files("D:\Downloads\Test_Original") manuell an meinen Ordner angepasst, dann wurden die CSV-Dateien auch gefunden.
In der Abfrage CSV erscheint unter Quelle (= Folder.Files(tab_CSV_Pfad)) eine Fehlermeldung 'Expression.Error: Ein Wert vom Typ "Table" kann nicht in den Typ "Text" konvertiert werden', obwohl ich im Sheet 'Konfiguration' meinen Dateiordner ohne Backslash richtig eingegeben habe. Der Parameter(1) tab_CSV_Pfad wird auch korrekt gesetzt. Erst wenn ich wieder manuell = Folder.Files("D:\Downloads\Test") eingebe, verschwindet der Error.
Habe dann noch die Transformation angepasst: nur 1. bis 3. Spalte behalten, 2. Spalte als Datum erkennen und danach sortieren.
Das Ergebnis sieht dann ganz passabel aus, also alle redundanten Zeilen sind gelöscht.
Nun bleibt nur noch das Problem, dass die Daten des 2. Wechselrichters mit gleichem Datum in die gleiche Spalte einsortiert wird und nicht in eine parallele Spalte, also 2 Energiewerte pro Datum.
Falls das zu kompliziert wird, muss ich halt 2 Ordner und 2 Abfragen anlegen und diese mit SVERWEIS verknüpfen.
Modifizierte Datei: https://www.herber.de/bbs/user/151332.xlsx
Gruß Oliver
Anzeige
AW: CSVs 1. transformieren 2. kombinieren
24.02.2022 16:15:36
Luschi
Hallo Oliver,
das Darstellen der Werte beider Wechselrichter für den gleichen Tag in 1 Zeile ist per Power Query natürlich möglich. Hier mal mein Beispiel dazu: https://www.herber.de/bbs/user/151359.xlsx
Noch ein paar Hinweise:
- der Pfad muß jetzt nur noch in Konfiguration!B3 (Name: tab_CSV_Pfad) angepaßt werden
- im M-Code von PQ wird immer auf diesen Pfad verwiesen, nur so wird's dynamisch
   so in den Abfragen 'Beispieldatei' und 'CSV'
- in diesem Pfad sollten sich nur die entsprechenden CSV-Dateien befinden und nix Anderes
Gruß von Luschi
aus klein-Paris
Anzeige
AW: CSVs 1. transformieren 2. kombinieren
24.02.2022 19:48:39
Oliver
Hallo Luschi,
das hat leider nicht geklappt:
Habe alle CSV alleine in einen Unterordner verschoben und den Verweis tab_CSV_Pfad korrekt gesetzt in Konfiguration!B3 (bei mir D:\Downloads\Test_Original\CSV)
Beim Aktualisieren erscheint: 

Abfrage 'CSV' (Schritt 'AutoRemoveColumns1)') verweist auf andere Abfragen oder Schritte und kann daher nicht direkt auf eine Datenqulle zugreifen. Erstellen Sie diese Datenkombination neu.
Beim Öffnen der Abfragen sehe ich Ausrufezeichen unter Beispieldatei, Zeile Quelle: 

Formula.Firewall: Abfrage 'Beispieldatei' (Schritt 'Navigation1') verweist auf andere Abfragen oder Schritte und kann daher nicht direkt auf eine Datenquelle zugreifen. Erstellen Sie diese Datenkombination neu.
xPfad ist korrekt übernommen und richtig.
Gleiche Fehlermeldung in Beispieldatei transformieren unter Quelle: 

= Csv.Document(Parameter1,[Delimiter=";", Columns=6, Encoding=65001, QuoteStyle=QuoteStyle.None])
Und nochmals unter Andere Abfragen: CSV: Quelle, x-Zeilen weg, Added CustomIndex und Added Custom.
Habe gesehen, dass du jetzt für Quelle jeweils 

= Folder.Files(xPfad)
benutzt, statt vorher 


tab_CSV_Pfad
Sorry für die Probleme!
Anzeige
AW: CSVs 1. transformieren 2. kombinieren
24.02.2022 20:43:49
Luschi
^Hallo Oliver,
in meinem M-Code der geschickte Demodatei '151359.xlsx' gibt es keinen Abfrageschritt 'AutoRemoveColumns1'
Du mußt schon meine Datei nehmen mit dem funktionierenden M-Code und dahinein den Pfad anpassen.
Ansonsten kann ich mir die Fehlermeldung auch nicht erklären.
Gruß von Luschi
aus klein-Paris
PS: ansonsten mußt du die CSV-Dateien abspecken und gemeinsam mit der xlsx-Datei zippen und hier und diese zip-Datei bereitstellen.
AW: CSVs 1. transformieren 2. kombinieren
25.02.2022 00:20:25
Oliver
Hallo Luschi,
selbstverständlich habe ich DEINE Datei genommen und den Pfad korrekt angepasst.
Alle Fehlermeldungen im PQ beginnen mit 

Formula.Firewall: ... verweist auf eine andere Abfrage oder Schritte ...
Habe gelesen, dass das evtl. mit irgendwelchen Sicherheitseinstellungen zu tun habe könnte oder auch Inkompatibilitäten zwischen Excelversionen (ich nutze Excel für Microsoft 365). Mit solchen Firewallblockaden sollen Datenlecks verhindert werden. Ich weiß aber nicht, wie ich das abstellen kann.
Habe die Exceldatei und CSV-Dateien als ZIP angehängt.
https://www.herber.de/bbs/user/151371.zip
Anzeige
AW: CSVs 1. transformieren 2. kombinieren
25.02.2022 11:05:07
Luschi
Hallo Oliver,
mir fiel es wie die Schuppen aus den Haaren, als ich unser Problem mal auf einem 2. Rechner ausprobierte. M$ hat mehr Angst als Verstand vor seinen eigenen Entwicklungen; viele Datenverbindungen (Excel-Access, M$-Office-DB-Server usw.) sind statisch aufgebaut und werden als Text hinterlegt. Sobald man versucht, diese Verbindungen dynamisch zu halten durch Variablen, schreiten M$-Warnmeldungen und Stopps ein.
In PQ kann man diese Variabilität erzwingen, wenn man Folgendes im PQ-Editor tut:
- Datei - Optionen und Einstellungen - Abfrageoptionen - Datenschutz
- markierte Einstellung wählen
Leider ist das aber keine arbeitsmappen-bezogene Einstellung, die man mit der Datei mitliefert, sondernn eine PC-bezogene Einstellung. Deshalb klappte das bei mir und Du hattest die Probleme - sorry.
Userbild
Gruß von Luschi
aus klein-Paris
Anzeige
AW: kleine Korrektur
25.02.2022 11:24:40
Luschi
Hallo Oliver,
wenn man die folgende Einstellung macht, dann ist ist die Sicherheit nur für diese AM festgelegt.
Userbild
Gruß von Luschi
aus klein-Paris
AW: kleine Korrektur
25.02.2022 16:25:10
Oliver
Genau solch eine Eiinstellung hatte ich bisher nur in den Excel-Optionen gesucht und nicht gefunden.
Vielen herzlichen Dank, werde ich nachher zuhause ausprobieren!
Gruß Olli
AW: kleine Korrektur
25.02.2022 18:40:35
Oliver
So, habe Datenschutz für die AM deaktiviert und alle Fehlermeldungen sind verschwunden. Schon einmal ein Teilerfolg!
Leider liefert die Abfrage aber nicht das gewünschte Ergebnis. Dieses habe ich manuell mal zusammenkopiert und als Datei beigefügt.
https://www.herber.de/bbs/user/151400.xlsx
In der Abfrage CSV, Zeile 18 - Erweiterte CustomAdd1 - führt die Aufteilung in Spalte CustomAdd.VorWert zu einem Durcheinander der beiden Wechselrichterdaten, weil die Sortierung der Datumspalte mal abwechselnd sortiert, aber auch mal 2 Tage eines WR hintereinander platziert. Beide Spalten enthalten also jeweils Werte von beiden WR und sind nicht korrekt auseinander sortiert wie in meinem Beispiel. Es handelt sich ja quasi um Zählerstände, die jeweils allmählich ansteigen.
Außerdem sollen alle Daten vom 18.12. bis 19.2. aufgelistet werden und nicht nur Tage mit 2 Werten. Die beiden CSV-Datenreihen der WR unterscheiden sich sowohl im Dateinamen ("SUNNY_TRIPOWER_5.0_xxx.csv" und "SUNNY_TRIPOWER_6.0_xxx.csv") als auch in Zeile 3-5 im Header, der allerdings zuvor gelöscht wurde.
Ich hoffe, dass das Ganze nicht zu kompliziert wird! Sonst kopiere ich mir die Daten wöchentlich manuell zusammen...
Gruß Olli
Anzeige
AW: kleine Korrektur
27.02.2022 15:20:39
Luschi
Hallo Oliver,
bin erst heute dazu gekommen, mich um das Problem noch mal zu kümmern. Wenn es in den csv-Dateien zu jedem Termin nur 1 oder 2 Einträge gibt,
läuft es bei mir (d.h. je Wechselrichter nur max. 1 Eintrag pro Tag).
Sollte es für den einen WR einen Eintrag am Tag geben, beim 2. WR für den gleichen Tag aber nicht, dann ist das auch OK - habe den Zeilen-Filter entsprechend angepaßt.
Was passieren soll, wenn es mehrere Einträge am Tag für einen oder beiden WR's gibt, muß noch geklärt werden:
- Werte addieren geht ja wohl nicht?!?
https://www.herber.de/bbs/user/151433.xlsx
Gruß von Luschi
aus klein-Paris
Anzeige
AW: kleine Korrektur
27.02.2022 19:06:48
Oliver
Nabend Luschi,
vielen Dank für deine Wochenendarbeit trotz Sonnenschein!
In der Tat liefert ein Wechselrichter nur einen Wert pro Tag, davon habe ich bekanntermaßen 2 (SO- und SW-Ausrichtung), die zu verschiedenen Zeiten (Dez und Feb) in Betrieb gegangen sind. Die Werte pro WR möchte ich gerne separat halten, also nicht automatisch schon in der Abfrage addieren, damit ich später die Energieerträge den WR zuordnen kann, um zu entscheiden, welche Himmelsrichtung ggfs. noch lukrativ aufzurüsten wäre.
Das aktuelle Abfrageergebnis ist zwar jetzt sortierter, aber der 1. WR füllt zunächst die B-Spalte, und wenn Daten des 2. WR dazu kommen weiter die C-Spalte, während der 2. WR mit null beginnend die B-Spalte ausfüllt. Damit kann ich nicht spaltenweise über die Gesamtzeit pro WR addieren. Geht das noch zu optimieren? Dabei ist es egal, welche Spalte vom WR befüllt wird, aber dies sollte durchgängig vom Anfang bis Ende so bleiben.
Vielen Dank,
Gruß Olli
Anzeige
AW: kleine Korrektur
27.02.2022 21:51:16
Luschi
Hallo Oliver,
im Moment kann ich Dir da nicht ganz folgen, wo das Problem liegen sollte - bei mir ist mit den bereitgestellten Demodateien alles paletti.
Mach mal einen Vorschlag, wie wir eine AnyDesk-Sitzung vereinbaren können. Ich habe auch ein Konto bei https://www.ms-office-forum.net/forum/index.php
Da kannst Du mir eine PN unter dem selben Kontonamen wie hier schicken.
Gruß von Luschi
aus klein-Paris
AW: kleine Korrektur
01.03.2022 19:32:36
Oliver
Hi Luschi,
auch ich benutze die gleichen Demodateien vom 22.2. Deine Abfrage führt bei mir zu folgendem Ergebnis (copy-paste):
...
28.01.2022 180442
29.01.2022 197448
30.01.2022 198188
01.02.2022 209512
02.02.2022 211277
03.02.2022 0 228525
04.02.2022 771 229801
05.02.2022 2621 232706
06.02.2022 8593 245684
07.02.2022 9493 247045
08.02.2022 18140 261965
09.02.2022 19145 263528
...
Also am 3.2. springen die Messwerte des 1. WR von Spalte B in Spalte C und die neuen Messwerte des 2. WR (beginnend mit 0) werden in Spalte B fortgeführt.
Schöner wäre es, wenn jeder Wechselrichter nur eine einzige, eigene Spalte nutzen würde, z. B. also 1. WR Spalte B und 2. WR Spalte C.
Lieben Gruß
AW: kleine Korrektur
03.03.2022 06:28:55
Luschi
Hallo Oliver,
Deine Aussage, daß sich die die CSV-Dateien in den Zeilen 3 - 5 bei bedien Wechselrichtern unterscheiden, kann ich nicht bestätigen, Da gibt es nur 'SUNNY TRIPOWER 6.0' und nicht auch 'SUNNY TRIPOWER 5.0'.
Aber egal, wenn die CSV-Datei-Namen nach dem Muster benannt sind:
1. WR: SUNNY_TRIPOWER_5.0_001.csv, SUNNY_TRIPOWER_5.0_002.csv usw.
2. WR SUNNY_TRIPOWER_6.0_001.csv, SUNNY_TRIPOWER_6.0_002.csv usw.
dann ist das OK für die Unterscheidung. Was Anderes wäre es, wenn es pro Tag mehr als 1 Meßwert pro WR gäbe. Dieser Fall ist bisher nicht betrachtet worden.
Hier mein Ergebnis: https://www.herber.de/bbs/user/151521.xlsx
Gruß von Luschi
aus klein-Paris
AW: kleine Korrektur
04.03.2022 12:01:05
Oliver
Hallo Luschi,
das Abfrageergebnis ist leider unverändert so, dass die späteren Werte des 2. Wechselrichters in der Spalte des 1. Wechselrichters erscheinen (nur diesmal in Spalte C statt B) und die Datenreihe des 1. Wechselrichters in einer neuen Spalte fortgeführt werden (diesmal Spalte B).
Guckst du:
...
30.01.2022 198188
01.02.2022 209512
02.02.2022 211277
03.02.2022 228525 0
04.02.2022 229801 771
05.02.2022 232706 2621
...
In meinem Beitrag vom 22.02.2022 17:44:33 hatte ich 4 Datendateien als Beispiel angehängt. Die 4. davon
https://www.herber.de/bbs/user/151297.txt
hat in der 3. Zeile den Namen des 2. Wechselrichters (SMA 5.0), die ersten 3 Datendateien 151294.txt bis 151296.txt sind vom 1. Wechselrichter (SMA 6.0). Vermutlich hast du diese letzte Datei ausgelassen.
In meinem Beitrag vom 27.02.2022 19:06:48 hatte ich ja bereits geschrieben, dass jeder WR nur genau einen Tageswert (Summenwert der erzeugten Stromenergie) in der 2. Spalte liefert. Ab der 3. Spalte kann alles gelöscht werden. Es gibt keinen 2. Wert bei einem Wechselrichter, nur Zeitstempel und einen Messwert dahinter.
Die Differenzierung über die 3. Zeile (SMA 5.0 und SMA 6.0) wäre etwas eleganter, da diese immer gleich sind im Gegensatz zum Dateinamen, der im Namen immer das Erstelldatum hinten dranhängt. Alle Dateinamen beginnen allerdings mit
SUNNY_TRIPOWER_5.0 bzw. SUNNY_TRIPOWER_6.0
Vielen Dank
Oliver
AW: kleine Korrektur
06.03.2022 08:40:01
Luschi
Hallo Oliver,
ich habe nochmals getestet und meinen PQ-Code für gut befunden. Um das zu beweísen, habe ich bei allen 4 CSV-Dateien die kWh so verändert, daß keine Zahl doppelt vorkommt und man sofort erkennen kann, wenn da etwas nicht richtig eingelesen wird. - aber alles in bester Ordnung. Und es gibt Tage, die nur in einem der beiden WR vorkommen.
Ich schicke Dir die Excel- und die 4 manipulierten CSV-Dateien im ZIP-Format nochmals zu.
https://www.herber.de/bbs/user/151581.zip
Gruß von Luschi
aus klein-Paris
AW: kleine Korrektur
06.03.2022 14:14:48
Oliver
Hi Luschi,
hervorragend, jetzt sieht das Abfrageergebnis mit den Beispieldateien richtig aus! Super Arbeit!
Allerdings funktioniert die Abfrage nicht mit den Originaldateien. Die Abfrage bleibt leer. Ich habe die CSV-Dateien noch einmal gezippt beigefügt, so wie sie geliefert werden.
Leider verstehe ich die Nomenklatur des M-Code kaum, so dass ich mich nicht traue, deinen zu modifizieren oder anzupassen, aber wir sind auf der Zielgeraden!!!
https://www.herber.de/bbs/user/151588.zip
LG Olli
AW: kleine Korrektur
09.03.2022 11:13:37
Luschi
Hallo Oliver,
habe Deinen Hilferuf erhalten und werde in den nächsten 2 Tagen Zeit haben, mich damit zu beschäftigen.
Gruß von Luschi
aus klein-Paris
AW: kleine Korrektur
09.03.2022 20:48:02
Luschi
Hallo Oliver,
es ist wie immer, kleine Ursache - große (Miß-)Wirkung; die alten Test-CSV-Dateien waren spaltenweise per Semikolon (;) getrennt, die neue CSV-Lieferung aber mit Komma getrennt.
Aus diesem Grunde war die Ergebnisliste natürlich leer, denn PQ hatte sich gemerkt:
Delimiter=";" und nicht automatisch auf Delimiter="," umgestellt.
https://www.herber.de/bbs/user/151667.zip
Gruß von Luschi
aus klein-Paris
AW: kleine Korrektur
09.03.2022 22:02:30
Oliver
Vielen herzlichen Dank für deine ganze Arbeit und entschuldige nochmal die vielen Korrekturen!
So funktioniert's genau richtig! Wäre ich nie im Leben drauf gekommen. Dieser M-Code ist schon eine eigene Welt. Weißt du, warum immer eine Beispieldatei in den Abfragen (Aufteilung in Hilfsprogrammabfragen und Andere Abfragen) dabei ist und nicht einfach ein durchgehender Code?
nochmals DANKE!
Gruß Olli
AW: CSVs 1. transformieren 2. kombinieren
24.02.2022 20:03:19
Oliver
Und unter Andere Abfragen: CSV steht 

Der Download wurde nicht abgeschlossen.
Das verstehe ich nun auch nicht.

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige