Transponeren van een Excel bestand

[martindes]

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

[andries]

de data zal jammer genoeg bewerkt moeten worden. Ofwel doe je dat in Excel, ofwel voorzie je een tabel die exact dezelfde kolommen heeft dan het Excel bestand, importeer je in die tabel en met een script ga je dan de data naar de juiste records schrijven...

[Banach]

Ligt het aan mij of is er echt geen verschil tussen de beide bestanden in Archief.zip??

[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.

[martindes]

13 uur geleden zei Banach:

Ligt het aan mij of is er echt geen verschil tussen de beide bestanden in Archief.zip??

Ja klopt, dat zag ik gisteren ook. Ik heb hierbij een bestand bijgevoegd met het probleem.Transponeren_Fout.zip

[martindes]

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.

 

[martindes]

Ik heb bestand aangepast zodat het misschien iets duidelijker is.

Er zitten in het Excel bestand kolommen met verschillende bronvelden. En meerdere ID velden zoals de _ID_Vraag in dit geval.

Ik zit zelf ook te denken aan een tussentabel en een list in die tussentabel om daarmee het probleem op te lossen.

Elke idee is welkom. Dank bij voorbaat.Archief.zip

[Banach]

Ik krijg maar niet helder wat je wilt. Daarom de volgende vragen:

- Wat krijg je aangeleverd?

- Hoe moet dat er na conversie uitzien?

Laat dat eens zien voor 1 enkel record.

[Peter Wagemans]

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.

[Peter Wagemans]

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.

[martindes]

Op 22/09/2018 om 16:25 zei Banach:

Ik krijg maar niet helder wat je wilt. Daarom de volgende vragen:

- Wat krijg je aangeleverd?

- Hoe moet dat er na conversie uitzien?

Laat dat eens zien voor 1 enkel record.

Ik heb een afbeelding toegevoegd wat ik bedoel.

[Banach]

Dit kan met een eenvoudig scriptje. Zie voorbeeld.

omzetten.fmp12

[hans erik]

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.

1 oktober 2018 aangepast door hans erik

[martindes]

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.

[Banach]

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

1 oktober 2018 aangepast door Banach

[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.

[Banach]

Kortom: Dit is niet te automatiseren?

[martindes]

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.

[martindes]

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.

1 oktober 2018 aangepast door martindes

[hans erik]

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.

[menno]

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

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

[martindes]

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.

[Marsau]

Even uit de losse pols: een andere mogelijkheid is gebruik van een plug-in, zoals Scribe of MBS, om de aangeleverde xls te verwerken. 

Voordeel is dat je per regel alle businesslogica zou kunnen scripten.