Beiträge zum Thread: vorgegebenen Bereich aus mehreren Dateien in ein Blatt

Hallo Christian,

via Power Querry teste mal:

let

    // Ordner einlesen

    Quelle = Folder.Files("C:\DeinVerzeichnis"),

    CSVs = Table.SelectRows(Quelle, each Text.Lower([Extension]) = ".csv"),

    MitListe = Table.AddColumn(CSVs, "Liste", each let

    Source = Csv.Document(File.Contents([Folder Path] & [Name]), [Delimiter = ";", Encoding = 65001, QuoteStyle = QuoteStyle.None]),

    Promoted = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),

    ColNames = Table.ColumnNames(Promoted),

    HasColB = List.Count(ColNames) >= 2,

    ColB = if HasColB then Table.Column(Promoted, ColNames{1}) else List.Repeat({null}, 53),

    ColB_fixed = if List.Count(ColB) < 53 then ColB & List.Repeat({null}, 53 - List.Count(ColB)) else ColB,

    Werte = List.FirstN(List.Skip(ColB_fixed, 1), 52) in Werte),

    AlsTabelle = Table.TransformColumns(MitListe,{"Liste", each let SpaltenNamen = List.Transform({2..53}, each "B" & Text.From(_)) in Table.FromRows({ _ }, SpaltenNamen)}),

    Expandiert = Table.ExpandTableColumn(AlsTabelle,"Liste", List.Transform({2..53}, each "B" & Text.From(_)),List.Transform({2..53}, each "B" & Text.From(_))),

    MitDatei = Table.RenameColumns(Expandiert,{{"Name", "Datei"}}),

    Ergebnis =Table.SelectColumns(MitDatei,{"Datei"} & List.Transform({2..53}, each "B" & Text.From(_)))

in

    Ergebnis

Den Verzeichnispfad musst du ohne abschließenden Backslash deinen Gegebenheiten anpassen.

Gruß Uwe

Sorry,

da war noch ein Fehler drin;
Die Zeile Werte = .... liest ab B3 ein.

Richtig ist diese Zeile so:

Werte = List.FirstN(ColB_fixed, 52) in Werte),

kompletter korrigierter M-Code:

let

    // Ordner einlesen

    Quelle = Folder.Files("C:\DeinVerzeichnis"),

    CSVs = Table.SelectRows(Quelle, each Text.Lower([Extension]) = ".csv"),

    MitListe = Table.AddColumn(CSVs, "Liste", each let

    Source = Csv.Document(File.Contents([Folder Path] & [Name]), [Delimiter = ";", Encoding = 65001, QuoteStyle = QuoteStyle.None]),

    Promoted = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),

    ColNames = Table.ColumnNames(Promoted),

    HasColB = List.Count(ColNames) >= 2,

    ColB = if HasColB then Table.Column(Promoted, ColNames{1}) else List.Repeat({null}, 53),

    ColB_fixed = if List.Count(ColB) < 53 then ColB & List.Repeat({null}, 53 - List.Count(ColB)) else ColB,

    Werte = List.FirstN(ColB_fixed, 52) in Werte),

    AlsTabelle = Table.TransformColumns(MitListe,{"Liste", each let SpaltenNamen = List.Transform({2..53}, each "B" & Text.From(_)) in Table.FromRows({ _ }, SpaltenNamen)}),

    Expandiert = Table.ExpandTableColumn(AlsTabelle,"Liste", List.Transform({2..53}, each "B" & Text.From(_)),List.Transform({2..53}, each "B" & Text.From(_))),

    MitDatei = Table.RenameColumns(Expandiert,{{"Name", "Name derDatei"}}),

    Ergebnis =Table.SelectColumns(MitDatei,{"Name derDatei"} & List.Transform({2..53}, each "B" & Text.From(_)))

in

    Ergebnis

Gruß Uwe

Hallo Uwe,

habe es jetzt mal mit 10 Dateien getestet, sieht bis auf einen kleinen Schönheitsfehler super aus.
Kann man noch die Dateiendung aus den in der ersten Spalte ausgegebenen Dateinamen wegbekommen?
Ich versuche hier parallel auch selbst mal mein Glück.

Gruß und danke
Christian

neue Spalte erstellt mit Text.BeforeDelimiter und die alte Spalte gelöscht.

geändert auf direkten Wege:

