Skip to the content

Excel data conversie adressen

In ons vak hebben we erg veel te maken met het importeren van data uit verschillende bronnen. In heel veel gevallen leveren onze klanten de data aan in een Excel sheet. Om deze data eenvoudig te converteren naar het juiste formaat voor de doeldatabase, maken we gebruik van een zelf ontwikkelde conversietool voor Windows.

Onderaan deze pagina vindt je de download link naar de conversie tool

Update 03-03-2019 : Versie 2019.1.2 staat klaar.In deze versie :

  • Profiel opslaan (zie 10 en 11)
  • Q20191131 : Bij selectie van 1 kolom kan ik de properties niet raadplegen. (opgelost)

Update 11-03-2019 : Versie 2019.1.3 staat klaar. In deze versie :

  • XML export van resultaat (zie 9.1)
  • Diverse issues gefixed

Met deze conversietool is het bijvoorbeeld eenvoudig om een adresregel te splitsen in straatnaam, huisnummer, toevoeging huisnummer, postcode, woonplaats en land. Zo zijn er nog een veel meer functies waarmee de data uit je Excelsheet is te manipuleren:

  • Datum omzetten naar ander formaat (ook eigen maskers zijn mogelijk);
  • Adressen splitsen in voorvoegsel straatnaam, huisnummer, toevoeging huisnummer, postcode, woonplaats, land en GEO coördinaten voor GPS;
  • Tekst omzetten naar kleine letters;
  • Tekst omzetten naar hoofdletters;
  • Eerste woord van de zin met hoofdletter;
  • Woord zoeken op vaste positie in zin;
  • Zoeken en vervangen;
  • Lege cellen vullen met vaste waarde;
  • Reguliere Expressie gebruiken voor zoek en vervang;
  • Autonummering, desgewenst met vaste lengte, prefix en achtervoegsel.

Hoe werkt de conversietool?

1. Openen Excel bestand en prepareren voor conversie

Laad eerst je Excelfile in de conversietool. Dit doe je door te klikken op [Openen] en vervolgens te bladeren naar je Excel bestand.

data conversie openen Excel bestand

 

2. Selecteer de range die je wilt gebruiken voor de conversie

De volgende stap is het selecteren van de data die je wilt gaan gebruiken. Wil je alle data in de spreadsheet gebruiken gebruik dan de sneltoets combinatie CTRL+Home en vervolgens CTRL+Shift+End.

Als je selectie kolomkoppen bevat zet dan de switch [Mijn selectie bevat kolomkoppen] aan.

reinder.eu conversie tool selecteer range

 

3. Klik op converteer om de conversie te beginnen

Als je een range hebt geselecteerd klik je op de button [Converteer]. Met deze actie zal de data uit je selectie worden omgezet naar een tabel waarmee we aan de slag kunnen. Er opent nu een nieuw scherm [Conversie blad].

Aan de linkerzijde verschijnt nu je originele data. Deze zal nooit worden aangepast en kan je ook niet handmatig aanpassen.

 

4. De data converteren

Klik in het scherm [Conversie werkblad] op de button [open properties]. Er zal nu een nieuw scherm openen waar we per veld kunnen gaan bepalen wat er moet gebeuren met de gegevens.

properties data conversie Excel

  • Conversie : Hier bepalen we wat er met de data moet gaan gebeuren (zie de volgende hoofdstukken)
  • Index : Hier zie je de volgorde van de velden te beginnen met 0. Wil je de volgorde aanpassen dan kan je dat straks middels slepen doen in de resultaat grid maar je kan ook handmatig de volgorde aanpassen door de index te wijzigen. Wil je een veld verbergen stel dan de index van het veld in op -1
  • Kolomnaam : De originele naam van het veld. Je kan dit niet aanpassen.
  • Label : Dit is het label dat mee zal worden gegeven naar je resultaat. In de uiteindelijke Excel export zal deze naam worden meegenomen als kolomkop.
  • Veldnaam : Dit veld vul je uitsluitend wanneer je de data gaat exporteren naar XML of JSON. In deze gevallen is dit veld verplicht!

 

5. Velden toevoegen of klonen

Het klonen van een veld kan in een aantal gevallen erg handig zijn als je bijvoorbeeld een bepaald woord uit een zin wilt halen en deze in je resultaat wilt tonen zonder dat de oorspronkelijke kolom wordt aangepast.

