Tekst bestanden importeren in Excel werkbladen

Inhoud

Inleiding

In dit artikel laat ik zien hoe je het importeren van .txt, .prn en/of .csv bestanden in een Excel werkblad kunt vereenvoudigen.

Als je ooit geprobeerd hebt om een tekstbestand te openen in Excel (Bestand, Openen, kies "Tekst bestanden" als bestandstype), dan heb je al kennis gemaakt met de "Wizard Tekst importeren". Als je bovendien wel eens een CSV bestand met Excel geopend hebt dan weet je ook, dat deze handige wizard niet wordt gestart bij dit type bestanden. Wat te doen, als je in de situatie zit dat je regelmatig bestanden moet importeren met een identieke structuur, waarbij je dus iedere keer precies dezelfde tekst importinstellingen nodig hebt? En hoe importeer je de gegevens uit die bestanden in hetzelfde werkblad, zonder de importdefinitie steeds opnieuw op te moeten geven?

In dit artikel laat ik zien hoe je deze taak makkelijk maakt met behulp van zowel Ophalen & transformeren, als met de oude wizard tekst importeren.

Ophalen en Transformeren (oftewel Power Query)

Voordat je de PowerQuery import stappen uitvoert, is het belangrijk eerst jezelf enkele vragen te stellen:

  1. Zal de naam van het te importeren bestand telkens hetzelfde zijn?
  2. Zal de locatie (regelmatig) veranderen?
  3. Wil ik één bestand importeren, of wellicht meerdere bestanden?

Als het antwoord op één van deze vragen Ja is, overweeg dan om andere stappen te kiezen dan degene die ik hieronder beschrijf. In dergelijke gevallen kan je beter gebruik maken van de keuze "Uit map": Gegevens tab, Gegevens ophalen knop, Uit bestand, Uit map. Deze route geeft je vervolgens de mogelijkheid om bijvoorbeeld de bestanden te filteren op extensie, eventueel ze te sorteren zodat de nieuwste bovenaan staat en vervolgens alleen dat bestand te importeren. Of misschien wil je alle .txt bestanden in een map openen die een naam hebben die start met "balans". De mogelijkheden zijn eindeloos.

Hoe dan ook, laten we eens een enkel CSV bestand importeren.

De Landinstellingen kiezen

CSV bestanden kennen geen internationale standaard. Het enige dat zeker is, is dat er gebruik wordt gemaakt van een scheidingsteken om de kolommen te scheiden. Hetzelfde geldt voor andere zaken in CSV bestanden: Er is geen overeenkomst over hoe een datum eruit ziet noch een afspraak over het decimaal- of het duizendtalscheidingsteken. Dit alles hangt af van de landinstellingen van de computer die de CSV heeft geproduceerd. Het kan dus zijn dat je te maken krijgt met een CSV met de puntkomma als scheidingsteken voor de kolommen, komma's als decimaalscheidingsteken en een datumvolgorde van dd-mm-jjjj. In de oude tekst importeren wizard (zie verderop in dit artikel), kies je deze instellingen pas nadat je het bestand hebt gekozen. In PowerQuery is het het makkelijkst als je dit vooraf doet.

Het is een goed idee om de CSV eerst even in Kladblok (Notepad) te openen, zodat je kunt zien wat het bestand bevat. Dat stelt je in staat om te zien hoe het zit met:

  • Kolom scheidingsteken
  • Decimaal scheidingsteken
  • Duizendtallen scheidingsteken
  • Datum opmaak en volgorde

Bekijk je CSV in kladblok (Notepad)

Als je een beetje geluk hebt, dan is de CSV gemaakt met standaard landinstellingen (en niet zoals bovenstaand voorbeeld waar de kolom- en decimaalscheidingstekens dezelfde zijn). Vertel PowerQuery dus welke landinstellingen het moet gebruiken bij het importeren. Klik de Gegevens tab, kies Gegevens ophalen en klik "Query opties". Klik vervolgens op de tab "Regionale instellingen" en kies de juiste landinstellingen:

De landinstellingen aanpassen voordat je een CSV importeert

Hoe weet je nu welke landinstellingen je nodig hebt?? Het dialoogvenster vertelt je niet welke scheidingstekens en formaten bij elke landinstelling horen. Google helpt hier enorm, Chris Webb heeft dit al voor ons uitgezocht! Chris heeft zelfs een Excel bestandje gemaakt met daarin alle instellingen.

