Most Valuable Professional


View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Subscribe to our mailing list

* indicates required

Audit !!!

Probeer onze RefTreeAnalyser
de beste Excel formule auditing tool.

Cursussen

Excel VBA Masterclass (Engels)
Excel VBA voor Financials

Third party tools

Speed up your file

FastExcel
The best tool to optimise your Excel model!

Repair your file

Stellar Phoenix Excel Repair
Best tool to repair corrupt Excel sheets and objects
Home > Nederlandse site > Artikelen > Text Importeren
This page in English

Tekst bestanden importeren in Excel werkbladen

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

Dit artikel is ook gepubliceerd op één van de Microsoft® blogs: Importing Text Files in an Excel Sheet.

Inleiding

Als je ooit geprobeerd hebt om een tekstbestand te openen in Excel, 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?

Ik laat het hieronder zien..

Instellen welk (soort) bestand geïmporteerd moet worden

Selecteer eerst het werkblad waar je de gegevens in wilt ontvangen.

De hierna volgende stappen verschillen naar gelang uw Excel versie.

Excel 2013/2010/2007

Zoek op het lint de tab Gegevens op en klik vervolgens op de dropdown  "Externe gegevens ophalen" en tenslotte op de knop "Van tekst".

 De "Van tekst" knop op de Gegevens tab van het lint

De "Van tekst" knop op de Gegevens tab van het lint

Selecteer het juiste bestand uit het volgende dialoogvenster:

Het dialoogvenster Tekstbestand importeren

Het dialoogvenster Tekstbestand importeren.

Klik Importeren. (lees verder na de sectie over Excel 2003 en ouder)

Excel 2003 en ouder

Kies uit het menu Data, Externe gegevens importeren, Gegevens importeren... Het volgende dialoogvenster wordt  geopend:

Het dialoogvenster Gegevensbron selecteren in Excel 2003

Het dialoogvenster Gegevensbron selecteren in Excel 2003.

Selecteer onderaan de optie "Tekstbestanden" en zoek vervolgens het juiste bestand op:

Venster Gegevensbron selecteren, een bestand selecteren

Venster Gegevensbron selecteren, een bestand selecteren

Alle versies: De import instellingen definiëren

Tot nu toe hebben we alleen maar opgegeven wat we willen importeren. Nu is het tijd om aan te geven hoe dit moet gebeuren. Deze sectie geldt voor alle Excel versies, in ieder geval terug tot Excel XP, maar waarschijnlijk ook daarvoor. De schermafbeeldingen die je hier ziet kunnen daarom licht afwijken van wat je zelf ziet, maar het principe is gelijk.

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 aanvinkt, 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. Het hangt af van je Excel versie waar deze zich bevindt:

Excel 2013/2010/2007

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

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.

Excel 2003 en ouder

In Excel 2003, is de optie om te vernieuwen onder andere op twee plaatsen te vinden:

1. Op het Data menu (maar alleen indien je een cel in een geïmporteerde tabel hebt geselecteerd):

De knop Gegevens vernieuwen in het Data menu

De knop Gegevens vernieuwen in het Data menu

2. Op de werkbalk Externe gegevens:

De knop Gegevens vernieuwen op de werkbalk Externe gegevens

De knop Gegevens vernieuwen op de werkbalk Externe gegevens

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 38 (Toon ze allemaal):

 


Comment by: Sander Veldkamp (8/1/2016 1:12:16 PM)

Is het ook mogelijk om van een csv bestand ook elke Nde rij in een aparte kolom te plaatsen ? Dus bijvoorbeeld rij 1,4,7,10,13, etc. in kolom 2 en rij 2,5,8,11, etc in kolom 3 en rij 3,6,9,12,etc. in kolom 3 ?

 


Comment by: Jan Karel Pieterse (8/3/2016 11:42:51 AM)

Hoi Sander,

Met deze functionaliteit gaat dit niet eenvoudig. Je zou eens naar POwerQuery (Get and Transform) moeten kijken, daar gaat het wel mee denk ik.
Of een VBA macrootje maken die dit doet.

 


Comment by: daan (6/22/2017 11:55:25 AM)

Office 365 heeft excel verandert. Als txt document is een file van bijv. behr.nl niet meer op te slaan als opslaan als maar het wordt bijv. 170620 NN Basic_Mat_php en is dan in excel als CSV bestand niet meer te importeren. Hoe moet dat??? Bij een download van bijv. ING krijgt men als datum bijv. 20170620 hoe maakt men hier bijvoorbeeld 20-06-2017 van

 


Comment by: Jan Karel Pieterse (6/22/2017 2:01:30 PM)

Hoi Daan,

Ik begrijp de vraag even niet, kan je deze in kleinere deelproblemen opdelen en uitleggen?

 


Comment by: Philip (7/14/2017 9:19:15 AM)

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"

 


Comment by: Jan Karel Pieterse (7/14/2017 4:18:18 PM)

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.

 


Comment by: Philip (7/17/2017 9:15:35 AM)

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

 


Comment by: Jan Karel Pieterse (7/18/2017 9:46:32 PM)

Hoi Philip,

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

 


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: www.eileenslounge.com.

Uw naam (verplicht veld):

Uw e-mail adres (Niet verplicht, dit adres wordt niet getoond)

Uw verzoek of commentaar:

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