10 Replies Latest reply on May 24, 2016 12:02 PM by alforiginal

    Problem exporting filemaker records into Excel

    alforiginal

      Hi all, this is my first post. I'm a filemaker novice but have been trying to wrap my head around things and have managed to create a database for my company (a production company) that does everything we need.

       

      However since upgrading Mac Office from 2011 to 2016 I've been having a problem with importing csv files from Filemaker 14. Excel reads the file fine, except for cells with multiple values in them (aka "repeating fields"). For the latter Excel adds a strange character - a question mark inside a box between each one - I guess where the carriage return was? In Excel 2011 the return was simply read as a space which worked fine, but this is really messing up my import into AVID (the ultimate destination of these records).

       

      I've tried the find and replace function in excel but it doesn't recognise the character, and I've tried exporting from Filemaker in numerous different codes - ASCII, Macintosh, UTF-8, UTF-16 but the character appears in all of them once imported into Excel. I've also tried exporting as both a csv and a tab file but the same thing keeps happening.

       

      Is this simply a problem with the new excel, or is there something I can do in Filemaker to prevent it from happening? Right now its really messing with my workflow and I've spent far too much time trying to figure it out already!

        • 1. Re: Problem exporting filemaker records into Excel
          smith7180

          When I import from excel I first bring the records into an 'import' table.  Then I (via scripts) clean up the data.  For example- I'll use the filter function to remove anything other than a predefined set of characters like:

           

          " abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890,./<>?;:'\"[{]}|`~!@#$%^&*()-_=+£€¥"

           

          Since that example group doesn't include a carriage return, carriage returns are removed.  In fact everything except for those characters are removed. 

           

          Then I import from the "import" table into the destination table.  Finally I delete all records in the import table.

          • 2. Re: Problem exporting filemaker records into Excel
            beverly

            Exporting Repeating Field Data | FileMaker

            Yes, you need to do something with the data (possibly a calc field with all the repeats):

            field[1] & "¶" & field[2] & ...

            then export that field.

             

            OR split as the article says, but I don't think you want that.

             

            Another thread (on this forum, sorry I cannot find the link) suggested exporting as HTML. Excel can read HTML.

            beverly

            • 3. Re: Problem exporting filemaker records into Excel
              DonCollier

              Can I ask why you are trying to go through Excel to get to Avid? Dose anything happen when it is in EXCEL ?

               

              Personally I would avoid EXCEL unless it is absolutely necessary as you are agin to cope with the changes that Microsoft introduce for no reason

               

              It should be perfectly possible to create a TAB or CSV from FileMaker to go straight into the ALE

              • 4. Re: Problem exporting filemaker records into Excel
                beverly

                The "issue" may be the repeating field upon export, Don.

                Personally, I'd use XML/XSLT to handle the export for Excel or anywhere needed, but that's me.

                beverly

                • 5. Re: Problem exporting filemaker records into Excel
                  DonCollier

                  I am sure it is Bev but there are issues with going through EXCEL as well. You have to do one thing to make it into EXCEL and then you hope that EXCEL will still honour the rules that the third party software needs !   I gave up with that a while back as I am finding more and more that the EXCEL version being referred to is not the current one!

                   

                  In either case you are going to need to do something to protect the array you built in the repeating fields

                  • 6. Re: Problem exporting filemaker records into Excel
                    alforiginal

                    Thank you guys. Really appreciate the help. To answer some questions and pose some of my own:

                     

                    - Its not neat but were trying to bring metadata from Filemaker and attach it to files in AVID, so we're exporting from Filemaker to Excel, combining the excel file with the ALE file in Text Wrangler, and then reimporting into AVID.

                     

                    - HTM doesn't give me the weird characters but unfortunately it breaks up the repeating fields into separate cells. We're trying to keep all repeating fields e.g. keywords in a single cell so that it can go into a single column in AVID

                     

                    - Very basic question but how do I use the filter function and where? In Excel or in Filemaker? How do I bring the data into an "import table"?

                     

                    - I don't think that Excel for Mac can read XML files in the same way that the windows version can? I don't have an import option...

                    • 7. Re: Problem exporting filemaker records into Excel
                      beverly

                      yes, Mac Excel can read XML. I do it all the time. It must be in the correct format (SpreadsheetML), but I can use XML & XSLT (export from FM) to include formulas and formatting. The FMPXMLRESULT grammar from the XML export alone will not open in Excel, it needs to be transformed with XSLT. You don't "import" the xml, you just save it as .xml (or .xls) and open with Excel.

                       

                      beverly

                      • 8. Re: Problem exporting filemaker records into Excel
                        DonCollier
                        - Its not neat but were trying to bring metadata from Filemaker and attach it to files in AVID, so we're exporting from Filemaker to Excel, combining the excel file with the ALE file in Text Wrangler, and then reimporting into AVID.

                        I am pretty sure you can do all of this in FileMaker as a single export.  No need to use Text Wrangler!

                         

                        Here is how I would approach it. Create an export table Insert the necessary information from the ALE using a script and use a different script to first put the data in the columns you need, then export that and import it into AVID directly . 

                         

                        I believe other AVID users are doing something similar. There was back in time a plugin that did it for you but it died as it was no longer necessary.

                        1 of 1 people found this helpful
                        • 9. Re: Problem exporting filemaker records into Excel
                          alforiginal

                          Hi Don,

                           

                          Thanks for the reply. This looks like a good solution! I will set it up and will probably need to troubleshoot as I go (because like I said, Im a filemaker novice), but really appreciate the advice!

                          • 10. Re: Problem exporting filemaker records into Excel
                            alforiginal

                            Hi All,

                             

                            Not an elegant solution but in case anyone else is interested, I found out that the character causing difficulties is the "Group Separator character" which filemaker adds to repeating fields. This is ASCII CHAR(29).

                             

                            The easiest way to remove it in excel and replace it with a space is to simply create a new column with the formula

                            =SUBSTITUTE(A1,CHAR(29)," ") where A1 is the target cell

                             

                            This avoids having to use export tables, scripts, VBA macros etc.

                            1 of 1 people found this helpful