Importeren in EcoLIMS (DBF of Excel)

Dit beschrijft het maken van een succesvol importbestand (DBF of Excel).

Om monsters, analyses en waarnemingen in een EcoLIMS administratie te importeren zijn de speciale modules ‘Import mutaties matrix’ en ‘Import mutaties tabel’ te verkrijgen. Met deze modules komen er bij de administraties onder het menu ‘Bewerken’ extra opties bij om bestanden met meetgegevens, die volgens een vaste structuur opgebouwd zijn in te kunnen lezen. Om monsters, analyses en waarnemingen te kunnen exporteren zijn de speciale modules ‘Export mutaties matrix’, ‘Export mutaties tabel’ beschikbaar. Met deze modules kunt u vanuit het Analyses scherm (van eventueel een selectie) een export maken.

De werking van deze modules wordt beschreven in de EcoLIMS handleiding in het hoofdstuk ‘Importeren en exporteren’. Het document dat u nu leest poogt een aantal vragen te beantwoorden die samenhangen bij het importeren en exporteren. Veel EcoLIMS gebruikers besteden werkzaamheden uit aan externe aanleverende partijen. Deze werkzaamheden bestaan simpel gezegd uit het vergaren van meetgegevens en eventueel determineren van de inhoud. Het resultaat van hun werkzaamheden wordt in een bestand aangeboden aan de opdrachtgever, die het vervolgens importeert in EcoLIMS.

Dit gaat echter niet altijd even vlekkeloos. Er blijken vaak verschillen te zitten tussen de stamgegevens in het importbestand en de doeladministratie in EcoLIMS. In het importbestand staat bijvoorbeeld het product (=compartiment) “WAT-OPP” genoemd, maar de gebruiker heeft alleen het product “OPP-WAT”. Dit record kan niet in EcoLIMS worden geïmporteerd voordat deze twee waarden gelijk zijn: hetzij het importbestand dient aangepast te worden, of de stamgegevens in de EcoLIMS administratie. Deze korte handleiding informeert u over de stappen die bij het importeren en exporteren horen, vanuit het perspectief om de gebruiker en externe aanleverende partijen te informeren.

In de toekomst zullen met het verschijnen van de TWN-parameterlijsten en de Aquo-Domus conversie veel van de importproblemen tot het verleden behoren. Daarnaast kunnen aanleverende partijen zelfs direct invoeren op de klant-database met gebruik van de klant-stamgegevens.

Export/import bestand aanmaken

De eerste stap bij het uitbesteden van werkzaamheden die moet resulteren in een importeerbaar bestand met meetgegevens, is het maken van een correct bestand met de juiste velden inclusief correcte veldnamen, veldtypes en veldlengtes. De veldvolgorde is alleen bij Excel-bestanden van toepassing in EcoLIMS versies ouder dan 6.0.

Ga naar Bestand, Analyses en maak via Selecteren, Definieer selectie een selectie op bijvoorbeeld het eerste monster in de dataset. Exporteer dit bestand via de optie Rapporteren, Export mutaties tabel, preferabel in het .DBF formaat. Verdichting en extra gegevens zijn niet noodzakelijk. Uw bestand ziet er uit zoals wordt weergegeven in de paragraaf “Formaat mutaties tabel”, met wellicht kleine wijzigingen indien u een andere dan EcoLIMS versie 6 heeft.

Als dit bestand gereed is, zal het gevuld zijn met de waarnemingen uit één of enkele analyses. Dit geeft de externe partij een idee hoe de data in het bestand moet worden gezet. Deze reeds gevulde records dienen natuurlijk verwijderd te worden bij het vullen van het bestand. Indien gebruik wordt gemaakt van een Excel bestand, dient de eerste regel (die begint met SMP_CODE) niet verwijderd te worden.

Stamgegevens uitwisselen

De tweede stap is het communiceren op welke wijze de gegevens aangeleverd moeten worden. Deze communicatie dient bij voorkeur te worden vastgelegd in de offerte, zodat duidelijk is dat beide partijen zijn overeengekomen op welke wijze het bestand dient te worden aangeleverd. Er zijn een aantal verplichte velden die in een importbestand aanwezig moeten zijn: locaties, producten, monstercode/externe referentie, analysecode, statuscode en parameters. Als deze velden niet aanwezig zijn, kan het bestand niet geïmporteerd worden. Als de velden wel aanwezig zijn, maar de inhoud van de velden komt niet overeen met de stamgegevens in EcoLIMS, kan het bestand ook niet geïmporteerd worden.

