13 Replies Latest reply on Dec 16, 2011 6:00 AM by TraceyLillis

    Excel import into 2 tables

    TraceyLillis

      Title

      Excel import into 2 tables

      Post

      I have researched the help files, online and still not getting how to do an import of data from Excel into 2 related tables.  My scripting experience is quite limited. 

      Here is my situation (using FM Pro 11 Advanced).......

      My DB has 2 tables.  Contacts and Checklists.  Relationship exists between them using a unique number.  A layout has been created ('Layout C') which contains contact information (from Table 'Contacts') and then 30 fields from another table 'Checklists'.  There is already existing records in this layout, however I also have a spreadsheet containing more than 1000 records on these contacts which I need to import. 

      Because there is already existing data, I need to copy the data over from the spreadsheet and put this data into the existing 'checklist' table, not just have a relationship created.  I have seen some solutions mentioned using a temporary table and copying the data accross, however there was not details on scipting steps for this process.  If someone could help with this (in a step by step process please), it would be greatly appreciated.

      TIA

      Tracey

       

        • 1. Re: Excel import into 2 tables
          philmodjunk

          You have two tables into which you want to import data, but data in each table both come from the same excel spreadsheet?

          How is the data organized in this spreadsheet? Is the contact info repeated over and over again for each checklist entry?

          In general, to import data into two tables requires importing the data twice, once into each table. In FileMaker, this requires importing the data for contacts into a layout based on contacts and then to import checklist entries, you do a second import records, but from a layout based on checklist instead of contacts.

          There will be issues with this process that you'll need to resolve depending on the exact structure of the data in your source (Excel) file to make sure that you don't import duplicate contact data.

          • 2. Re: Excel import into 2 tables
            TraceyLillis

            Hi Phil,

            The spreadsheet contains the unique number for each contact (eg student number), name etc and then has fields for the checklist.  So there are over 1000 students.  So no, the contact info is not repeated for each checklist entry as it is 1 record for each student. 

            I tried importing the spreadsheet into the Layout C (based on the Contact Table, with TOC of the Checklist Table), however it is greyed out and won't let me import the data for some reason.  I do have a relationship between the contact id field on the TOC for Contacts/Checklists and it works, otherwise I wouldn't be able to see any checklist data on my Layout C. 

            I imported the data into the Checklist table, however it is not appearing in my Layout C.  I am wondering if this is because I have already imported the students contact information only into the Layout C and it isn't matching the student numbers up between the checklist table and the contacts table?

            Any suggestions please?  Hopefully I have made sense.

            Tracey

            • 3. Re: Excel import into 2 tables
              philmodjunk

              How is the check list data formatted? each check list item in it's own column or something else? Do you have any say in how the data in this spreadsheet is formatted?

              Whenyou import from a spreadsheet into FileMaker, each row of the spreadsheet is imported into a different record. Thus, you can easily import from this spreadsheet into your contacts table, but not into the checklist table. Instead, as you have read elsewhere, you'll need to import this data into a separate "temp" table and then use a script to move the data from the temp table into the check list table.

              I tried importing the spreadsheet into the Layout C (based on the Contact Table, with TOC of the Checklist Table), however it is greyed out and won't let me import the data

              That suggests that Layout C is not based on the Contact Table. Enter layout mode and select layout setup... What do you see listed in the "show records from" drop down?

              I am also assuming that for a given student, each item in their check list is a separate related record. Let me know if this is not the case.

              • 4. Re: Excel import into 2 tables
                TraceyLillis

                The spreadsheet data is formatted the same as the database.  I made sure the date fields were the same format as the database. 

                Each item does have it's own column.  E.g  date signed, expiry date etc.

                Example

                (columns)     A           B                C                      D

                (content)     Name    student no.    date signed     Expiry date  and so on for another 30 or more columns. 

                 

                When I look at the layout setup, it is showing records from TO5_contacts. 

                You said to use a script to move the data from the temp table into the checklist table.  Could you direct me to a script I could use please? 

                Thankyou so much for your help.

                • 5. Re: Excel import into 2 tables
                  philmodjunk

                  While it's not a structure I'd use, if you have separate fields in the check list table for each checklist column in the spreadsheet, you do not need a temp table.

                  Go to a layout based on the Checklist table, not contacts. Import the data from your spreadsheet there, making sure to map the appropriate columns to the appropriate fields--make sure your student ID number is imported so that you can link each check list record to it's associated contact record. This should poplulate the CheckList table with records that match to the data you've already imported into Contacts from the same file.

                  BTW, what you describe would appear to be a one to one relationship between Contacts and CheckList. If this is the case, why do you need two separate tables?

                  • 6. Re: Excel import into 2 tables
                    TraceyLillis

                    I have created two separate tables due to the pure size of the checklist.  There is already enough fields in the Contacts table.  This is why there is a separate table.  Also, in future when someone needs to modify the checklist, it is handy in one table already.

                     

                    I have already tried the importation of the data into the checklist layout.  It is not populating Layout C with the data.  This is the problem. 

                    • 7. Re: Excel import into 2 tables
                      philmodjunk

                      Layout C must list your check list table in layout setup | Show Records From or you cannot use it to import data into the check list table.

                      • 8. Re: Excel import into 2 tables
                        TraceyLillis

                        Okay now I am confused.  I have changed the layout setup to show the checklist, changed the individula data fields to refer to the TOC of the relationship.  Now it will not display contact information such as name, address etc unless I create new fields in the Checklist table.  This defeats the purpose of having a relationship as it will all be in 1 table. 

                        I need to be able to search on all contacts, whether they be staff, student or someone else from the contacts perspective, so having this data separate in the checklist table is not very useful.

                        Any more suggestions?

                        Thanks

                        Tracey

                        • 9. Re: Excel import into 2 tables
                          philmodjunk

                          To repeat, you need two layouts in order to import your data into two tables. You cannot use the same layout to import data into both tables.

                          One layout should be based on contacts by specifying contacts in show records from. The other should be based on Checklist.

                          You then import data once on each layout into the tables on which they are based.

                          • 10. Re: Excel import into 2 tables
                            TraceyLillis

                            Followed your instructions and the data is appearing in the Checklist layout, the contact information is appearing in the Contacts layout, but they are not displaying an entire record (i.e. contact information plus checklist) on the one layout in Layout C. 

                            It is not picking up any relationship between the Checklist table and the Contact table when I try and specify a checklist field to display data from the Checklist TOC's.  It only says that the Contacts TOC's are related tables. 

                            I would upload the database for you to look at, however due to privacy reasons i am not permitted to upload the data!  Frown

                            I will upload some screenshots to see if they help. 

                            • 11. Re: Excel import into 2 tables
                              TraceyLillis

                              Here is one of the checklist too. 

                              • 12. Re: Excel import into 2 tables
                                philmodjunk

                                You could upload a clone (an empty copy), but let's look at your relationship first.

                                You do have a value in the excel file that uniquely identifies each row in the spread sheet?

                                If so, this column should be imported into a field in contacts and also into a field in Checklist. YOur relationship between the two tables should then be based on these two fields.

                                Do that and you'll see the linked data.

                                • 13. Re: Excel import into 2 tables
                                  TraceyLillis

                                  Dear Phil,

                                  Thankyou so much for your help and perseverance. You are a godsend!  If I could see you right now I would Kiss you!  You have saved me hundreds of hours manually typing this data into the database.  Thankyou thankyou!!!!

                                  I had the darn tables linked by student number (eventually with your help), but I wasn't chosing the linked relationship TOC for the field data.  Once I had done this and imported the spreadsheet into both tables, the data is there!

                                  Now off to fix the next one which you are helping with too. 

                                  Have a great weekend.

                                  Tracey