Samenvoegen Excel-bestanden

[RBV]

Ik heb 100 simpele Excel-bestanden met allemaal dezelfde format. Ze bestaan allemaal uit slechts één gevulde kolom met 10 tot 20 items. Het resultaat dat ik graag wil hebben, bestaat uit twee kolommen:
Bestandnaam1 | item1.1
Bestandnaam1 | item1.2
Bestandnaam1 | item1.3
Bestandnaam1 | item1.4
Bestandnaam1 | item1.5
Bestandnaam1 | item1.6
Bestandnaam2 | item2.1
Bestandnaam2 | item2.2
Bestandnaam2 | item2.3
Bestandnaam3 | item3.1
Bestandnaam3 | item3.2
Bestandnaam3 | item3.3
Bestandnaam3 | item3.4
Als ik dit handmatig allemaal in één tabblad wil zetten, dan ben ik nogal een tijdje bezig. Ik voeg mij daarom af, of het mogelijk is om alle 100 bestanden te selecteren en in één keer in een Excel-blad te openen?

 

[JeanPaul28]

met vba importeren, die 100 bestanden in een directory zetten

 

[snb]

Waar worden die Excelbestanden gegenereerd ?
Zijn het geen csv bestanden ?

 

[RBV]

De extensie van deze bestanden is *.csv en bij eigenschappen staat "Bestandstype: Microsoft Excel 2016 bestand"

 

[jkpieterse]

Gegevens tab, Nieuwe query, Uit bestand, Uit Map:
- map kiezen
- gegevens transformeren klikken
- lijst filteren op de csv extensies
- eventueel een filter instellen voor de bestandsnaam
- op het kleine ikoontje klikken rechts van de kop van de eerste kolom (titel van de knop is "bestanden combineren").

Vervolgens kan je dingen opgeven zoals het scheidings teken.

 

[RBV]

Na map kiezen, komen er andere keuzes, namelijk:
- Combineren
-- Combineren en bewerken
-- Combineren en laden
-- Combineren en laden naar
- Laden
-- Laden
-- Laden naar
- Bewerken
- Annuleren

Ik heb wat keuzes geprobeerd en kom niet verder. Bij "Combineren en bewerken" krijg ik een foutmelding, namelijk:

Expression.Error: Kan een waarde van type Binary niet converteren naar type Table.
Details:
Value=Binary
Type=Type

Zie ik misschien iets over het hoofd?

 

[jkpieterse]

Je moet denk ik "Bewerken" kiezen, die was ik vergeten (knop heet bij mij "Clean data", kennelijk wordt PowerQuery nog niet zo goed vertaald). Het is de tweede knop van rechts in het venster.

 

[RBV]

Ik ben nu op die plek terechtgekomen.
Wanneer ik echter de op een na laatste stap zet (de stap vóór "Vervolgens kan je dingen opgeven zoals het scheidings teken.") krijg ik ook hier die foutmelding die ik hierboven noemde.

 

[snb]

Pas G:\ aan waar jouw csv bestanden staan
Pas F:\ aan naar waar jij de geïntegreerde data wil hebben.

Draai eerst deze macro:

Sub M_snb()
  Shell "cmd /c copy G:\*.csv F:\alles.csv", 0
End Sub

Dan de volgende

Sub M_snb()
   Workbooks.Open "F:\alles.csv"
End Sub

 

[jkpieterse]

Heb je gecontroleerd of er geen bestanden tussen zitten in de gefilterde lijst die geen CSV bestanden zijn? Anders gaat het inderdaad mis.

 

[RBV]

@SNB: ik ben op mijn werk en kan geen macro's draaien
@jkpieterse: er staan alleen csv-bestanden in de map

Een andere collega heeft net nog even met mij meegekeken en het is voor een deel gelukt, maar helaas niet zodat het goed werkbaar is. Nu heb ik namelijk in Cel A1 alle items staan en in cel B1 de bestandsnaam (van C1 t/m G1 nog andere bestandsinfo).
@jkpieterse: is dit ook de uitvoer zoals je hem voor ogen had? (want dan moet ik het ermee doen en eens zien of ik het handmatig kan omzetten naar losse regels, al is dat toch nog behoorlijk wat werk)

Als het volgende toch ook mogelijk is, zou ik nog heel graag daar tips over willen hebben:

kolom A | kolom B
1 Bestandnaam1 | item1.1
2 Bestandnaam1 | item1.2
3 Bestandnaam1 | item1.3
4 Bestandnaam1 | item1.4
5 Bestandnaam1 | item1.5
6 Bestandnaam1 | item1.6
7 Bestandnaam2 | item2.1
8 Bestandnaam2 | item2.2
9 Bestandnaam2 | item2.3
10 Bestandnaam3 | item3.1
11 Bestandnaam3 | item3.2
12 Bestandnaam3 | item3.3
13 Bestandnaam3 | item3.4

Morgen weer een dag, ik ga nu eerst naar huis. Bedankt voor alle suggesties trouwens! Eventueel tot morgen.

 

[RBV]

P.S.
Aanvulling op: ... Cel A1 alle items staan en in cel B1 de bestandsnaam (van C1 t/m G1 nog andere bestandsinfo):
Hetzelfde geldt voor:
A2, B2, C2 t/m G2
A3, B3, C3 t/m G3
A4, B4, C4 t/m G4
enz.

 

[jkpieterse]

Je kunt kolommen die overbodig zijn simpel verwijderen in de query. Verder moet je bij de opties van de query de juiste land instelling gebruiken om de juiste opsplitsing in kolommen te krijgen en het juiste decimaal scheidingsteken.

 

[RBV]

Er zijn juist geen kolommen overbodig, maar de hele reeks is in één cel gekomen. Klopt het dat dit niet anders te doen is?

kolom A | kolom B
1 item1.1 | Bestandnaam1
item1.2
item1.3
item1.4
item1.5
item1.6

2 item2.1 | Bestandnaam2
item2.2
item2.3

3 item3.1 | Bestandnaam3
item3.2
item3.3
item3.4

 

[jkpieterse]

Nee dat klopt niet, het kan zeker anders. Maar je moet in de query instellingen iets veranderen. Selecteer een cel in de resullterende tabel en kies Gegevens, Nieuwe query, Queryopties. Stel bij regionale instellingen een ander land in. Het lastige is hoe vind ik het land met de juiste instellingen voor decimaal, duizend en datum scheidingstekens en de juiste datum volgorde. Het staat hier tamelijk goed uitgelegd: https://blog.crossjoin.co.uk/2015/0...ional-date-and-number-formats-in-power-query/