Voor alle hierna volgende verplichte velden geldt dat de aanleverende partij niet per definitie alle stamgegevens moet gebruiken van de EcoLIMS gebruiker, maar dat het wel makkelijker is voor het uiteindelijke importeren. Voorbeeld: als de aanleverende partij de analyse FYPL gebruikt bij een fytoplankton analyse en in EcoLIMS wordt F4A gebruikt, dan zal EcoLIMS de analyse dus niet herkennen ook al is het bestand wellicht technisch correct. U heeft twee mogelijkheden: in het importbestand alle FYPL veranderen naar F4A, of de aanleverende partij vóóraf reeds melden dat een fytoplankton analyse de code F4A dient te hebben.

Locaties

Bespreek of er gebruik wordt gemaakt van locaties van de EcoLIMS gebruiker of de aanleverende partij. Indien de locaties van de EcoLIMS gebruiker moeten worden gehanteerd, maak dan een “Export locaties” naar een bestand en lever deze aan de aanleverende partij. Stel bij een import altijd vast of de locatie codes uit het importbestand overeenkomen met de locaties in EcoLIMS. Indien geen gebruik wordt gemaakt van de locatiecode’s van de EcoLIMS gebruiker dan zal EcoLIMS bij de import de melding geven dat locatie XYZ niet bekend is in het systeem en zal het importbestand aangepast moeten worden.

Producten

In de meeste gevallen zullen aanleverende partijen exclusief in oppervlaktewater bemonsteren. Geef in dat geval door welke code uw systeem gebruikt voor het product oppervlaktewater. In de meeste gevallen is dit “10″. Indien meerdere producten gebruikt zullen worden, kan een bestand worden aangemaakt middels Bestand, Producten en vervolgens Rapporten, Producten lijst naar een bestand.

Monstercode / externe referentie

Wij adviseren om altijd te importeren op externe referentie. EcoLIMS zal zelfstandig nieuwe monsternummers aanmaken. Spreek af hoe deze externe referentie eruit zal zien, bijv. “LOCATIENAAM/JJJJMMDD”. Importeren op monster heeft praktische nadelen. Stel dat u zelf gebruik maakt van de monstercode en reeds 1000 monsters in EcoLIMS heeft zitten, dan moet de aanleverende partij bijvoorbeeld 1001 t/m 1050 gebruiken. Als echter vóór de import toch nog monsters worden ingevoerd (bijv 1001 t/m 1009) kan dit resulteren in overlappende monsters. Na importeren op monstercode worden die monsters vervolgens vervangen met de waardes uit het importbestand. Analyses worden aangepast of toegevoegd. Voor meer informatie over wat er gebeurd tijdens een import, zie voorbeeld in handleiding voor EcoLIMS 6.0.

In sommige gevallen is het gewenst dat er gegevens worden toegevoegd. Indien monsters 1001 t/m 1050 door u zijn gevuld met veldwaarnemingen, maar een externe partij levert de fytoplankton gegevens aan, dan is het wenselijk dat deze als aparte analyse in hetzelfde monster verschijnt. Dit kan dan middels het monsternummer en de externe referentie.

Analysecode

Vaak zal een specifiek onderzoek uitgevoerd worden, zoals een fytoplankton onderzoek. Geef door welke analysecode(s) u gebruikt bij fytoplankton onderzoek, bijv. “F4A” voor Fytoplankton aantallen. U kunt ook alle analyse types naar een bestand exporteren door Bestand, Monsters te kiezen, dan Bewerken, Analyse types en vervolgens Rapporteren, Analyse types lijst. De aanleverende partij kan nu precies zien welke code u gebruikt met welke omschrijving incl. parametertype, parametercode en eventueel kenmerk.

Statuscode

Statuscode is een verplicht veld in EcoLIMS. Geef door welke statuscode gewenst is bij de aangeleverde gegevens, bijv. “01″ voor Analyse gepland / in uitvoering. Indien de gegevens een andere status moeten krijgen, kunt ook een lijst met statussen naar een bestand exporteren.

Parameters

Het belangrijkste onderdeel van het importbestand en de EcoLIMS database: wat is er gemeten? Hier zijn vaak verschillen tussen systemen de oorzaak van fouten bij de import. Hopelijk zullen met het verschijnen van de Taxa Waterbeheer Nederland standaard de meeste problemen met verschillende parameters uit de wereld zijn geholpen. Zie onderstaande tabel voor de volgorde waarin mutaties worden geïmporteerd:

