hurtikurti
Goto Top

Mehrere .csv Dateien automatisch transponieren und zusammenführen

Hallo Zusammen,

ich habe mehrere Strukturell gleiche .csv Dateien und möchte deren Inhalt gerne in einer Datei zusammenführen.

Ich habe es bereits mit PowerQuery versucht, mein Problem ist jedoch, dass die Überschriften in Spalte B und die Werte jeweils in Spalte C stehen. Excel wünscht sich jedoch, dass die Daten Transponiert vorliegen, daher Überschriften in einer Zeile und Werte in einer anderen Zeile.
Bei mir hat er die Werte der anderen Dateien nun jeweils unterhalb der alten eingefügt. Ich bräuchte Sie jedoch alle nebeneinander oder noch besser transponiert und dann untereinander.
Für eine Datei ist das Transponieren ja einfach, aber ich finde keine Weg das zu automatisieren.

Mir persönlich ist es fast egal, ob es Spalten oder Zeilenweise aufgelistet wird. Zeilenweise wäre schöner ist aber nicht zwingend.

Mich interessieren die Zeilen 48 bis 75, es können aber auch gerne alle Zeilen ausgegeben werden.

Hat jemand eine Idee?

Vielen Dank schon mal für eure Tipps.

PS: Das ist nur ein Ordner, ich habe über 500 davon, allerdings immer mit einer etwas anderen Struktur und auch mal mit ein paar mehr Dateien.
screenshot_dateiinhalt-struktur
screenshot_ordnerinhalt

Content-Key: 340508

Url: https://administrator.de/contentid/340508

Ausgedruckt am: 28.03.2024 um 18:03 Uhr

Mitglied: 133417
133417 13.06.2017 aktualisiert um 13:01:49 Uhr
Goto Top
Für eine Datei ist das Transponieren ja einfach, aber ich finde keine Weg das zu automatisieren.
VBA ...
Zelleninhalte mit bestimmtem Abstand transponieren mit Schleife

Zu Schleifen über alle Dateien im Verzeichnis findest du hier im Forum unzählige Beispiele. Einfach die Suchfunktion nutzen.

Gruß
Mitglied: HurtiKurti
HurtiKurti 13.06.2017 um 13:09:40 Uhr
Goto Top
Danke, das hilft.
Mitglied: colinardo
colinardo 14.06.2017, aktualisiert am 23.06.2023 um 12:35:00 Uhr
Goto Top
Servus @HurtiKurti, willkommen auf Administrator.de!
Als VBA:
Sub MergeTranspose()
    Dim isFirst As Boolean, pathFiles as String, fso as Object, file as Object, wsTemp as Worksheet
    pathFiles = "D:\HurtiKurti\Quelle"  
    Set fso = CreateObject("Scripting.FileSystemObject")  
    Set wsTemp = Sheets(2)
    isFirst = True
    With ActiveSheet
        For Each file In fso.GetFolder(pathFiles).Files
            If LCase(fso.GetExtensionName(file.Name)) = "csv" Then  
                With wsTemp.QueryTables.Add(Connection:="TEXT;" & file.Path, Destination:=wsTemp.Range("A1"))  
                    .Name = "import"  
                    .FieldNames = True
                    .AdjustColumnWidth = True
                    .RefreshPeriod = 0
                    .TextFilePlatform = 1252
                    .TextFileStartRow = 1
                    .TextFileParseType = xlDelimited
                    .TextFileTextQualifier = xlTextQualifierDoubleQuote
                    .TextFileSemicolonDelimiter = True
                    .Refresh BackgroundQuery:=False
                    .Delete
                End With
                If Not isFirst Then
                    wsTemp.Range("B1:B" & wsTemp.Cells(Rows.Count, "B").End(xlUp).Row).Copy  
                    .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial Transpose:=True  
                Else
                    wsTemp.UsedRange.Copy
                    .Range("A1").PasteSpecial Transpose:=True  
                End If
                
            End If
            isFirst = False
            wsTemp.UsedRange.Clear
        Next
    End With
End Sub
Oder als Powershell:
$all = @()
$folder = 'D:\HurtiKurti\Quelle'  
$csvout = 'D:\HurtiKurti\fertig.csv'  
gci $folder -Filter *.csv -File | %{
    $obj = [pscustomobject]@{}
    Import-csv $_.Fullname -Delimiter ";" -Header "A","B" | %{$obj | Add-Member -MemberType NoteProperty -Name $_.A -Value $_.B}  
    $all += $obj
}
$all | export-csv $csvout -Delimiter ";" -NoType -Encoding UTF8  
Pfade anpassen, fertig ist die Laube.