Nu de landinstellingen goed staan zijn we klaar om het importeren te starten. Klik Gegevens, Uit tekst/CSV. Nadat je het gewenste bestand gekozen hebt, verschijnt dit venster:

De eerste stap uit de PowerQuery Uit tekst/CSV optie

Zoals je kunt zien heeft Power Query geprobeerd om zaken als het scheidingsteken en de gegevenstypen (op basis van de eerste 200 rijen) af te leiden uit de gegevens in het bestand. Wijzig deze instellingen als ze niet juist zijn.

De beste stap die je vervolgens kunt nemen is klikken op "Gegevens transformeren". Direct op Laden klikken is vrijwel nooit goed genoeg want je zult daarna veel kliks nodig hebben om alsnog wijzigingen aan te brengen indien bijvoorbeeld datums niet als datums zijn herkend tijdens het importeren. Klik dus altijd op Gegevens transformeren zodat je e.e.a. direct kan corrigeren.

Nadat je op Gegevens transformeren hebt geklikt, opent het Power Query venster. Controleer nu alle kolommen. Het icoontje naast hun naam in de koprij geeft aan welk gegevenstype voor die kolom geldt:

Het Power Query venster met een voorlopige weergave van de gegevens

Heeft een kolom het verkeerde gegevenstype, klik dan op het ikoontje en kies het juiste type. Dit is een belangrijke stap. Verkeerde gegevenstypen zorgen voor foute berekeningen in Excel!

Dit is het moment om dingen te doen als verwijderen van kolommen die niet nodig zijn of sorteren van de tabel op de gewenste kolommen. Zodra je tevreden bent met de gegevens klik je op Start tab van het PQ lint en op de vervolgkeuzelijst onder "Sluiten en laden", kies "Sluiten en laden naar..."

PowerQuery venster, sluiten en laden

In het daaropvolgende dialoogvenster kan je keuzes maken met betrekking tot waar PowerQuery de geïmporteerde gegevens moet laten:

Het gegevens importeren venster na klikken op sluiten en laden naar...

Helaas is hier de knop Eigenschappen niet beschikbaar. Klik op OK om -zoals ingesteld in het voorbeeld- de gegevens te laden in een tabel op het huidige werkblad.

Om alsnog de eigenschappen van de verbinding te kunnen aanpassen klik je in de tabel en vervolgens op de Tabelontwerp tab op de Eigenschappen knop:

De eigenschappen van de gegevensverbinding bewerken

In het dialoogvenster dat opent kan je dingen instellen zoals wat er moet gebeuren als er meer of juist minder rijen met gegevens worden geïmporteerd en of de kolombreedtes telkens mogen worden aangepast:

Het dialoogvenster Externe gegevenseigenschappen

Er zijn nog meer eigenschappen in te stellen. Deze vind je door ervoor te zorgen dat het venster Query's en verbindingen zichtbaar is door te klikken op de juiste knop op het lint op de Gegevens tab:

The Queries & Connections task pane

Je kunt dan rechts-klikken op de query en Eigenschappen kiezen:

Het dialoogvenster Queryeigenschappen

De oude wizard tekst importeren gebruiken

De oude wizards weer zichtbaar maken

Sinds Excel 2016 is Power Query ingebouwd in Excel. Om het gebruik van PowerQuery te promoten, zijn toen de oude wizards verborgen. Zo krijg je deze weer terug:

Klik Bestand, Opties en klik op de tab Gegevens. Vink de wizards aan die je nodig hebt, zie hieronder:

Enabling legacy data import wizards

Vervolgens vind je hier de oude wizard tekst importeren:

Import data from text files on Excel 2007/2010 ribbon.

Instellen welk (soort) bestand geïmporteerd moet worden

Selecteer eerst het werkblad waar je de gegevens in wilt ontvangen. Zoek op het lint de tab Gegevens en klik vervolgens op de drop-down "Gegevens ophalen", dan op Oude wizards en tenslotte op de knop "Uit tekst (verouderd)".

 De knop Uit tekst (verouderd) op het lint

Selecteer het juiste bestand uit het volgende dialoogvenster:

Het dialoogvenster Tekstbestand importeren

Het dialoogvenster Tekstbestand importeren.

Tot nu toe hebben we alleen maar opgegeven wat we willen importeren. Nu is het tijd om aan te geven hoe dit moet gebeuren.

Klik op de Openen knop. De Wizard Tekst importeren opent haar deuren:

Stap 1 van de Wizard Tekst importeren, definieer het bestandstype

Stap 1 van de Wizard Tekst importeren, definieer het bestandstype.