Voor EcoLIMS 6.0 geldt aldus: het toevoegen of muteren van parameters uit het importbestand vindt alleen plaats indien er minimaal één exacte overeenkomst is tussen het importbestand en EcoLIMS in de volgorde

1) PAR_NAME (volledige parameter naam)

2) TCN_CODE

3) IAWM_CODE

4) PAR_CODE

EcoLIMS 6.0 is zodanig aangepast dat bij een niet succesvolle match automatisch naar de volgende code wordt gekeken. In voorgaande versies moest een veld of een correcte match hebben, of leeg zijn.

Er wordt eerst gekeken naar een match op parameternaam. Indien deze gevonden wordt in EcoLIMS, dan worden de gegevens uit het importbestand toegevoegd of gewijzigd (in EcoLIMS wordt geregistreerd: de PARCODE, aantal gemeten, opmerking gemeten, indicator gemeten, eenheid gemeten, aantal berekend, eenheid berekend). EcoLIMS controleert bij een succesvolle match op parameternaam niet of de TCN, IAWM of PARCODE overeenkomen met het importbestand. Als er geen match plaatsvindt op parameternaam, zal de TCN-code worden vergeleken en daarna de IAWM-code en tot slot de PAR-code. Als er geen enkele match kan worden gemaakt, zal EcoLIMS de soort niet herkennen en kan het hele bestand niet geïmporteerd worden, totdat dit opgelost is.

Een voorbeeld: in de PAR_NAME, de volledige naam van het organisme, staat in het importbestand een schrijffout van slechts één letter. Bij het importeren wordt deze naam niet herkend, Ecolims gaat door met zoeken in de TCN-code, die er wel juist in staat en dus herkend wordt. Hierdoor wordt een succesvolle match gemaakt en verschijnt de parameter in EcoLIMS met de bekende parametercode. Er wordt niets veranderd aan een eventuele foute parameternaam, of deze nu in EcoLIMS staat of in het importbestand. Dit is anders bij het importeren van parameters, maar dat is niet aan de orde in deze handleiding (zie EcoLIMS handleiding, hoofdstuk parameters).

Indien de TWN-parameterlijst beschikbaar is, is het aan te raden dat zowel de EcoLIMS gebruiker als de aanleverende partij gebruik gaan maken van deze lijsten. Dit is voor de aanleverende partij vaak eenvoudiger omdat zij geen historische gegevens hebben. De EcoLIMS gebruiker moet in bijna alle gevallen eerst haar historische data (laten) converteren naar de nieuwe lijsten.

Formaat mutaties tabel (voor EcoLIMS 6.0)

Het tabel formaat kan in diverse bestandssoorten aangeleverd worden (een .DBF bestand, een .XLS Excel bestand en een .CSV ASCII “fixed length” bestand). Hieronder volgt een beschrijving van de velden in het formaat mutaties tabel. Onder de kolom ‘Verplicht’ staat of dit veld verplicht (*) moet worden ingevuld. Achter dit teken staat een letter die aangeeft voor welke importkeuze dit geldt. De ‘M’ staat voor Monsters, ‘A’ voor Monsteranalyses en de ‘W’ voor Waarnemingen. In de kolom ‘Importveld’ staat de veldnaam, in de kolom ‘Type’ staan veldtype en -lengte van het veld in het mutatiebestand. Onder de kolom ‘EcoLIMS-veld(en)’ staat de verwijzing naar de tabel en het veld waar deze gegevens in moeten komen. SMP_CODE òf EXT_REF moet gevuld zijn.

Dit is een overzichtstabel. De echte importtabel ziet er anders uit, want daarbij staan de velden onder kolom “Import veld” in de verticale kolommen van het importbestand. Het is dus een tabel met 34 kolommen met per record één waarneming. Alle velden waar een * voor staat moeten verplicht ingevuld zijn, de overige velden niet persé. De letters staan voor de veldeigenschappen: C = Character (alfanumeriek), D = Date (datum) en N = Numeric (numeriek). Er mogen dus nooit letters in een numeriek veld staan en het datum formaat is DDMMJJJJ. De logica achter de volgorde van deze import velden is Monstergegevens (SMP_CODE t/m EMP_NR_S), Analysegegevens (ANAL_CODE t/m EMP_NR_A) en Waarneming gegevens (PAR_TYPE t/m EMP_NR_S).

Bestanden openen in Excel

