Opgelost Gegevens van meerdere Excel bestanden samenvoegen naar 1 tabblad

[Georgyboy]

Goeiedag,

Dubbele vraag als het kan?
Hier in het voorbeeld 3 Excelbestanden + 1 Excelbestand om naar toe te schrijven.
Programma 1, Programma 2, Programma 3
En Excelbestand: samenvoegen

1° vraag:
Kunnen we gegevens van de 3 Excelbestanden Batchnummer B6 en Proces uit Rij 3 (samengevoegde cel) in een lijst krijgen in bestand "Samenvoegen"?

2° vraag:
In de de 3 bestande rij 10 en 11 wissen
Dan de gegevens van de 3 Excelbestanden samenvoegen vanaf regel 9 bestand "Samenvoegen"?
We met 2 extra kolommen voor het batchnummer uit B6 en de tekst uit rij 3.

Deze gegevens halen we uit een vooraf gedownload programma per processtap, gemiddeld 10 à 20 per dag.
Aan de hand van het batchnummer weten we uit andere bronnen welk product het is,
1 proces kan dienen voor meerdere producten als de tijd en kooktemperatuur hetzelfde is.
We proberen onze producten zoveel mogelijk te koppelen aan 1 programma om het overzicht te bewaren en geen honderden programma´s te moeten aanmaken.

De opmaak kunnen we zelf niet veranderen daar dit zo is opgemaakt van de fabrikant.

Alvast bedankt!

 

[edmoor]

Maak een voorbeeld van hoe je het in het bestand samenvoegen.xls zou willen hebben.

 

[peter59]

Hallo,

Als ik het zo lees en je bestanden bekijk is dit iets heel moois voor power query.
Verdiep je daar eens in.

 

[Georgyboy]

@ peter59
Werk dikwijls met power query, via OBDC of eenvoudigere taken, maar hier moet ik nog leren!
weet niet of het zal lukken met 100 bestanden, vermoed dat dit teveel kan zijn of niet?