let

    // Ordner einlesen

    Quelle = Folder.Files("C:\DeinVerzeichnis"),

    CSVs = Table.SelectRows(Quelle, each Text.Lower([Extension]) = ".csv"),

    MitListe = Table.AddColumn(CSVs, "Liste", each let

    Source = Csv.Document(File.Contents([Folder Path] & [Name]), [Delimiter = ";", Encoding = 65001, QuoteStyle = QuoteStyle.None]),

    Promoted = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),

    ColNames = Table.ColumnNames(Promoted),

    HasColB = List.Count(ColNames) >= 2,

    ColB = if HasColB then Table.Column(Promoted, ColNames{1}) else List.Repeat({null}, 53),

    ColB_fixed = if List.Count(ColB) < 53 then ColB & List.Repeat({null}, 53 - List.Count(ColB)) else ColB,

    Werte = List.FirstN(ColB_fixed, 52) in Werte),

    AlsTabelle = Table.TransformColumns(MitListe,{"Liste", each let SpaltenNamen = List.Transform({2..53}, each "B" & Text.From(_)) in Table.FromRows({ _ }, SpaltenNamen)}),

    Expandiert = Table.ExpandTableColumn(AlsTabelle,"Liste", List.Transform({2..53}, each "B" & Text.From(_)),List.Transform({2..53}, each "B" & Text.From(_))),

    MitDatei = let Umbenannt = Table.RenameColumns(Expandiert, {{"Name", "Datei"}}),

    OhneExt = Table.AddColumn(Umbenannt,"Datei Name",each Text.BeforeDelimiter([Datei], "."),type text)in OhneExt,

    Ergebnis = Table.SelectColumns(MitDatei, {"Datei Name"} & List.Transform({2..53}, each "B" & Text.From(_)))

in

    Ergebnis

Gruß Uwe

Hallo Uwe,

ja vielen Dank,

hab ich direkt geändert.

Gruß
Christian

Hi

mit VBA wäre das hier eine Möglichkeit.

Sub test()

Dim datei As String

Dim pfad As String

Dim wb As Workbook



pfad = ThisWorkbook.Path



datei = Dir(pfad & "\*.csv")

Do Until datei = ""

    Set wb = Workbooks.Open(pfad & "\" & datei, ReadOnly:=True, local:=True)

    With ThisWorkbook.Sheets(1).Cells(Rows.Count, 1).End(xlUp)

        .Offset(1, 0).Value = Replace(datei, ".csv", "")

        wb.Sheets(1).Cells(1, 1).CurrentRegion.Columns(2).Offset(1, 0).Copy

        .Offset(1, 1).PasteSpecial xlPasteValues, Transpose:=True

    End With

    Application.CutCopyMode = False

    wb.Close False

    datei = Dir

Loop

    

End Sub

bei pfad legst du den Dateipfad fest, hier ist es der Ordner, in dem sich auch die Datei mit dem Makro befindet.

Gruß Daniel

Hallo Daniel,

hab auch das mit 10 Dateien getestet, auch das funktioniert.
Jetzt versuche ich nur noch. zu vermeiden dass sich da ständig Fenster öffnen und Schließen. Aber glaub ich hab da was gefunden. Mit Open For Binary versuche das grad einzubauen.

Gruß und danke
Christian

Hi
ja, kann damit funktionieren, allerdings muss das Übernehmen der Daten dann anders programmiert werden.

du kannst aber das Flackern des Bildschirms beim Öffnen der Datei auch noch auf zwei anderen Wegen verhindern:

- füge am Anfang des Codes die Zeile ein

 Application.ScreenUpdating = False

Das friert den Bildschirm ein solange das Makro läuft und du siehst das Öffnen und Schließen der Dateien nicht mehr.

- ersetze die Zeile mit Set wb = Workbooks.Open(...) durch

set wb = GetObject(Pfad  & "\" & Datei)

das GetObject öffnet die Dateien im Hintergrund.

Gruß Daniel

Hallo Daniel,

danke für den Hinweis

Gruß
Christian

Moin Christian, :-)

... zwei Möglichkeiten mit Power Query: ;-)
https://www.herber.de/bbs/user/180908.xlsx

Den Pfad zu den CSV-Dateien musst du im Code ändern. Machst du sowas öfter, kann man den Pfad auch aus einer Zelle lesen, oder den Pfad nehmen, in welchem die Exceldatei UND die CSV-Dateien sind.

tblErg_2 ist nur Verbindung - das könnte bei vielen Dateien etwas schneller sein. ;-)