Grüße Uwe
Mitglied: HurtiKurti
HurtiKurti 14.06.2017 um 15:14:23 Uhr
Goto Top
Hallo Uwe,

vielen Dank.

Ich habe noch ein paar Fragen zur PowerShell Methode. Vielleicht hat noch jemand Lust und kann helfen.
1. Quelle gebe ich hier nur den Ordner an oder muss ich jede Datei einzeln anfügen bzw. kann ich mit Wildcard arbeiten.
2.a Funktioniert das ganze auch für Dateien mit der Endung .dat
2. b Die .dat Dateien haben als Delimiter "Tab", wie kann ich das eingeben (einfach Tab drücken zwischen den Anführungszeichen?)
3. Bei Code Zeile 6 steige ich nicht durch, meine Überschriften sind in Zeile 1, die anderen Zeilen sind Daten. Kann ich den Code aus Zeile 6 so lassen oder muss ich da noch etwas anpassen?

Tut mir leid, dass ich weiter mit Fragen löcher. Hoffe, ich übertreibe mit meinen Fragen/Wünschen nicht.

Vielen Dank für Eure Hilfe

Gruß Micha
Mitglied: colinardo
colinardo 14.06.2017 aktualisiert um 15:24:09 Uhr
Goto Top
Zitat von @HurtiKurti:
1. Quelle gebe ich hier nur den Ordner an oder muss ich jede Datei einzeln anfügen bzw. kann ich mit Wildcard arbeiten.
Ordner genügt es werden darin alle Files mit Endung *.csv verarbeitet
2.a Funktioniert das ganze auch für Dateien mit der Endung .dat
Ja, einfach den Filter in Zeile 04. bei -Filter anpassen. -Filter *.dat
2. b Die .dat Dateien haben als Delimiter "Tab", wie kann ich das eingeben (einfach Tab drücken zwischen den Anführungszeichen?)
Nein, machst du mit "`t" in Zeile 6 so
Import-csv $_.Fullname -Delimiter "`t"  
3. Bei Code Zeile 6 steige ich nicht durch, meine Überschriften sind in Zeile 1, die anderen Zeilen sind Daten. Kann ich den Code aus Zeile 6 so lassen oder muss ich da noch etwas anpassen?
Wenn du Überschriften hast (wusste ich nicht) musst du den Parameter -Header weg lassen und es so schreiben (Spaltennamen ["Spalte1"|"Spalte2"] natürlich anpassen)
Import-csv $_.Fullname -Delimiter ";" | %{$obj | Add-Member -MemberType NoteProperty -Name $_.'Spalte1' -Value $_.'Spalte2'}   
Die Funktionsweise ist diese in Zeile 5 wird ein leeres Custom Object erstellt. Dieses Object wird jetzt mit einer Schleife über die Einträge der CSV-Datei mit Eigenschaften befüllt. Der Name der Eigenschaft wird aus der ersten Spalte1 entnommen und der zugehörige Wert aus Spalte2 zugewiesen.
In Zeile 7 wird dann dieses Object einem Array hinzugefügt und zum Schluss das Array gesammelt wieder in eine CSV-Datei exportiert. Schnell einfach effektiv.
Tut mir leid, dass ich weiter mit Fragen löcher. Hoffe, ich übertreibe mit meinen Fragen/Wünschen nicht.
Zum Beantworten von Fragen sind wir hier ja da face-smile.

Grüße Uwe
Mitglied: HurtiKurti
HurtiKurti 14.06.2017 um 15:45:34 Uhr
Goto Top
Wie krass,

das ging schnell und alle Fragen leicht verständlich beantwortet.

Nun noch eine letzte (ich weiß "der Fischer und seine Frau"- Prinzip)

Ich habe insgesamt 500 Spalten mit Daten (und ca. 4500 Zeilen). Abgesehen von der Überschriften-Zeile (Zeile 1), dort sind die ersten 3 SpaltenText, der Rest Zahlen, habe ich bei den Daten, außer in Spalte 1, nur Zahlen. Spalte 1 ist die Uhrzeit allerdings als Txt formatiert. hhmmss ohne ":" dazwischen.

Mir reichen die Daten und die Uhrzeit kann gerne auch Text bleiben.

Meine Frage, muss ich die Spaltenanzahl noch irgendwo angeben oder kopiert er automatisch alles?

Danke Uwe für Deine super Hilfe bis hier.