Veel mensen werken met Microsoft Excel om invoer en uitvoer-bestanden te bekijken en om gegevens te wijzigen. Excel is als spreadsheet programma niet ideaal ingericht voor omgang met (dBase) database bestanden. Zo kan het programma ongewild en wellicht ongemerkt wijzigingen aanbrengen in een database bestand waardoor een veldtype of lengte wordt gewijzigd. Wij geven u de volgende tips om met Excel een bestand te maken:

De celeigenschappen van alle cellen dienen op standaard te staan, met uitzondering van datum velden (aangepast, d-m-jjjj), decimale velden (zoals locatie coördinaten 150.442 bij 114.996 zal Excel afronden naar respectievelijk 150 en 115. Dit kan verholpen worden door te kiezen voor Getal waarbij aangevinkt “Scheidingsteken voor duizendtallen (.) gebruiken”. Let ook op meetwaarden met drie decimalen).
De veldlengte wordt bepaald door de kolombreedte in Excel. Het veranderen van deze breedte (bijv door Opmaak, Kolom, AutoAanpassen aan selectie) verhoogt de leesbaarheid maar veranderd de lengte. Het kan dus zo zijn dat voor EcoLIMS de lengte goed ingesteld is terwijl Excel een numerieke waarde afkort naar ####. Dit doet Excel met alle waarden die niet visueel in een vak kunnen worden weergegeven, maar dit betekent niet dat de inhoud van dat veld vier hekjes is. Als u twijfelt kunt u altijd op een cel klikken. De werkelijke waarde van die cel staat dan boven in het scherm. De lengte van een kolom kan worden ingesteld door de kolombreedte in pixels aan te passen (met uw muis klikken op het verticale lijntje tussen twee kolommen, of rechtermuis klikken op een kolomletter en dan kiezen voor “Kolombreedte”). Zo is de pixelbreedte van kolom MPNIDENT 24.
Let op de Windows instellingen van uw werkstation (configuratiescherm, landinstellingen) met betrekking tot scheidingstekens voor duizendtallen en decimalen! Indien deze niet overeenkomen met het invoerbestand zal Excel het bestand niet correct opslaan.
Selecteer bij het opslaan alle velden en kies Bestand, Afdrukbereik, Afdrukbereik wissen. Selecteer hierna alle gevulde velden en kies Bestand, Afdrukbereik, Afdrukbereik bepalen. Nu staat er een streepjeslijn om alle waarden heen die het bereik aangeven waarbinnen Excel alle gegevens zal opslaan in een .DBF.
Bij het opslaan van een Excel bestand altijd kiezen voor Opslaan als. In welke Excel versie u ook werkt: in het dialoogscherm dat verschijnt dient u onderaan bij “Opslaan als” kiezen voor de optie “Microsoft Excel 95/5.0 werkmap (*.xls)”. Excel geeft soms een vage melding bij het afsluiten. Ondanks jaren ervaring is deze melding ons nog steeds een klein raadsel. Kies Ja.
Let op de uitlijning! Alle kolommen dienen LINKS uitgelijnd te worden! U kunt dit eenvoudig bereiken door alle cellen te selecteren met CTRL + A en vervolgens op het icoontje “Links uitlijnen” te klikken. Incorrecte uitlijning kan resulteren in ongewenste importresultaten! Het bestand zal goed worden gecontroleerd, maar u zult in één monster meerdere analyses met hetzelfde product tegenkomen met daarin één waarneming!
Omdat Excel ongemerkt wijzigingen kan opslaan adviseren wij u om altijd met kopieën van bestanden te werken, zodat u altijd terug kunt naar het origineel indien een fout is gemaakt.

Problemen met importeren

Eén van de belangrijkste functies van EcoLIMS is het importeren van gegevens. Hiermee kan een grote hoeveelheid data (bijvoorbeeld van een externe derde partij) op hoge snelheid door de computer worden toegevoegd aan de bestaande database. Maanden werk kan in enkele seconden worden verwerkt. Dit krachtige medium heeft echter ook zijn nadelen. Zo kan met enkele muisklikken een bestand worden geimporteerd op een niet gewenste manier, waarna alle data er handmatig moet worden uitgehaald of moet worden terug gegaan naar een back-up versie van de database, want een import kan niet ongedaan worden gemaakt. Wat sterk van belang is bij het importeren van meetgegevens is het vooraf controleren van de data. Een visuele check kan al veel vertellen over de juistheid van de data, omdat een ervaren persoon onmiddellijk de (on)mogelijkheden in bepaalde velden eruit pikt.