Reinder.eu conversie clonen velden

Ook wanneer je een autonummering toe wilt voegen aan je resultaat zou je dit bij voorkeur in een nieuwe kolom toe willen voegen.

Deze acties voer je uit door met je rechtermuisknop te klikken op een kolom en een keuze te maken uit de opties.

  • Kolom toevoegen : Voeg een lege kolom toe aan je resultaat
  • Kolom klonen : Kloon de kolom inclusief het datatype (tekst, datum, numeriek enz)
  • Kolom klonen als string : Kloon de kolom als tekst.
  • Verwijder kolom : Verwijder de kolom in het resultaat.

 

6. Conversie stap toevoegen

Je kan de data in verschillende stappen aanpassen. Deze optie gebruik je bijvoorbeeld om eerst een woord uit een zin te filteren en daarna dat woord met een hoofdletter te laten beginnen. Deze actie gaat in 2 stappen.

Klik op de drie puntjes in het veld [verzameling] om een conversie stap toe te voegen. Hierna zal de editor worden geopend.

conversie stap toevoegen

 

6.1 Actie selecteren

Bepaal nu eerst wat er moet gebeuren met de waarde in het veld. Klik daarvoor op de lijst in het veld [Actie]

Je ziet hier 5 actie soorten waaruit je kan kiezen;

  • Leeg : Maak de waarde van iedere cel leeg
  • Veldwaarde : Geen actie je wilt de oorspronkelijke veldwaarde behouden. (Obsolete : Deze optie heeft geen functie meer en zal in een volgende versie niet langer beschikbaar zijn)
  • Vaste waarde : Vul ieder veld van een kolom met een vaste waarde. Na selectie van deze optie kan je in het veld [Vaste waarde] een waarde invullen.
  • Inlezen : Met deze actie beschik je over verschillende opties om de data te bewerken. (zie ook het hoofdstuk inlezen verderop in de handleiding)
  • Autonummer : Met deze actie kan je een autonummer veld toevoegen aan je resultaat.(zie ook het hoofdstuk autonummering verderop in de handleiding)

 

6.1.1 Autonummering

De conversie optie autonummering geeft je de mogelijkheid om een autonummering toe te voegen aan je Conversie. De autonummering wordt altijd toegepast met respect voor je sorteervolgorde en filter in de Resultaat grid.

Bij het gebruik van de autonummering zijn verschillende opties mogelijk

  • Prefix : Plaats een waarde voor je autonummering. Bijvoorbeeld a1, a2 enz. Laat dit leeg indien je geen waarde toe wilt voegen voor je autonummer.
  • Stap : Geef aan met welke stappen de autonummering moet worden opgehoogd.
  • Startwaarde : Geef aan met welk nummer de autonummering moet beginnen.
  • Vaste lengte : Hier geef je aan hoeveel posities groot de autonummering moet zijn. Het aantal posities zal worden opgevuld met voorloop nullen. Let op de vaste breedte is inclusief de [prefix] en de [Waarde achter nummer]. Als je in onderstaande voorbeeld de [Vaste lengte] instelt op 10 karakters dan zal de eerste waarde er als volgt uitzien a00000001b.
  • Waarde achter nummer : Hier geef je aan welke waarde er achter het nummer moet worden geplaatst. Laat dit leeg indien je geen waarde toe wilt voegen achter je autonummer.

 

6.1.2 Inlezen

Met deze optie kan je de data converteren met een aantal voor ingestelde opties

conversie opties

 

6.1.3 Datum conversie

Middels de datum conversie wordt de oorspronkelijke datum geconverteerd naar de optie die je hebt gekozen.

Bij eigen masker ben je in staat om een eigen datum masker samen te stellen. Dit kan in een gecombineerd formaat maar ook als 1 enkele optie uit onderstaande selectie.

Bijvoorbeeld een datum en tijd omzetten naar een XML datum en tijd doe je met het volgende masker : {6}-{7}-{8}T{9}:{10}

In het veld [Waarde lege velden] voer je het masker in waarbij je een $ dollarteken gebruikt om het masker te scheiden van een lege waarde.

Dus alle velden aanpassen naar XML formaat en indien leeg 2019-01-01T00:00 dan is het masker : {6}-{7}-{8}T{9}:{10}$2019-1-1 00:00

