Jump to content
  • 0

Transponeren van een Excel bestand

martindes

Asked by martindes,

Question

martindes

martindes

Posted
Posted

Beste Filemaker mensen

Ik zit opgescheept met een enorme hersenbreker. Ik heb een voorbeeld bijgevoegd wat de kern van het probleem weergeeft.

Dit Excel bestand moet ik importeren in een database maar het probleem is dat er meerdere waardes in een kolom staan zodat ik die niet per record kan importeren in een database.

Ik heb twee excel bestanden bijgevoegd in een zip bestand Archief.zip. Eén met de opzet zoals ik mijn excel bestand nu krijg en één zoals ik het bestand eigenlijk moet hebben. En ook een Filemaker bestand.

Heeft iemand een oplossing hoe je zo'n excel bestand kan ombouwen naar een bruikbaar bestand?

Of hoe je via een omweg ervoor kan zorgen dat elk record afzonderlijk kan worden toegevoegd in de database.

Vriendelijk dan voor de antwoorden.

Transponeren.fmp12.zip

Archief.zip

Link to comment

22 answers to this question

Recommended Posts

  • 0
martindes

martindes

Posted
  • Author
Posted
8 uur geleden zei hans erik:

Als het een eenmalige importaktie is, kun je het beste de transpose in Excel doen. Als je het elke week of maand moet doen kun je beter een tussentabel in FM inrichten.

Het is nog erger. Het wordt dagelijks gebruik en de bestanden kunnen behoorlijk groot zijn. Tot wel 100 kolommen en 100 rijen.

 

Link to comment
  • 0
Peter Wagemans

Peter Wagemans

Posted
Posted

Een tussentabel is ALTIJD een goed idee. Je wil de data die je importeert immers altijd controleren op fouten die de integriteit van je actieve database data naar de filisteinen kunnen helpen.

Dit laat je ook toe om de ruwe data te importeren in whatever structuur de excel file is, EERST de data wil controleren en misschien al zelfs wat will corrigeren, om dan rustig in een loopje alles weg te schrijven naar je eigenlijke tabel. Beter dan een import, want als je velden bijmaakt kan je ze simpelweg en duidelijk toevoegen in je loop, en als je velden wist, krijg je een verwittiging over het gebruik daarvan in je loop script. Bij de import script stap is dat helemaal niet zo goed uitgewerkt.

Daar dit dagelijks moet gebeuren, ligt het  voor de hand dat je geen manuele voorbereidingen wil doen in de Excel file, maar dat je dit wil automatiseren in een FileMaker script. 

Verder kan je een creëerende relatie gebruiken tussen je buffer tabel en je eigenlijke data, wat toelaat om te updaten of aan te maken in dat ene loopje. En kan je zelfs data wegschrijven vanuit de platte buffer tabel naar meerde tabellen.

Het staat je vrij om deze tussentabel als een externe tabel te definieren in een ander bestand, zodat je hoofdbestand niet constant belaagd wordt door grote data operaties die de backups ook nutteloos opblazen.

Nadeel is dat alles in 2 keer gebeurd, dus het pakt een stuk langer. Daar dit dagelijks moet gebeuren kan dit eventueel gedeeltelijk of zelfs geheel door server side scripts gedaan worden. Dat maakt alles sneller en er zijn ook geen wachttijden meer op de client. Maar dat vergt wel wat extra denkwerk en scripting.

Link to comment
  • 0
Peter Wagemans

Peter Wagemans

Posted
Posted

3 minuten later bedenk ik nog: Bij een direct update-import in een record dat gelockt is door een andere gebruiker, zit je zwaar in de problemen. Je kan er immers niet achter komen wel record er niet geimporteerd is. Dat probleem is veel kleiner als je een buffertabel en een loop script gebruikt. Je moet hier nog altijd iets mee doen, maar je weet tenminste al welk record problemen geeft.

Link to comment
  • 0
hans erik

hans erik

Posted
Posted (edited)
On 27 september 2018 at 7:02 AM, martindes said:

Ik heb een afbeelding toegevoegd wat ik bedoel.

Je voorbeeld is duidelijk en het antwoord van Banach ook, maar...

Je gaf eerder aan :

Quote

Het is nog erger. Het wordt dagelijks gebruik en de bestanden kunnen behoorlijk groot zijn. Tot wel 100 kolommen en 100 rijen.

Als dat betekent dat het aantal kolommen van dag tot dag verschilt, heb je nog wel een extra probleem op te lossen. Je kunt het 'omzetten script' voorbereiden op 100 kolommen. Dan moet je de Listfunctie uitbreiden of vervangen door een berekend veld in je import tabel, en je moet de loop langer laten lopen.

Maar dan moeten kolommen van dag tot dag wel dezelfde betekenis cq vergelijkbare inhoud hebben.

Edited by hans erik
Link to comment
  • 0
martindes

martindes

Posted
  • Author
Posted

Dit is inderdaad ongeveer wat ik bedoel. Het enige wat ik niet snap is het stoppen van de loop.

Want nu staat deze ingesteld op een vast getal. Echter, de excel bestanden verschillen per bestand. Ik heb voor de eenvoud een bestand met 4 kolommen gebruikt maar het kunnen er net zo goed 50, 58, 34, 99 of meer dan 100 zijn.

Ik zou graag het script daarom wat beter snappen zodat ik het zelf kan aanpassen naar behoefte.

Met groet en dank voor de antwoorden.

Link to comment
  • 0
Banach

Banach

Posted
Posted (edited)

Je zult, zoals hans erik aangeeft, de list moeten uitbreiden naar het maximale aantal kolommen. De loop kun je b.v. stoppen wanneer de waarde in de list leeg is.