= Table.TransformColumnTypes(#"Tabelkolom uitgevouwen1",{{"Source.Name", type text}, {"Column1", type any}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}, {"Column5", type text}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}, {"Column10", type any}, {"Column11", type any}, {"Column12", type any}})

@edmoor
Maak nog een tabblad zoals gewenst

 

[Georgyboy]

Hier niet opgekuiste versie van Power Query, moet nog beter

 

[Georgyboy]

@ Edmoor

Hier heb ik een gewenst voorbeeld, zou dit mogelijk zijn met VBA?

@ Peter59
Zou dit ook mogelijk zijn met power query?

 

[snb]

Weet je zeker dat je dezelfde gegevens niet ook als CSV-bestand kunt laten exporteren ?

 

[snb]

Niet ontevreden met:

Sub M_snb()
   c00 = "J:\Temp\"
   c01 = Dir(c00 & "Programma*.xlsx")
  
   Do While c01 <> ""
     With GetObject(c00 & c01)
        .Sheets(1).UsedRange.Offset(11).Copy Sheet2.Cells(Rows.Count, 2).End(xlUp).Offset(1)
        Sheet2.Columns(2).SpecialCells(2).Offset(, -1).SpecialCells(4) = c01
        .Close 0
     End With
     c01 = Dir
   Loop
End Sub

 

[Georgyboy]

Bedankt SNB

Mag ik vragen wat ik verkeerd doe?

Het lukt me niet!

In de Map Gegevens is er een bestand “Samenvoegen” en 1 zonder bestand “Samenvoegen”

Alvast bedankt!

 

[snb]

Je wil toch de gegevens van de bestanden met de naam'programma' integreren ?
Zet het bestand 'samenvoegen' niet in dezelfde directory als de 'programma'-bestanden
Zet de code in het bestand 'samenvoegen'.
en gebruik

c00 = "C:\gegevens\"
   c01 = Dir(c00 & "Programma*.xlsx")

 

[JEC.]

Hier een PQ oplossing. Even de source folder path aanpassen

let
    Source = Folder.Files("C:\Users\xxxx\Downloads\test"), 
    fltr_files = Table.SelectRows(Source, each Text.Contains([Name], "Programma")),
    fltr_hidden_files = Table.SelectRows(fltr_files, each [Attributes]?[Hidden]? <> true),
    get_content = Table.AddColumn(fltr_hidden_files, "Transform File", each #"Transform File"([Content])),
    cols = Table.SelectColumns(get_content, {"Name", "Transform File"}),
    expand = Table.ExpandTableColumn(cols, "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
    delCols = Table.RemoveColumns(expand,{"Column6", "Column9"}),
    Headers = Table.RenameColumns(delCols,{{"Column1", "Date"}, {"Column2", "Time"}, {"Column3", "Step"}, {"Column4", "BadeprogramID"}, {"Column5", "Baseprogram"}, {"Column7", "ChamberTemp"}, {"Column8", "coreTemp"}, {"Column10", "chamberTempp"}, {"Column11", "coreTempp"}, {"Column12", "FValue"}}),
    GetBatch = Table.AddColumn(Headers, "Batch", each if [Date] = "Batch:" then [Time] else null),
    fill_batch = Table.FillDown(GetBatch,{"Batch"}),
    keep_dates = Table.SelectRows(fill_batch, each Value.Type([Date]) = type datetime),
    reorder = Table.ReorderColumns(keep_dates,{"Name", "Batch", "Date", "Time", "Step", "BadeprogramID", "Baseprogram", "ChamberTemp", "coreTemp", "chamberTempp", "coreTempp", "FValue"}),
    cTypes = Table.TransformColumnTypes(reorder,{{"Date", type date}, {"Time", type time}}),
    result = Table.ReplaceValue(cTypes,".xlsx","",Replacer.ReplaceText,{"Name"})
in
    result

 

[Georgyboy]

Dankjewel JEC.,

Sorry maar doe waarschijnlijk iets verkeerd?




let
Source = Folder.Files("C:\Gegevens"),
fltr_files = Table.SelectRows(Source, each Text.Contains([Name], "Programma")),
fltr_hidden_files = Table.SelectRows(fltr_files, each [Attributes]?[Hidden]? <> true),
get_content = Table.AddColumn(fltr_hidden_files, "Transform File", each #"Transform File"([Content])),
cols = Table.SelectColumns(get_content, {"Name", "Transform File"}),
expand = Table.ExpandTableColumn(cols, "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
delCols = Table.RemoveColumns(expand,{"Column6", "Column9"}),
Headers = Table.RenameColumns(delCols,{{"Column1", "Date"}, {"Column2", "Time"}, {"Column3", "Step"}, {"Column4", "BadeprogramID"}, {"Column5", "Baseprogram"}, {"Column7", "ChamberTemp"}, {"Column8", "coreTemp"}, {"Column10", "chamberTempp"}, {"Column11", "coreTempp"}, {"Column12", "FValue"}}),
GetBatch = Table.AddColumn(Headers, "Batch", each if [Date] = "Batch:" then [Time] else null),
fill_batch = Table.FillDown(GetBatch,{"Batch"}),
keep_dates = Table.SelectRows(fill_batch, each Value.Type([Date]) = type datetime),
reorder = Table.ReorderColumns(keep_dates,{"Name", "Batch", "Date", "Time", "Step", "BadeprogramID", "Baseprogram", "ChamberTemp", "coreTemp", "chamberTempp", "coreTempp", "FValue"}),
cTypes = Table.TransformColumnTypes(reorder,{{"Date", type date}, {"Time", type time}}),
result = Table.ReplaceValue(cTypes,".xlsx","",Replacer.ReplaceText,{"Name"})
in
result

 

[JEC.]

Even kijken in welke stap het misgaat

 

[JEC.]

Alleen weer even het pad aanpassen

 

[JEC.]

Hier nog eentje zonder die helper queries. Dit zou met aanpassen van het pad direct moeten werken als je dit in een lege query zet.

let
    Source = Folder.Files("C:\Users\xxxx\Downloads\test"),
    fltr_files = Table.SelectRows(Source, each Text.Contains([Name], "Programma")),
    fltr_hidden_files = Table.SelectRows(fltr_files, each [Attributes]?[Hidden]? <> true),
    get_content = Table.Combine(List.Transform(fltr_hidden_files[Content], each Excel.Workbook(_))),
    expand = Table.ExpandTableColumn(get_content, "Data", Table.ColumnNames(get_content[Data]{0})),
    delCols = Table.RemoveColumns(expand,{"Column6", "Column9", "Item", "Kind", "Hidden"}),
    Headers = Table.RenameColumns(delCols,{{"Column1", "Date"}, {"Column2", "Time"}, {"Column3", "Step"}, {"Column4", "BadeprogramID"}, {"Column5", "Baseprogram"}, {"Column7", "ChamberTemp"}, {"Column8", "coreTemp"}, {"Column10", "chamberTempp"}, {"Column11", "coreTempp"}, {"Column12", "FValue"}}),
    GetBatch = Table.AddColumn(Headers, "Batch", each if [Date] = "Batch:" then [Time] else null),
    GetProg = Table.AddColumn(GetBatch, "Programma", each try if Text.Contains([Date], "Programma") then [Date] else null otherwise null),
    fill = Table.FillDown(GetProg,{"Batch", "Programma"}),
    keep_dates = Table.SelectRows(fill, each Value.Type([Date]) = type datetime),
    reorder = Table.ReorderColumns(keep_dates,{"Programma", "Name", "Batch", "Date", "Time", "Step", "BadeprogramID", "Baseprogram", "ChamberTemp", "coreTemp", "chamberTempp", "coreTempp", "FValue"}),
    result = Table.TransformColumnTypes(reorder,{{"Date", type date}, {"Time", type time}})
in
    result

 

[peter59]

Hallo,

Ook nog een poging ondernomen.
Ik heb puur gebruik gemaakt van de ingebouwde functionaliteiten welke in power query zijn ingebouwd.
Het is dus absoluut niet zo mooi geschreven als wat JEC heeft gedaan.
Maak een map aan, zet daar alle werkbladen in en verander het pad.

Bron = Folder.Files("C:\Users\xxxx\xxxx\Bureaublad\Samenvoegen"),

@JEC.
Hier gaat je query helaas al de mist.

fltr_files = Table.SelectRows(Source, each Text.Contains([Name], "Programma")),

In de voorbeeld bestanden van TS heeft maar een bijlage, een rij de naam "Programma" staan en bij de andere 2 "Proces".

 

[JEC.]

Inderdaad, dan deze regel even aanpassen

GetProg = Table.AddColumn(GetBatch, "Programma", each try if Text.Contains([Date], "Programma") or Text.Contains([Date], "PROCES") then [Date] else null otherwise null)

 

[cow18]

voor de hein eventjes de macro van @snb gedocumenteerd en lichtjes aangepast zodat je die kan begrijpen.

Sub M_snb()
     c00 = ThisWorkbook.Path & "\"           'je subdirectory van je bestanden
     c01 = Dir(c00 & "Programma *.xlsx")     'zo noemen je bestandjes en ook per direct het eerste bestand die zo noemt
     Set sh = ThisWorkbook.Sheets("Blad1")   'blad in deze file waar je naar toe kopieert

     Do While c01 <> ""                      'loopje door alle gelijkaardige bestanden
          With GetObject(c00 & c01).Sheets(1)     'open bestandje op de achtergrond en gebruik 1e blad
               arr = Array(.Range("B6").Value, .Range("A3").Value)     'ophalen van deze 2 vaste gegevens
               Set C = .Range("A12").CurrentRegion.Resize(, 12)     'je procesgegevens vanaf A12, 12 kolommen breed
               With sh.Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(C.Rows.Count)     'doelcel
                    .Resize(, 2).Value = arr     'je 2 vaste gegevens
                    .Offset(, 2).Resize(, C.Columns.Count).Value2 = C.Value2     'de procesgegevens
               End With
               .Parent.Close 0               'bestand sluiten zonder opslaan
          End With
          c01 = Dir                          'volgende gelijkaardige bestand
     Loop

     With sh
          .Range("C:C").NumberFormat = "dd-mm-yy"     'datum
          .Range("D:D").NumberFormat = "hh:mm:ss"     'tijd (zijn eigenlijk 2 keer dezelfde waarden
          .Range("L:N").NumberFormat = "0.0"     'temperaturen tot op een tiende graad
          .Range("A1").Resize(, 12).EntireColumn.AutoFit
     End With
End Sub

 

[JEC.]

Zodra je bestanden toenemen en je runt deze nogmaals, dan wordt alles dubbel in het doelbestand geplakt. Dan zou je de macro aan moeten passen zodat je de reeds bestaande bestanden overslaat.

Met Power Query wordt de dataset gewoon netjes uitgebreid.

 

[cow18]

okay @JEC. , er van uitgaande dat de combinatie B6-A3 een unieke sleutel maakt, wordt die geen 2e keer doorlopen.