Masker opties :

  • {0} = Dag zonder voorloop nul dus 1-1-2016 wordt 1 en 10-1-2016 wordt 10
  • {1} = Maand zonder voorloop nul dus 1-1-2016 wordt 1 en 1-10-2016 wordt 10
  • {2} = Jaar in 4 tekens: 1-1-2016 wordt 2016
  • {3} = Uur zonder voorloop nul: 1-1-2016 1:01 wordt 1 en 1-1-2016 12:20 wordt 12
  • {4} = Minuut zonder voorloop nul: 1-1-2016 1:01 wordt 1 en 1-1-2016 12:20 wordt 20
  • {5} = seconden zonder voorloop nul: 1-1-2016 1:01:01 wordt 1
  • {6} = Dag met voorloop nul: 1-1-2016 wordt 01 en 10-1-2016 wordt 10
  • {7} = Maand met voorloop nul: 1-1-2016 wordt 01 en 1-10-2016 wordt 10
  • {8} = Jaar in 4 tekens: 1-1-2016 wordt 2016
  • {9} = Uur met voorloop nul: 1:20 wordt 01 en 12:20 wordt 12
  • {10} = minuut met voorloop nul: 10:01 wordt 01 en 12:20 wordt 20
  • {11} = seconden met voorloop nul: 10:10:01 wordt 01 en 12:12:20 wordt 20
  • {12} = Milliseconden: altijd 00 er vindt dus geen conversie plaats
  • {13} = Jaar in laatste 2 cijfers: 2016 wordt 16
  • {14} = Weeknummer (Nederlands) zonder voorloop nul maandag eerste dag van de week
  • {15} = Weeknummer (Nederlands) zonder voorloop nul zondag eerste dag van de week
  • {16} = Weeknummer (Nederlands) met voorloop nul maandag eerste dag van de week
  • {17) = Weeknummer (Nederlands) met voorloop nul zondag eerste dag van de week
  • {18} = Kwartaal zonder voorloop nul
  • {19} = kwartaal met voorloop nul

 

6.1.4 Adres splitsen met GEO informatie in het resultaat (let op voor deze actie is een api_key verplicht)

Met de Optie [Adres splitsen] wordt een adres veld opgesplitst in losse velden. Deze optie is niet gratis en er is een licentie noodzakelijk om de actie uit te kunnen voeren.

Om deze actie te kunnen gebruiken kan je een API_KEY aanvragen. De bundel die hoort bij de API_KEY is onderverdeeld in 2 tarieven;

  • Bundel 1 : tot 5.000 adressen (1 jaar geldig) € 85,-
  • Bundel 2 : tot 100.000 adressen (1 jaar geldig) € 175,-

De ontvangen API_KEY moet worden ingevoerd in de [Applicatie instellingen]. Klik in het hoofdscherm op de optie [Instellingen] en voer de API KEY in. Vergeet niet om je invoer te bevestigen door op [Opslaan] te klikken.

Bij deze optie worden de volgende velden toegevoegd aan het resultaat :

Opmerking : De veldnamen zijn afwijkend om conflicten met je eigen kolomnamen te voorkomen.

  • r_country_code_alpha2 : Landcode volgens ISO Alpha 2
  • r_country : Landnaam
  • r_county : Naam gemeente
  • r_locality : Plaatsnaam
  • r_postal_code : Postcode
  • r_region : Provincie
  • r_street : Straatnaam
  • r_homenumber : Huisnummer
  • r_homenumber_addition : Toevoeging huisnummer
  • r_latitude : Latitude van het adres
  • r_longitude : Longitude van het adres
  • r_validationdescription : Gevonden fouten na splitsen*

* Voor deze actie geldt garbage in = garbage out. Bij een spelfout in de straatnaam is de postcode leidend. In dergelijke gevallen kan het huisnummer niet worden bepaald. Je krijgt in alle gevallen de juiste straatnaam terug die hoort bij de postcode. Met deze informatie kan je in verschillende stappen (originele adres aangevuld met bovenstaande velden) komen tot een gecorrigeerd bestand.

 

6.1.5 Woord uit een zin halen

Met deze optie kan je een woord uit een zin halen dat zich op een vaste positie in een zin bevind. Dus uit iedere kolom het tweede woord of derde woord.

