4 Replies Latest reply on Jun 30, 2014 7:36 AM by ChristopherKessler

    Importing from Excel into repeating field?

    ChristopherKessler

      Title

      Importing from Excel into repeating field?

      Post

           I have a database with a repeating field (4 repeats), and have an excel spreadsheet with a single field that contains up to four different items. Currently these items are separated by semi-colons, but when I import them they are put into the first repeat as "item1;item2;item3..."

           I have tried separating them with commas, and with carriage returns, but they still import into the first repeat and not into the others. Is there a way to have Filemaker import them into the separate repeats? I have found this article (http://www.filemaker.com/help/html/import_export.16.18.html) that claims an options box should appear for managing repeating data, but this does not pop up.

           My import is about 5300 records, and I have two such repeating fields to import, so hopefully this can be done automatically without having to use an options dialogue box for each record.

        • 1. Re: Importing from Excel into repeating field?
          philmodjunk

               I would suggest that you remove the repeating fields from your database and replace them with related records as the long term fix for this issue.

               The only way that I can think of to get the data into your repetitions from such data is to import the data and let it be imported all into one field or into the first repetition of the repeating field and then use a script that loops through the found set produced by Import Records to parse this data into the individual repetitions of your repeating fields.

               Note: Repeating Fields, back in Filemaker 2.5--a flat file non relational database system, were the only way to associate multiple identical type items with a single record. With the release of FileMaker 3, FileMaker became a relational data base and we gained a better way of accomplishing this that is far more flexible. Thus, while there are still a very small numbers of "special case exceptions",  a repeating field is almost always a less than optimum design choice for a FileMaker Database.

          • 2. Re: Importing from Excel into repeating field?
            ChristopherKessler

                 Then I guess I'm a bit stuck. I have a table of animals with specific attributes I've called "defects", such as the following:

                 Animal 1 -- Missing a toe; broken wing; deformed tail

                 Animal 2 -- Missing a toe; deformed tail

                 Animal 3 -- Broken wing

                 I would like these to be in the same field so I can search this field for any of these three attributes to find whatever animals they encompass. For instance, searching for "Broken wing" in this one field should bring up Animals 1 and 3, but not Animal 2. The repeating field does this perfectly, with the one problem that importing this data from an Excel sheet only populates the first repeat, and not the others.

                 If possible, I would like these attributes to be part of this "animals" table, and not be a separate table referenced by a lookup, if possible...but I'm open to anything.

            • 3. Re: Importing from Excel into repeating field?
              philmodjunk
                   

                        I would like these to be in the same field so I can search this field for any of these three attributes to find whatever animals they encompass.

                   This is also possible and easy to do with a related table of records when the search is performed from the context of the parent table.

                   With a portal to a table of such entries, you can enter find mode, specify "broken wing" in a field in the portal and you'll find all records in the layout's table that have at least one record with this text--exactly the same results as doing this on a repeating field.

                   

                        If possible, I would like these attributes to be part of this "animals" table, and not be a separate table referenced by a lookup, if possible...but I'm open to anything.

                   At this point, I see no benefits from doing that and do see a loss of flexibility.

              • 4. Re: Importing from Excel into repeating field?
                ChristopherKessler

                     Thanks. I'll look into using a related table.

                     BTW, I found out how to do the import properly by using the ASCII "group" character. The items in each field I wished to separate were done so by a semi-colon character. I exported it to a CSV formatted file and opened it in TextWrangler (a basic but powerful text editor for the Mac), and then found and replaced all instances of semi-colons with the ASCII ^29 (group/information separator) character using the program's find/replace feature and the system's character palette for locating the ASCII ^29 character. The character does not print, so I had to drag it from the palette (viewed by adding the Unicode code table to the character palette, and then browsing through the basic characters) to the "Replace" field of the fine/replace box, and then perform the find/replace action. When done, importing this properly distributed the separate groups into the different repeats of the fields. It did take a little preparation, but when done it went smoothly.