In dit voorbeeld heb ik aangegeven dat mijn bestand van het type Windows (ANSI) is en dat ik een bestand heb met scheidingstekens. Klik op Volgende als de instellingen goed staan voor jouw bestand.

Stap 2 van de wizard geeft de mogelijkheid om de scheidingstekens in te stellen. Ik heb hem ingesteld op Komma:

  Stap 1 van de Wizard Tekst importeren, definieer het scheidingsteken

Stap 2 van de Wizard Tekst importeren, definieer het scheidingsteken

Klik weer op Volgende om in stap 3 te komen, waar je voor iedere kolom kunt instellen wat het formaat van die kolom moet zijn. Ik heb het datumformaat van de eerste kolom ingesteld op de volgorde DMJ. Klik op een kolom als je het formaat ervan wilt aanpassen.

Stap 3 van de Wizard Tekst importeren, kolom formattering instellen

Stap 3 van de Wizard Tekst importeren, kolom formattering instellen

Als je op de knop Geavanceerd klikt, dan kan je instellen welk decimaalteken moet worden gebruikt tijdens het importeren:

Het dialoogvenster Geavanceerde instellingen voor tekst importeren

Het dialoogvenster Geavanceerde instellingen voor tekst importeren

Merk op, dat de instellingen in dit venster gelden voor alle kolommen.

Nadat alle kolommen zijn ingesteld klik je op de knop Voltooien. Excel opent nu het venster Gegevens importeren en vraagt daarmee waar je de gegevens wilt plaatsen. Selecteer de juiste plek in je bestand.

Het dialoogvenster Gegevens importeren

Het dialoogvenster Gegevens importeren

Ho nu even! Nog niet op OK klikken! Klik op de knop Eigenschappen. Het dialoogvenster Eigenschappen extern gegevensbereik opent zich. Hier kan je enkele belangrijke instellingen wijzigen.

Het dialoogvenster Eigenschappen extern gegevensbereik

Het dialoogvenster Eigenschappen extern gegevensbereik

Merk op, dat ik enkele belangrijke eigenschappen heb uitgelicht. Ik bespreek die hieronder.

Querydefinitie opslaan

Laat deze aangevinkt, anders moet je de instellingen telkens opnieuw doen en dat was nou net niet de bedoeling!

Vragen om bestandsnaam bij vernieuwen

Als je dit vakje aan vinkt, dan zal Excel bij het vernieuwen van de gegevens altijd om een bestandsnaam vragen. Laat dit vakje uitgevinkt als je bestandsnaam en -locatie steeds dezelfde is.

Bestaande cellen overschrijven met nieuwe gegevens, ongebruikte cellen wissen

Welke selectie hier het beste is, hangt helemaal af van je specifieke situatie. De eerste twee mogelijkheden zorgen ervoor, dat gegevens onder je importeerbereik omlaag geduwd worden c.q. omhoog getrokken worden als je meer of minder gegevens importeert.

Ik beveel aan om voor te importeren gegevens een apart werkblad te nemen, waarop niets anders staat dan het importeerbereik en eventuele naastgelegen formules.

Formules doorvoeren naar aangrenzende kolommen

Een hele handige optie. Heb je het plan om een berekening te doen met elke rij van de import, dan zorgt deze optie ervoor dat deze cellen ook formules blijven houden als de hoeveelheid gegevens toeneemt.

Klik OK als je tevreden bent met de instellingen.

Importeren van de gegevens

Tenslotte klik je op OK en je gegevens worden ingelezen.  Mijn werkblad ziet er nu zo uit:

Resultaten na het importeren

Resultaten na het importeren

Nu de import juist is ingesteld zou ik het bestand maar eens opslaan.

De gegevens vernieuwen

Tot nu toe was alles erop gericht om de import instellingen juist te krijgen en ervoor te zorgen dat we die niet nog eens hoeven in te stellen. Maar hoe importeer je nou een nieuw bestand? Heel eenvoudig. Klik op een willekeurige cel in de tabel.

Vervolgens moet er op "Vernieuwen" worden geklikt.

Op de tab Gegevens bevindt zich de groep "Verbindingen" met daarin de knop "Alles vernieuwen". Klik daarop en kies voor Vernieuwen:

De knop Vernieuwen in Excel 2007

Als je de optie om te vragen om een nieuw bestand had ingesteld moet je dat nieuwe bestand nu opgeven .

Zo. Klaar!

Conclusie