Voer in het veld [Waarde lege velden] de woord index in. Stel je wilt het tweede woord uit een zin ophalen dan gebruik je als masker 2$”waarde lege cell” waarbij “waarde lege cell” aangepast moet worden naar de waarde die je wilt gebruiken als de cel leeg is. Wil je geen vervangende waarde meegeven gebruik dan een lege string “”.

 

6.1.6 Zoek en vervang

Met deze optie zoek je een naar een specifiek woord of deel van een woord en vervangt deze met de meegegeven waarde.

Geef in het veld [Waarde lege velden] de parameters Zoekwoord$Vervangen door mee. Stel je wilt het woord van in de naam (bijvoorbeeld van der Meer) vervangen door Van (hoofdletter V) dan is de zoekstring van$Van

 

6.1.7 Zoek en vervang met reguliere expressie

Met deze optie gebruik je een RegEx (Reguliere Expressie) om in de veldwaarde wijzigingen aan te brengen. (https://docs.microsoft.com/en-us/dotnet/standard/base-types/regular-expression-language-quick-reference)

Lees meer over reguliere expressies op de website van Microsoft.

 

7. Bijwerken resultaat (Profiel editor)

Het resultaat wordt bijgewerkt na het klikken op de button [Toepassen]. Na het klikken op deze button wordt het resultaat geconverteerd naar de laatst ingestelde opties.

 

8. Terug draaien acties (Profiel editor)

Met de button [Ongedaan maken] worden alle acties tussen de laatste aanroep (klik op button) [Toepassen] en nu teruggedraaid. Uitzondering hierop vormt het splitsen van een adres. Het resultaat wordt in dit geval wel leeg gemaakt maar de toegevoegde velden blijven bestaan.

 

9. Exporteren resultaat  (Conversie werkblad)

Met het klikken op de button [Opslaan] zal de file dialog openen. In deze wizard selecteer je de folder en de naam van je resultaat Excel.

Let op alleen gefilterde gegevens worden geëxporteerd. What-you-see-is-what-you-get.

 

9.1 Exporteren resultaat naar XML

XML export is een simpele export van de data naar XML. Middels het groeperen van de gegevens is er een vorm van gelaagde XML mogelijk.

Vergeet niet om bij de properties de waarde veldnaam te vullen met de naam van het xml element voor de kolom. Ook velden met een datum niet vergeten aan te passen.

 

Gelaagde xml bestanden maken

Klik op een willekeurige kolomkop en kies voor [Groeperingspaneel zichtbaar maken]. Er verschijnt nu een extra blok boven de resultaat grid. Sleep hier de kolomkop naar toe waarop je wilt groeperen. Herhaal deze actie bij meerdere groepeerlagen.

xml conversie groeperen

Klik nu op [Export XML] om je output te bewerken of op te slaan.

conversie resultaat xml

10. Opslaan profiel (vanaf versie 2019.1.2)

Als je een Excel bestand meerdere malen wilt converteren of je hebt een terugkerend Excel bestand met dezelfde opmaak dan kan het handig zijn om de instellingen op te slaan.

Middels de button [Opslaan profiel] op het tabblad [Conversie] sla je het profiel op.

11. Openen opgeslagen profiel (vanaf versie 2019.1.2)

Om een eerder opgeslagen profiel opnieuw te gebruiken klik je op de button [Open profiel] en selecteer je het opgeslagen profiel bestand.

Alle instellingen worden nu ingelezen en het Excel bestand wordt automatisch geopend.

12. Download en installeren

Klik op deze link om de applicatie te downloaden. Let op deze applicatie gebruikt het .NET Framework versie 4.7.2.

Wil je automatisch een bericht ontvangen als er een nieuwe versie wordt uitgebracht? Vul dan onderstaande contactformulier in.

 

Meer informatie of een API KEY nodig?

Wil je op de hoogte blijven van updates? Stuur dan ook een contact verzoek middels onderstaande formulier. Je ontvangt dan automatisch een e-mail als er een nieuwe versie beschikbaar is.

 

Adres conversie

Adressen splitsen en controleren.

We hebben een handige tool ontwikkeld waarmee je adressen kunt splitsen of je Excel data kunt converteren voor allerlei doeleinden. Probeer het gratis uit!

Data conversie Excel