Dit is er een om je het hoofd over te breken, en je zal niet de eerste zijn die het paard achter de wagen spant en eerst in Excel alle headers gaat aanpassen. Maar dat is toch geen doen elke keer? Hoe importeer je in 1 keer meerdere Excel sheets met verschillende headers en schemas (indeling) naar Alteryx. Met een Batch Macro lukt het. In dit blog leg ik het uit.
Probleemstelling
We willen dus een Excelfile binnenhalen met daarin meerdere sheets, die op hun beurt gevuld zijn met vergelijkbare data. Echter zijn de headers van sheet tot sheet anders en is de volgorde van kolommen niet in elke sheet gelijk. Importeren via een Input Tool icm Dynamic Input werkt hier dus niet goed.
Oplossing
We gaan een Batch Macro maken. Die importeert sheet voor sheet de data, past de headers aan zoals je het graag wilt en plaatst de data onder elkaar in 1 output anker. Je hebt hiervoor wel een klein voorbereidend werkje nodig: Een mapping van de headers zoals ze in die sheets staan aan de gewenste headers.
De Mapping maken
In mijn voorbeeld dataset zitten 4 kolommen: datum, dag, week en omzet. Echter komen ze over 10 sheets ook voor als date, day, tag, woche en sales. Dat probleem lossen we op door middel van deze mapping. Dit kan in een text input, maar ook een aparte Excelfile zijn. Beiden hebben voor en nadelen, dus ik laat het aan de lezer. Ik gebruik in mijn voorbeeld een text input.
Begin in een nieuwe lege workflow.
Ik neem een Text Input, noem de eerste kolom bijvoorbeeld “Old Column Names”, en neem daarin als waarden op alle varieteiten die in mijn dataset voorkomen, dus zowel de gewenste: “datum, dag, week en omzet”, als de ongewenste: “date, day, tag, woche en sales”.
In de tweede kolom, ik noem hem “New Column Names”, geef ik de gewenste kolom naam voor elke waarde in de eerste kolom.
Dit lijstje kan je natuurlijk zo nodig updaten. Indien je de mapping in een Excelfile hebt gemaakt, gebruik dan een Input om de mapping te importeren.
De sheets importeren (de Batch Macro maken)
Sleep een Input Tool naar je canvas en selecteer de betreffende Excelfile. Kies bij “select a sheet” het eerste sheet, of het sheet dat qua schema het meest voldoet aan jouw wensen. Deze vormt het template voor je import.
Sleep een Dynamic Rename (Developer palette) in de flow en verbind die met Input Data (op L) en de Mapping (op R). Selecteer de rename mode “Take Field Names from Right Input Rows”. Configureer als volgt:
Verbind een Macro Output aan de output van de Dynamic Rename. Geef die desgewenst een “Anchor Abreviation”, “O” bijvoorbeeld.
Wat een Batch Macro definieert is het gebruik van een Control Parameter (Interface palette). Sleep die naar het canvas en verbind hem met de Data Input (Q naar bliksem). Er verschijnt vanzelf een Action Tool. Die gaan we configureren. Selecteer boven aan “Update Value (Default)” en daaronder:
File - value="\\map\map\jouwdataset.xlsx|||'sheet1$', vervolgens daaronderaan klik je het vinkje aan, replace a specific string, de string die daar staat is goed.
Nu nog een redelijk belangrijke instelling: open de interface designer (ctrl+alt+d, of via view in de menubalk.) Klik op het tandwieltje (Properties) en selecteer bij output mode voor:
Auto Configure by Name (Wait Until All Iteration Run), daarvoor hebben we immers net die Dynamic Rename ingesteld.
Sla je Macro op en sluit hem af.
De Batch Macro Data Input
Nu gaan we werken aan de flow die je in gedachten had. Wellicht heb je een workflow waarvoor je dit wil toepassen. Pak die nu gerust erbij. Neem een Input Data Tool en selecteer wederom je lastige Excelfile. Nu kies je voor “Import only the list of sheet names” en in het configuratievenster kies je bij rij 5 voor “Full Path”.
Verbind een Formula Tool aan de Input en update de kolom “FileName” met de volgende expressie:
Replace([FileName], "<List of Sheet Names>", [Sheet Names])
Nu verbind je de Macro met de Formula Tool. Configureer de fieldmapping (FileName) en klik “Run”. En als het goed is staat nu alle data netjes onder elkaar, met de juiste headers en in de juiste kolommen.
Alteryx Input Data: Meerdere Excel tabbladen, verschillende schemas en headers. Zo doe je dat. Houd er rekening mee dat de datatypes kunnen afwijken van wat je verwacht, dus wellicht is er nog wat dataprep / cleanse nodig.
Heb je meer hulp of uitleg nodig? Aarzel dan niet om contact met ons op te nemen voor onze workshops en trainingen of huur een consultant in.
Wil je nog meer leren over Alteryx of The Information Lab, check dan ons blog of onze website.