Gruß Micha
screenshot_auszug_datei
Mitglied: colinardo
colinardo 14.06.2017 aktualisiert um 16:05:29 Uhr
Goto Top
Hm schön und gut, aber oben hast du beschrieben das deine CSV so aussieht:
Übeschrift1    Überschrift2
Property1    Wert1
Property2    Wert2
Property3    Wert3
Das hat nun mit deiner jetzt gezeigten Tabelle überhaupt nichts zu tun face-confused, irgendwas habe ich da wohl verpasst ??!

Mein Skript oben transponiert diese Tabelle automatisch in
Property1    Property2     Property3
Wert1        Wert2         Wert3
und das automatisch für alle CSV-Dateien, die Überschrift kommt natürlich dann in der Ausgabe-CSV nur einmal vor, logisch.

Bitte schick mir deine Frage via PM, wenn das hier nichts mehr mit dem eigentlichen Thread zu tun hat, sonst verwirrt das nur nachfolgende User die sich diesen Thread anschauen und nach Lösungen suchen. Merci.

Meine Frage, muss ich die Spaltenanzahl noch irgendwo angeben oder kopiert er automatisch alles?
Nein, es werden alle Überschriften(Alle Zeilen zu Spalten) automatisch umgewandelt
Mitglied: HurtiKurti
HurtiKurti 14.06.2017 um 16:05:13 Uhr
Goto Top
Uwe, Du hast natürlich recht.

Die Datei vom Anfang des Thread hat die Endung .csv und den beschriebenen Aufbau und dort funktioniert es auch wunderbar.

Ich habe gedacht, man könnte einen ähnlich kurzen Code für meine .dat Datei erstellen. Diese hat dann den anderen erst kürzlich beschriebenen Aufbau.

Tut mir leid, dass ich nun Verwirrung gestiftet habe.

Gruß Micha
Mitglied: colinardo
Lösung colinardo 14.06.2017, aktualisiert am 02.12.2022 um 18:17:47 Uhr
Goto Top
Zitat von @HurtiKurti:
Ich habe gedacht, man könnte einen ähnlich kurzen Code für meine .dat Datei erstellen. Diese hat dann den anderen erst kürzlich beschriebenen Aufbau.
Nur sagst du leider nicht was du damit machen willst face-smile
Selbstverständlich kannst du jedwede CSV in Powershell importieren und dort damit anstellen was du willst.
Per Default werden ja alle Spalten in das CSV-Objekt übernommen, wenn du nur bestimmte brauchst lässt sich das ebenfalls über ein zwischengeschaltetes select Spaltename1,Spaltenname2 definieren.

Vielleicht liest du einfach mal die Doku dazu face-wink, das sollte Unklarheiten aus dem Weg schaffen.
http://www.msxfaq.de/code/powershell/pscsv.htm


#edit# Falls du so eine Tabelle mit beliebig vielen Spalten in der Quell-CSV hast

screenshot

Kannst du folgendes transponiertes Ergebnis

screenshot

mit diesem Skript erreichen:

# source folder
$folder = 'A:\geologe\quelle'  
# target csv file
$csvout = 'A:\geologe\fertig.csv'  
# csv delimiter
$delimiter = ";"  
foreach($file in Get-ChildItem $folder -File -Filter *.csv){
    $colcount = (Get-Content $file.Fullname -Head 1).split($delimiter).count
    $csv = Import-csv $file.Fullname -Delimiter $delimiter -Header (1..$colcount)
    # create header template 
    $template = [ordered]@{}
    $csv.1 | %{$template.$_ = ''}  
    # add template x times as custom object
    $tbl = 1..($colcount-1) | %{[pscustomobject]$template}
    # for each row in csv
    foreach($row in $csv){
        # for each column (excluding first) in row
        2..$colcount | %{
            # add data to table
            $tbl[$_-2].($row.1) = $row.$_
        }
    }
    # append table to csv
    $tbl | export-csv $csvout -Delimiter $delimiter -NoType -Encoding UTF8 -Force -Append
}

Grüße Uwe
Mitglied: colinardo
colinardo 23.07.2017 um 12:36:17 Uhr
Goto Top
Wenns das dann war, den Beitrag bitte noch auf gelöst setzen, und Lösungen markieren. Merci.
Mitglied: nonessential
nonessential 12.12.2018 um 16:24:51 Uhr
Goto Top
Hallo Uwe

Wie würde der Powershell Import-CSV aussehen, wenn man alle Spalten ins Array $obj importieren möchte, nicht nur A + B?

besten Dank für deine Hilfe.

Gruss

Oliver