10 Replies Latest reply on Apr 16, 2017 7:55 AM by BruceRobertson

    scripting import from xlsx to several fm tables

    cortiel

      i have an xlsx file i need to import into fm tables

      the spreadsheet contains names addresses ---> contacts table

      payments for each year  ----> donations tabel

      etc,

       

      I want to create a script

       

      go to layout table (a straint excel import)

      go to record first

       

      loop

            set variable $firstname = table:: firstname

           .....

          insert into contacts where firstname&lastname&address = $firstname&$lastname....

       

          go to record next exit after last

      end loop

       

       

       

      how do I accomplish this insert. I other languages I would use sql selct to find a record and than updat/insert the data

       

      Thanks

       

      Peter

        • 1. Re: scripting import from xlsx to several fm tables
          philmodjunk

          It is not fully clear what you are trying to do.

           

          Telling us how the data is structured in your xlsx file and how the rows/fields of your FileMaker tables are set up would also be helpful.

           

          Is your import supposed to update records that already exist in FileMaker if the names match to the data imported from FileMaker?

           

          In simple terms, You can import your data twice, once into contacts and once into donations mapping different fields to columns for each import.

           

          also, an "Update matching records of found set" option can be used to match data by matching the two name columns in your xlsx file to the name fields of a table.

           

          It is also sometimes necessary to import data into a table designed just to receive the imported data--a staging table as it is sometimes called, and then a script moves the data into the proper tables in your solution. This last option is used when the row/column structure of your xlsx data isn't a workable match to the records/fields of your tables--such as when n columns of data needs to be imported into n different records.

          • 2. Re: scripting import from xlsx to several fm tables
            cortiel

            ok i imported the xlsx file into a fm staging table called imptable

             

            from there i need to move them into fm table after I clear the fm tables

             

            import table :

             

            firstname     lastname     address     city....           Y1998     Y1999     y2000 ...

            Peter          Boyle            123 Main   meriden      12.55        75.00       59.00

             

            table contacts:

            id      firstname     lastname     address etc

             

            table donations:

            id     contactsid     checkdate     checkamount

             

             

             

            a person can have one or more rows (multiple donations) int the imptable

             

            the import routine :

            zap contacts

            zap donation

             

            open imptable

            scan though it till EOF

            see if donor is existing

            if yes insert donation into donations table

            if no insert contact into contacts table and donations into donations table

             

            Y1998 would be a donation with a 12/31/1998 date

            Y1999 would be a dontaion with a 12/31/1999 data

             

            current year donation might have multiple rows with  the same donor in xls

             

            prior year donation are rolled into one row in the xls file (imported into imptable iFM

             

            my question is

             

            as I loop throu imptable

            what steps do I have to create to search for a donor and then update/insert it depending if found or not

            and inserting multiple donations for that donor from the yearly fields Y1998,Y1999 etc.

             

            without loosing my record pointer in the import file.

             

            I hope this makes it clear

            • 3. Re: scripting import from xlsx to several fm tables
              philmodjunk

              the import routine :

              zap contacts

              zap donation

              "zap" means that you delete all records? Why?

               

              Such a step would preclude the need to find anything here as each record in contact would be a new record and your import can simply create the new contact record and a relationship can then be used to create the donation records without any searching at all.

               

              But I don't see why you'd clear all records from the two tables first.... so maybe I misunderstand the jargon used?

              • 4. Re: scripting import from xlsx to several fm tables
                PeterCortiel

                I am talking to  start out with clean data. I might have changed some during development

                 

                This is my import staging table

                 

                And here are the real fm tables:

                I want to be able to rerun the import at any given time always starting from fresh

                 

                How would that be accomplished in fm

                 

                 

                Here is wha ti have sofar:

                • 5. Re: scripting import from xlsx to several fm tables
                  philmodjunk

                  I am talking to  start out with clean data. I might have changed some during development

                   

                  So in actual day to day operation, you won't do that? And the donations may be for existing contacts or a new contact?

                   

                  The biggest potential failure point in your data are the names. Should you get two donors with exactly the same first and last name, how will you tell them apart? What happens if a donor changes their name? Much depends on how the software that produces the xlsx file manages those issues. If it added a column to your data that served as a unique identifier that is not strictly dependent on name, it would make for better data management.

                   

                  Assuming that you always have unique, unchanging names for the moment, here's a general outline of how you can do this. I am assuming that all donations are new, but that some donors may be new or may already be in your table.

                   

                  Starting from a layout based on contacts:

                  Show all records to that all contacts are in the found set.

                  Do a "Import matching records" import with the option to add records that don't match as new contacts directly into your contacts file. Select the first and last name columns as match fields to the first and last name fields in your contacts table. The other contact fields, such as those for the address will be updated by this import for existing donors.

                   

                  Now:
                  Purge all records from your staging table.

                  Import records a second time, but into the staging table and include the first row of as data to be imported so that you can get the year info out of the column headers for the donation columns.

                   

                  Set up these relationships to support your import process:

                  (These would not be the only relationships in your solution. These are just the ones needed for the import process that I am describing.)

                   

                  Contatcts-----<ImportTable------<Donations|MagicKey

                  ImportTable::FirstName = Contacts::FirstName AND

                  ImportTable::LastName = Contacts::LastName

                   

                  ImportTable::gMagicKey = Donation|MagicKey::__pkDonationID

                   

                  gMagicKey is a global field of the same data type as __pkDonationID. "Allow Creation..." is enabled for Donation|MagicKey and __pkDonationID is either a number field that auto-enters a serial number or a text field that auto-enters Get ( UUID ).

                   

                  I don't know the field names (some of the images in your previous post are "broken" for some reason at the moment.) for the donation columns in the importTable so I am going to name them Donation 1, Donation 2, Donation 3....

                  Your script would then work like this:

                  #Capture the donation dates (years actually) in a variable array

                  Go to record/request/page [first]
                  Loop

                     Set Variable [$K ; value: $K + 1 ]

                     Exit Loop If [ $K > The number of donation columns in your table go here ]

                     Set Variable [$Years [$K] ; Value: GetField ( "ImportTable::Donation " & $K ) ]
                  End Loop

                  Go to record/Request/Page [Next]

                  Loop

                     Set Variable [$K ; value: 0 ]

                     Loop

                         Set Variable [$K ; value: $K + 1 ]

                         Exit Loop If [ $K > The number of donation columns in your table go here ]

                         Set Field [ImportTable::gMagicKey ; "" //clear the magic key field so that following steps create a new record ]

                         if [ not isempty ( GetField ( "ImportTable::Donation " & $K ) //check to see if there's a donation in the cell ]

                            Set Field [Donation|MagicKey::Amount ; GetField ( "ImportTable::Donation " & $K ) //this step creates a new record ]

                            Set Field [ Donation|MagicKey::Year ; $Years[$K] ]

                            Set Field [ Donation|MagicKey::_fkDonorID ; Contacts::__pkContactID //this links the new donation record to the correct contact ]

                        End IF
                     End Loop

                     Go to Record/Request/Page [next ; exit after last]

                  End Loop

                   

                  The system loops through the records and loops through the donation columns to get data and move it into individual donation records.

                   

                  "MagicKey" is a term that you can web search as there are some excellent blog articles on the concept if you want to learn more.

                   

                  Note that I do not at anytime perform a find, but rather use relationships to create and link records.

                  • 6. Re: scripting import from xlsx to several fm tables
                    beverly

                    import staging

                    That is my most used method. Data frequently needs to be massaged before real "import".

                    beverly

                    • 7. Re: scripting import from xlsx to several fm tables
                      PeterCortiel

                      Thanks for your explanations.

                      Magickeys is somewhat esoteric for someone coming from a SQL background.

                       

                      I did manage to get my data into my application correctly. I did it the old way

                      Import into a staging table (simple)

                      Dividing city-state-zip column into city, state, zip fields

                      Then I use this staging table and loop through it and create the donors records

                      Grab the PK

                      And create all donation records with then donors PK as FK

                      It works fine.

                       

                      Now i need to summarize the donations by deposit date into the deposit table

                      Something like

                      Insert into deposits (select  depdate,sum(net amount) from deposits group by depdate)

                       

                      How can i accomplish this in filemaker?

                       

                      PS: I will research this magic key thing, it sounds intriguing but but quite illogical at first.

                      How came up with this?

                       

                      Peter

                      • 8. Re: scripting import from xlsx to several fm tables
                        BruceRobertson

                        It is a simple and straightforward consequence of understanding how the "allow creation of related records" feature operates.

                        • 9. Re: scripting import from xlsx to several fm tables
                          PeterCortiel

                          Yes, once one understands filemaker logic :-)

                           

                          But here I have

                          table donations :

                          Pk

                          Donors_fk

                          Deposits_fk

                          Netamount

                          Deposit_date

                           

                          Table deposits:

                          PK

                          Deposit_amount

                          Deposit_date

                           

                          I need to sum up sum(donations) by Deposit_date and create a record in deposits at the same time pushing the deposits PK into donations deposits_fk

                           

                          My script creates only the deposits PK

                          How should I do this and why do I need to have  a relationship to an empty table ???

                          • 10. Re: scripting import from xlsx to several fm tables
                            BruceRobertson
                            How should I do this and why do I need to have  a relationship to an empty table ???

                            Do what?

                            What relationship? You have not shown any relationships.

                            You mention the concept of logic but there seems to be no connection.

                             

                            scriptErrors.png