EcoLIMS voert de import in twee stappen uit: eerst wordt er gecontroleerd en daarna geimporteerd. Deze twee functies zijn afzonderlijk van elkaar in het programma gebouwd. De controle functie controleert de data op alle punten en zal niets importeren. De import functie controleert de data op alle punten en zal bij het constateren van één fout ook niet importeren. Pas als de controle geen fouten geeft, kan er data worden geimporteerd.

Indien er een fout in het import-bestand staat, wordt deze geconstateerd met de melding “Import was niet succesvol”.

Het is handig om de foutmelding op het scherm af te beelden.

Wees niet meteen bezorgd indien de foutrapportage 314 pagina’s in beslag neemt, want het kan best zijn dat een bepaalde medewerker niet in de database voorkomt en dat alle 6700 records uit het import bestand voor die medewerker een foutmelding genereren. Houdt hier echter wel rekening mee bij het printen! In EcoLIMS 6.0 is functionaliteit opgenomen waarbij detailmelden standaard uit staan. Dat betekent dat indien bijvoorbeeld voor elk record een bepaalde medewerker niet kan worden gevonden, er slechts één melding wordt gegeven voor de ontbrekende medewerker.

De foutrapportage bestaat uit drie kolommen: het foutnummer, het record en het foutbericht. Probeer te onderscheiden of er een patroon inzit. In onderstaand voorbeeld komt dezelfde fout in alle records voor, maar het kan ook zijn dat meerdere fouten in één record zitten. Hierna volgt een foutrapportage. Bovenin beeld (niet afgebeeld) staat een navigatie tool waarmee door de pagina’s kan worden gebladerd.

In bovenstaand voorbeeld worden 2 foutmeldingen gegenereerd: nummer 320 en 430. Fout 320 heeft te maken met het product dat niet herkend wordt. In dit geval zal het wijzigen van het product WAT-OPP naar bijv. OPP-WAT of 10 ervoor zorgen dat de meting wel kan worden geimporteerd. Deze foutmelding treedt bij elk record op. Fout 430 treedt in dit rapport alleen op bij record 12 (dat zou overeenkomen met rij 12 of 13 als u het bestand bekijkt in Excel. Hier kan EcoLIMS niet bepalen om welke parameter het gaat. Controleer dus of de naam, TCN, IAWM of PAR_CODE overeenkomen tussen het import bestand en de EcoLIMS administratie. In dit geval gaat het om een plant en dient te worden gezocht bij Bestand, Parameters. “PLANT DADAS” is in dit geval parameter type + parameter code. Planten vallen onder Microfyten en zouden correct genomen het parameter type MAFY moeten hebben. Wellicht is dit ook de reden dat EcoLIMS de parameter niet kan vinden? Het bestand wordt niet geimporteerd totdat alle fouten opgelost zijn.

U kunt dus aan de gegeven meldingen reeds afleiden waar de foutmelding ongeveer moet zitten. Een ontbrekende werknemer zal een foutmelding genereren in de tabel Employee.DBF in de SYSTEM directory. Het toevoegen van de bewuste Employee in EcoLIMS lost het probleem op. Echter, indien de werknemer naam in het import-bestand niet correct is, moet dat bestand worden aangepast met Microsoft Visual FoxPro of de File Manager, die is geintegreerd in de AnyBase tool. In deze gevallen is het altijd verstandig om even contact op te nemen met EcoSys indien u niet zeker bent van uw zaak, omdat er direct op de data wordt gewerkt zonder controles buiten de standaard database controles. Wij raden het gebruik van de File Manager in principe af!

In de praktijk blijk het in 80% van de gevallen te gaan om een veldnaam die ontbreekt, niet de juiste naam heeft of van het verkeerde type en/of lengte te zijn. Microsoft Excel kan hierin de boosdoener zijn omdat deze applicatie ongemerkt de veldtypes aanpast. Het is dus zaak om met de functie Modify Table in de File Manager de veldtypes en andere verplichte elementen te controleren aan de hand van de lijst “EcoLIMS mutatie import tabel” die in de EcoLIMS help staat. Indien het importeren niet lukt en EcoLIMS de melding “PF_CHK_FIL/0 Fout: Alias ‘mutatie’ is not found/13″ geeft, is het import bestand hoogstwaarschijnlijk gelocked door een andere applicatie. Database applicaties zijn erg kritisch op “locking” van bestanden files en indien tijdens een import in EcoLIMS hetzelfde bestand tevens is geopend in een andere applicatie (zoals bijvoorbeeld Visual FoxPro, Excel, AnyBase of System) kan het bestand niet worden geïmporteerd.