11 Replies Latest reply on Jan 12, 2012 5:53 PM by RossHurley

    Importing Excel Repeating Fields Into FM 11

    don6397

      I've been trying without any success to import an Excel spreadsheet containing an account # & a reprating amount field (12 repitiions) for the 12 months within a year.

       

      I've researched FM Help & this site, but still can not locate the mechanics of how this should be done. Can anyone provide suggestions?

        • 1. Re: Importing Excel Repeating Fields Into FM 11
          comment

          Not sure what you mean - AFAIK, there are no repeating fields in Excel.

           

          Importing into repeating fields in Filemaker is not trivial either - and in most cases you should not be using repeating fields to store your data anyway.

          • 2. Re: Importing Excel Repeating Fields Into FM 11
            don6397

            Sure you can have a repeating field in Excel as I have previously defind for you - account # in column 1, on line 1 with 12 individual columnar cells horizontially each containing an amount for a particular month.  Excel doesn't refer to it as a repeating field because it is cell based software.

             

            FM can accomplishe the same thing by using a non-repeating field for the account # and a repeating field for each monthly amount such as Amount[12].  In FM's table view, that is how it can be setup to emulate a spreadsheet. 

             

            Importing Excel data into FM has not been a problem with non-repeating fields, works every time and simple.  However, repeating fields are a different story.

             

            Using repeating fields is a perfect solution for the software being written and I'm sure you would agree if you knew the design and functionality of the software. 

             

            In any event, thanks for your comments. 

            • 3. Re: Importing Excel Repeating Fields Into FM 11
              comment

              don6397 wrote:

               

              FM can accomplishe the same thing by using a non-repeating field for the account # and a repeating field for each monthly amount such as Amount[12]. 

               

              IMHO, the equivalent thing in Filemaker would be a non-repeating field for the account # and 12 non-repeating fields - one for each monthly amount.

               

              In any case, I don't know how you can import Excel data directly into a repeating field. Note also that you cannot export data in repeating fields to Excel. You could, however, use a repeating calculation field to "fetch" the data once it's imported.

               

               

              don6397 wrote:

               

              Using repeating fields is a perfect solution for the software being written and I'm sure you would agree if you knew the design and functionality of the software. 

               

              That's possible, but very unlikely.

              • 4. Re: Importing Excel Repeating Fields Into FM 11

                don6397 wrote:

                 

                Importing Excel data into FM has not been a problem with non-repeating fields, works every time and simple.  However, repeating fields are a different story.

                 

                Hi Don,

                 

                You even provide one of the reasons yourself (above) why repeating fields are not good choice.  If it is to hold data, and particularly data which needs to be treated as 'independent cells' then repeating field is the last place you want to store it ... with multiple fields being second-to-last.

                 

                I personally love Repeaters but I use them only for Developer purposes - never data. 

                • 5. Re: Importing Excel Repeating Fields Into FM 11
                  beverly

                  I think of cross-tab data as 3 fields in a "join table". One field for the values in the first column (label/header), one field for the values in the first row (header/label), and one field for the values in the cell intersected by the row/column. if you have 5 values in the first column, for example, and 12 values in the first row, then you'd have 60 records after import.

                   

                  This also is not easy to import, but you have data that can be used in more ways than for "cross-tab" spreadsheets.

                  IMHO,

                  Beverly Voth

                  • 6. Re: Importing Excel Repeating Fields Into FM 11
                    brianrich46

                    Hi Don

                     

                    The only non-filemaker file type which supports repeating fields during import is a merge file. In this situation, the structure of the repeating data consists of the repeating data values separated by the group separator character, which is an ascii code 29 character.

                     

                    To work out the exact format you require, I'd suggest that you export a few records out of your filemaker database into a .mer file and see how the file structure appears. This should help you work out the structure of the import file.

                     

                    I'd do this by building a separate worksheet in Excel which contains copies from the main worksheet of just the fields you want to import. You'll need to calculate a new column on this sheet which is a concatenation of the 12 months values with an ascii 29 between each value. You may also need to add a first row which contains the field names you want each column to go to in Filemaker. It may take a bit of fiddling the first time to get it working, but it does work well once you get it right.

                     

                    Repeating fields do have their uses, despite what many say.

                     

                    Hope this helps

                     

                    Brian

                    • 7. Re: Importing Excel Repeating Fields Into FM 11
                      comment

                      brianrich46 wrote:

                       

                      Repeating fields do have their uses

                       

                      Indeed they do. For example, one could import the Excel worksheet as is into a temp table having 1 + 12 data fields and a repeating calculation field with 12 repetitions. Then import the ID and the repeating field into the final table, breaking the repetitions into individual records during the import - so that the final result is a separate record for each value, as described by Beverly.

                      • 8. Re: Importing Excel Repeating Fields Into FM 11
                        don6397

                        After spending more trial & error time on my problem, I was able to come up with a solution that works for my situation.  I've learned that FM can only import & export repeating fields when the imports or exports are performed between FM databases.  Repeating records can be kept as repeating fields or split into seaparate records when being imported or exported.

                         

                        FM can not import native Excel files with the intent of converting records into repeating fields.  When FM exports FM records to Excel, only the first fiield in a repeating field will be exported.  All other repetitions will be ignored & not exported.

                         

                        The solution that worked for me started with a closed Excel file being dragged onto the FM icon on my desktop.

                         

                        FM will create a new FM file showing each of the 12 repetitions in separate fields for each account # as one record.

                         

                        In the newly created FM file, I wrote a simple looping script using Set Field to assign each of the 12 monthly amounts to a new repetitive field having 12 repetitions.  I ended up with one repetitive filed for each account #.

                         

                        I then imported the new repetitive records into the other FM file selecting them to be imported as a repetitive field.

                         

                        Since this process only needs to be done once a year, my solution serves my purpose.

                         

                        I thank evryone for their thoughts & comments.  I appreciate the help.

                        • 9. Re: Importing Excel Repeating Fields Into FM 11
                          beverly

                          Great! that's the solution that works, then it's the CORRECT one and don't let anyone tell you differently.

                           

                          Most of us old-er-experienced FM developers did precisely what you did (MANY TIMES OVER). We tested and experimented and got it to work. Now your the expert at the import-into-repeating fields from excel.

                           

                          Beverly

                          • 10. Re: Importing Excel Repeating Fields Into FM 11
                            don6397

                            Thank you for your help & the kind words…Don

                            • 11. Re: Importing Excel Repeating Fields Into FM 11
                              RossHurley

                              Don,

                               

                              Back in FileMaker Pro 2 days I did importing and exporting to/from Excel successfully with repeating fields, once I discovered that the ASCII character for the delimiter of repeating fields is ASCII29. I thought I had exported repeating fields to Excel successfully and this is how I discovered the ASCII character, but I could be wrong. I know I could use Find and Replace in Word files to replace a given character with an ASCII29 character. My notes of that period say to use the carat character in the Replace field, eg to replace given characters with ASCII29, put ^29 in the Replace field.

                               

                              You should, then, be able to build a formula in Excel to concatenate the 12 cells in each row with ASCII29 between them, then import that column into your FMP repeating field.

                               

                              Maybe this will do what you want.

                               

                              Ross

                              Adelaide, Australia