Servus
Case

Hallo Case,

vielen Dank. Funktioniert beides.

Gruß
Christian

Hallo ihr 3,

danke nochmal für eure Unterstützung,

ich habe mehrere Vorschläge (alle funktionierend) bekommen, bei denen ich bei allen zugeben muss, ich hätte sie ohne Eure Hilfe nicht hinbekommen.
Jetzt habe ich die Qual der Wahl und da ich leider da kaum Ahnung habe habe ich zugegebenermaßen KI (Copilot) gefragt, welches sie für meinen Fall mit 2047 Dateien für die geeignetste Methode hält und sie sagte Case' tblErg_2. Da mir die Kenntnisse fehlen, um jetzt im Detail beurteilen zu können ob das wirklich die beste Version ist oder KI da einen Fehler macht, werde ich dann erstmal diese Version nehmen.
Wenn ihr da anderer Meinung seid, könnt ihr mir es natürlich gerne sagen.

Gruß
Christian

Moin Christian, :-)

... Erklärung: ;-)

Lines.FromBinary() ist nur dann die schnellste Methode (bezogen auf Power Query), wenn du "einfach strukturierte" CSV-Dateien hast. ;-)

Das ist bei dir ja der Fall. Der "Rahmen" ist klar - 53 Zeilen (Header + 52 Datenzeilen). Mehr nicht. ;-)

Du sparst dir das CSV-Parsing, die Tabellenerzeugung und die Spaltenverwaltung. ;-)

Noch etwas mehr Geschwindigkeit könntest du erreichen, wenn du die Codezeile...

Rang = List.Transform(

         Werte,

         each Text.AfterDelimiter(_, ";")

)

Damit ersetzt: ;-)

Rang =

         List.Transform(

            List.FirstN(List.Skip(Lines.FromBinary([Content], null, null, 65001),1),52),

            each Text.Range(_, Text.PositionOf(_, ";")+1)

         )

Die beiden M-Codezeilen dann entfernen: ;-)

Zeilen = Lines.FromBinary([Content], null, null, 65001),

Werte = List.FirstN(List.Skip(Zeilen,1),52),

Nun wird kein Text.AfterDelimiter() mehr aufgerufen. ;-)

Du kannst sowas auch über "Table.Pivot(..." erreichen, aber das wäre in dem Fall eine "teure Operation". ;-)
Nicht falsch verstehen - Pivotieren/Entpivotieren sind tolle Funktionen - aber in dem Fall "Bremsen". ;-)

Es kommt immer auf die jeweilige Situation an. ;-)

Servus
Case

mit einer Schleife
eine Datei nach der anderen Öffnen und einfügen..

Moin Christian, :-)

... "Insider-Kanal" ist, dann hat man die Funktion "IMPORTTEXT": ;-)
https://support.microsoft.com/de-de/excel/functions/importtext-function

Damit kann man eine CSV-Datei schnell auslesen: ;-)



Ich habe - für diesen Test - die Dateinamen (ohne Pfad und Dateiendung) in einer anderen Tabelle ab A2 stehen. ;-)

Die 3 Zeilen (A4:A6) habe ich mit VBA generiert: ;-)

Option Explicit

Public Sub Main()

    [A4:A6].Formula2 = "=HSTACK(Tabelle2!A2,TRANSPOSE(CHOOSECOLS(IMPORTTEXT(""C:\Temp\CSVDateien\""&Tabelle2!A2&"".csv"","";"",1,52),2)))"

    [A4].SpillingToRange.Value = [A4].SpillingToRange.Value

End Sub

Formel eingetragen und gleich in Werte umgewandelt. ;-)

Es ist mir nicht gelungen, das per Formel (also spillen nach rechts UND unten) hinzubekommen. ;-)
Mit MAP und LAMBDA probiert, aber IMPORTTEXT sträubt sich in der Arrayverarbeitung. ;-)

Vom Grundsatz her würde ich das aber mit Power Query lösen. Per VBA ist es mit "Open PfadUndDatei For Binary As #1" und Array möglich. Das dürfte auch sehr schnell sein. ;-)

Servus
Case

Hallo Case,

mir steht die Importtext Funktion leider nicht zur Verfügung. Aber trotzdem danke für das Beispiel. Wie ja bereits gesagt, ich hatte mich für eine deiner beiden VBA Varianten entschieden.

Gruß
Christian