Hoe kan een ETL-oplossing helpen om gegevens uit een Excelbestand te extraheren en opnieuw te structureren? In een eerder artikel had ik het al over een methode om de extractie uit een groot aantal Excelbestanden te automatiseren. In het artikel van vandaag ga ik dieper hierop in en leg ik uit hoe ik ternaire operatoren heb gebruikt om de door mij gewenste informatie te extraheren.
Probleemstelling
Het probleem waarmee ik te maken had, komt algemeen voor. U hebt een weinig gestructureerd bestand dat door een gebruiker of een machine is gemaakt en u wilt er bepaalde informatie uit halen. Om welk bestandsformaat het haat, is niet zo belangrijk. Het belangrijkste is dat u een methode hebt waarmee u de informatie kunt extraheren en herstructureren.
Ik kreeg voor het eerst met dit probleem van extractie te maken toen een van mijn klanten mij een reeks Excelbestanden bezorgde. Zoals ik in mijn vorige artikel heb uitgelegd, zou een manuele herindeling wel mogelijk, maar niet duurzaam zijn geweest. Dus ging ik op zoek naar een oplossing met mijn favoriete ETL (Anatella) om mijn doel te bereiken. En het is door het gebruik van een ternaire operator dat de magie ontstond.
Ternaire operator
Simpel gezegd is de ternaire operator het equivalent van de ALS-functie in Excel.
Als u dit artikel leest, weet u waarschijnlijk wel wat dat is. Ik ga u dus alleen herinneren aan de syntaxis van de operator en degenen die meer willen weten verwijs ik door naar externe bronnen.
De ternaire operator wordt als volgt geschreven: als.voorwaarde(waar):(onwaar) en laat toe een van beide argumenten terug te sturen naargelang er aan de voorwaarde die wordt getest is voldaan.
Hoe gegevens extraheren uit bepaalde cellen van een Excelbestand
Vaak zijn er processen waarbij Excel-sjablonen worden gemaakt, verspreid, ingevuld en vervolgens verzameld om de gegevens erin te analyseren. Het proces van data preparation kan dan al snel tot een moeizame taak uitgroeien als er veel Excelsjablonen zijn of als er een overvloed aan informatie uit elk sjabloon moet worden gehaald. Maar er is één belangrijk aspect dat wij in ons voordeel kunnen gebruiken: de informatie wordt altijd in dezelfde cellen van het Excelbestand ingevoerd.
Als ik de gegevens van de cel in de tabel ken, gebruik ik de volgende formule om de juiste gegevens te extraheren
_n == (numéro de la ligne)? numéro de la colonne:
nullS
Met deze formule kan ik bepalen uit welke cellen ik inhoud wil halen. In alle andere gevallen komt er een lege cel terug. Na 3 cellen wordt dit
_n == 75? C5:
_n == 80? C4:
_n == 82? C4:
_nullS
Tegelijkertijd maak ik van de gelegenheid gebruik om een “label”-variabele te definiëren, waarmee ik de inhoud van de cel kan koppelen aan het soort geëxtraheerde informatie.
Omdat de oorspronkelijke structuur van het bestand behouden blijft, heb ik nu 2 kolommen (de inhoud van de cel en het label) en een hele reeks lege rijen. Ik hoef nu alleen nog maar de informatie op een rij te transponeren dankzij de functie “unflatten”.
Laatste kleine moeilijkheid: de datum. De extractie van het veld “datum” gebeurt in het formaat “elapsed time”. Om deze informatie te kunnen gebruiken in een datavisualisatie-software, is het beter dat alles wordt omgezet in een formaat van het type datum/maand/jaar (dd/MM/jjjjj). Excel gebruikt echter een eigen referentiedatum. Als deze laatste valkuil voorbij is, hoeft u het resultaat alleen nog maar op te slaan in een formaat naar keuze.