Het importeren van tekst bestanden in Excel kan een arbeidsintensief proces zijn, als je dezelfde stappen steeds opnieuw moet doen. Door de importopties die Excel biedt op de juiste manier te gebruiken kan je jezelf echter veel tijd besparen. Tegelijkertijd neemt hierdoor de consistentie van je gegevens toe doordat er geen fouten meer worden gemaakt bij het importeren.

Dit artikel heeft een overzicht gegeven over hoe e.e.a. in Excel kan worden ingericht.

Mocht je vragen en/of opmerkingen hebben, hieronder is de mogelijkheid deze in te vullen.


Vragen, suggesties en opmerkingen

Laatste 8 commentaren van in totaal 42 (Toon ze allemaal):

 


Commentaar van: Philip (14-7-2017 09:19:15) deeplink naar dit commentaar

Weet iemand waarom onderstaande twee manieren om een bestand te openen een verschil opleveren in resultaat m.b.t. kolommen e.d.?

Onderstaande methode opent direct het bestand met verkeerde kolomindeling

strBestand = Application.GetOpenFilename
    Workbooks.Open Filename:=strBestand

Onderstaande methode opent meteen het bestand met alle kolommen goed...

Workbooks.Open Filename:= _
        "K:\BankNL template.csv"


Commentaar van: Jan Karel Pieterse (14-7-2017 16:18:18) deeplink naar dit commentaar

Hoi Philip,

Dat heeft te maken met het gegeven dat VBA "Amerikaans" spreekt, maar kennelijk niet altijd :-) Als je de techniek hierboven beschreven gebruikt, dan heb je dit probleem niet.


Commentaar van: Philip (17-7-2017 09:15:35) deeplink naar dit commentaar

Hoi Jan Karel,

Maar hoe kan ik met die methode "variabel" bestand openen? Ik wil elke dag een ander bestand openen waarbij de naam telkens veranderd, dat zie ik in bovenstaande methode niet terug komen. Daarnaast moeten meerdere mensen gebruik maken van deze macro.

groeten, Philip


Commentaar van: Jan Karel Pieterse (18-7-2017 21:46:32) deeplink naar dit commentaar

Hoi Philip,

Als je op vernieuwen klikt, dan zal Excel om een bestand vragen.


Commentaar van: Marco (25-7-2017 13:17:55) deeplink naar dit commentaar

ik heb txt bestanden met uniforme naamstellingen (alleen maandcode) is anders. voorbeeld "A001-Y17M01.txt" en "A001-Y17M02.txt". nu wil ik deze open met VBA , maar dan in een bestaand werkblad met de naam bij "Y17M01" (nu wordt er steeds een nieuw bestand met nieuw werkblad aangemaakt. Hoe kan ik dit het beste doen ?


Sub inlezen_KB__mnd()
    Sheets("Y17M01").Select
    Range("A1").Activate
    ChDir "L:\Boekhouding\KB_mnd\Y17"
    Workbooks.OpenText Filename:= _
        "L:\Boekhouding\Y17\A001-KB_export_M01_LAY760.txt" _
        , Origin:=65001, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=True, _
        Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
        Array(2, 1), Array(3, 1)), TrailingMinusNumbers:=True
    Columns("A:C").Select
    Columns("A:C").EntireColumn.AutoFit
    Range("A2").Select
End Sub


Commentaar van: Jan Karel Pieterse (13-8-2017 17:12:05) deeplink naar dit commentaar

Hoi Marco,

Is je vraag nog actueel?


Commentaar van: Krijn Boone (30-12-2019 22:08:00) deeplink naar dit commentaar

Geachte,
importeer al jaren de ING csv info in excel om zo mijn financien te beheren.
nu heb ik recent office 365 en kan ik voor het importeren niet meer kiezen vanaf welke regel.dus waar zit dat?
deze regel met kolomnamen is naderhand niet te verwijderen door mij, wat zie ik over het hoofd??
kan 365 wel zo instellen dat het op de "oude" manier gaat maar daar heb ik geen abonnement voor


Commentaar van: Jan Karel Pieterse (6-1-2020 11:48:00) deeplink naar dit commentaar

Hoi Krijn,

ALs je de oude text import wizard gebruikt zou dat nog gewoon moeten werken.


Heeft u vragen, suggesties of opmerkingen? Gebruik dan dit formulier.

Mocht uw vraag niet direct relevant zijn voor deze pagina, maar een algemene Excel vraag betreffen, dan adviseer ik om deze hier te stellen: excelexperts.nl/forum/index.php.




Als u VBA code in uw commentaar plaatst, gebruik dan [VB] tags: [VB]Uw code[/VB].