In het bijgevoegde voorbeeld is een nieuwe variabele in het script ingevoerd waarin je het maximale aantal kolommen kunt zetten (hier 4). De list wordt nu via een loop gevuld.

omzetten_2.fmp12

Edited by Banach
Link to comment
  • 0
hans erik

hans erik

Posted
Posted

Ik denk dat je allereerst moet uitzoeken wat je precies aangeleverd krijgt.

Stel, de ene dag een Excel bestand met 4 kolommen, met namen col_1 tm col_4. De volgende dag een Excel bestand met 7 kolommen, col_1 tm col_7. Bevat col_3 dan in beide gevallen dezelfde soort gegevens? Zo niet, dan heb je een probleem, want dan vul je je database telkens met verschillende sets. 

En stel dat col_3 wel altijd hetzelfde soort gegeven bevat, maar niet altijd wordt aangeleverd. Dan wordt je script ook wel iets ingewikkelder, lijkt me.

Link to comment
  • 0
martindes

martindes

Posted
  • Author
Posted
11 minuten geleden zei hans erik:

Ik denk dat je allereerst moet uitzoeken wat je precies aangeleverd krijgt.

Stel, de ene dag een Excel bestand met 4 kolommen, met namen col_1 tm col_4. De volgende dag een Excel bestand met 7 kolommen, col_1 tm col_7. Bevat col_3 dan in beide gevallen dezelfde soort gegevens? Zo niet, dan heb je een probleem, want dan vul je je database telkens met verschillende sets. 

En stel dat col_3 wel altijd hetzelfde soort gegeven bevat, maar niet altijd wordt aangeleverd. Dan wordt je script ook wel iets ingewikkelder, lijkt me.

Voor de eerste kolommen heb ik al een oplossing. Het kan nl. inderdaad zo zijn dat er een aantal kolommen uit de eerste rijen als spitsvelden worden gebruikt. Bijvoorbeeld kolom 1 voor de leeftijd en kolom twee voor het geslacht. Daar heb ik reeds een oplossing voor. Ik gebruik dan een list en een variabele voor die kolommen. Dat kan ik ook inbouwen in een knop door de gebruiker te laten kiezen voor het aantal kolommen dat als spitsveld gebruikt moet worden.

Bij dat script gebruik ik de list functie en ik gooi na de import in de tabel "in" dat record weg. Enzovoort voor het tweede record wat het tweede splitsveld heeft. Dat werkt prima.

Ik wil alleen even afvangen hoe ik de loop kan laten stoppen bij de laatste regel in de list.

Link to comment
  • 0
martindes

martindes

Posted
  • Author
Posted (edited)
9 minuten geleden zei Banach:

Kortom: Dit is niet te automatiseren?

Niet helemaal. Wat ze nu doen is het handmatig transponeren in Excel met een script wat kolom voor kolom omzet. Bijzonder tijdrovend en foutgevoelig.

Het excel bestand bestaat meestal vanaf links uit één of meerdere kolommen die als splits (sorteer) velden dienen. Dan een tig aantal kolommen met de data en dan vaak nog een aantal kolommen met data die niet altijd gebruikt worden.

Ik denk dat ik het af kan vangen met een scriptparameter in de knop waarbij ik de gebruiker laat kiezen hoeveel kolommen vanaf links gebruikt worden als splitsvelden.

Edited by martindes
Link to comment
  • 0
hans erik

hans erik

Posted
Posted
On 1 oktober 2018 at 6:55 PM, Banach said:

Kortom: Dit is niet te automatiseren?

Jawel, mits in de eerste regel van het Excelbestand wordt aangegeven wat voor gegeven de kolom bevat. Anders heb je geen aanknopingspunt. Maar dan kom je er handmatig ook niet uit denk ik!

Als elke kolomkop een string bevat die de betekenis van de gegevens aanduidt, kun je met een slim script alles vanuit de tussentabel verdelen: naar aparte rijen in 1 tabel, naar meerdere tabellen enz  enz. , eigenlijk zoals je in het voorbeeld al liet zien maar dan met wat meer voorwaarden.

Link to comment
  • 0
menno

menno

Posted
Posted

Het valt prima te automatiseren, mits je bestand in bepaalde opzichten maar altijd aan dezelfde voorwaarden voldoet. Je kan in een excel immers gemakkelijk kolommen toevoegen en verschuiven, dus dat lijkt me geen probleem.

In het bijgaande voorbeeld heb ik jouw laatste voorbeeldbestand gebruikt. Uitgangspunten zijn in dit geval dat er

  1. altijd numerieke data wordt geleverd ...  anders moet je de zaak even aanpassen.
  2. kolom1 het id van de persoon bevat
  3. kolommen 2 en 3 geen relevante informatie bevatten

image.thumb.png.bdb3f5cc3358c9588e24eeff0e1bd916.png

De niet relevante kolommen worden wél geïmporteerd, maar voor het verzamelen van de resultaten zijn ze niet van belang, vandaar irrelevant.

Als je nu op de knop "Importeer Excel" klikt, kies je daarna het te importeren bestand en dat wordt meteen verwerkt. Het maakt niet uit of je nu 1 vraagkolom hebt of 200 (dat is het voorlopige maximum) het wordt verwerkt en er worden alleen records geschreven in het resultaat wanneer een vraag is beantwoord.

Excel_Import.zip

Link to comment
  • 0
martindes

martindes

Posted
  • Author
Posted

Het  valt inderdaad wel te automatiseren zoals het er nu uit ziet. Ik moet er alleen voor zorgen dat de dat consistent is en dat in de eerste kolommen altijd de relevante sorteervelden zitten.

Ik zou dan met een keuzeknop het aantal kolommen kunnen aangeven die als sorteerveld meegenomen moeten worden.

Link to comment

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
Go to question listing
×
×
  • Create New...