1 2 Previous Next 22 Replies Latest reply on Sep 17, 2016 4:21 AM by fmpdude

    Filemaker to Excel... but they don't want 50+ columns...

    ender

      Our area manager wants an export from Filemaker to Excel, but doesn't want "a huge spreadsheet with 50+ columns". She want to know if there is a way to export Filemaker data (including portal data from related tables) to specific cells in Excel spreadsheets--one record per spreadsheet. From what I understand, she is hoping for something form-like similar to what we have in Filemaker. (The spreadsheets will be a "long-term interim" tool while a corporate system is under transition.)

       

      I've been reading about exporting as .csv, XML/XLST, Merge, etc. and working with pivot tables, etc., and encountering descriptions that sound vaguely similar to what we want to do, but am confused on what would actually get us there. I have almost no experience with other formats, and I'm no Excel expert either. (I'm just the FM database person.)

       

      Part of my problem, I think, is that some of the descriptions I've read reference deprecated functionality in Filemaker or Excel (e.g. apparently Excel used to have a Filemaker import option).

       

      • What process and/or format should I spend my time researching in order to make this happen?
      • Anyone know of a tutorial that would help?

       

      We have multiple users on various platforms:

      • Filemaker: 13 (soon to be 14) on Windows 10 Enterprise, some still on Windows 7, and some on Mac OS Mavericks (soon to be Yosemite)
      • Excel: 2016 on Windows 10, and 2013 on Windows Enterprise 7
      • May also have some users on Excel 2008 on Mac OS

       

      Thank you.

        • 1. Re: Filemaker to Excel... but they don't want 50+ columns...
          fmpdude

          If you mean something beyond the Export in FM where you can select the fields to export to Excel, then writing a field to a "particular cell" in a destination spreadsheet will take more work.

           

          Without knowing your programming background, I can't give you specific suggestions.

           

          One way to do it would be to use the Java/JDBC to connect to the FM database in real time, extract the fields you want, and then use an API like "jExcel" (JExcelApi ) or Apache POI (POI-HSSF and POI-XSSF - Java API To Access Microsoft Excel Format Files ) to write to the spreadsheet. That code could even be automated/scheduled. And, this really is not that difficult if you're already a SW developer (say, Java, C, or .NET).

           

          One of the things I like best about FM is that when it can't do something I want (which is more often than I would like), FM is still open enough (with external interfaces) where I can work around those limitations with some effort.

           

          HOPE THIS HELPS.

          • 2. Re: Filemaker to Excel... but they don't want 50+ columns...
            mikebeargie

            Oh gosh, that sounds awful. I hope that area manager is making it worth your while!

             

            360Works Scribe: FileMaker plug-in to read and write PDF, Word, Excel files

            I'd highly recommend you spring for a license of this plugin and use that. It will allow you to "open" an excel file, write any data from filemaker to any specified cell in the file, and then save the file as a new file.

             

            From this you can open a "template" excel file stored in a filemaker container (or imported from a known location), make your modifications, and then export said excel file or even email it.

             

            One thing that is sorely lacking with native methods that your area manager will probably not be happy without is excel formatting. .mer files.

             

            Another option would be to configure FileMaker as an ODBC data source and write some macros to pull data into the spreadsheet similar to how a mail merge is done. You can do it with .mer files but it's a very "hands on process"; something you won't want to be doing for every record in the database.

            • 3. Re: Filemaker to Excel... but they don't want 50+ columns...
              fmpdude

              Mike's ideas are cool, but the Java approach will give you complete control.

               

              In this case, per MIke's suggestion, I would try the plug-in before trying the Java approach. (Well, if it were me, I'd do the Java approach since I don't get the cost of FM plug-ins, but I seem to be in the minority.)

              • 4. Re: Filemaker to Excel... but they don't want 50+ columns...
                David Moyer

                HI,

                I have 14 - if you require more than one sheet, I think you're out of luck (without a third party solution).

                One way I've "dealt" with Excel is to use a worker table with a bunch of text fields that can be exported as columns (e.g. text01, text02, text03, etc.).  I programmatically create each temporary worker-record and populate each field, including data, headers, summaries, and blank lines.  Every record is a row.  You can also populate a numerical "SortOrder" to help you organize the rows.

                • 5. Re: Filemaker to Excel... but they don't want 50+ columns...
                  monkeybreadsoftware

                  You can use MBS Plugin with XL functions to automatically remove columns.

                   

                  Or use the XL functions to load a template Excel file and insert the numbers you need to report.

                  Load a file, insert rows with data, fill cells with values, export as Excel file.

                   

                  If interested, load our plugin and check the examples.

                  • 6. Re: Filemaker to Excel... but they don't want 50+ columns...
                    TonyWhite

                    As you can see, from the previous posts, there are many ways to approach this task...

                    Here is one more method to consider: AppleScript...

                    MicroSoft invested in AppleScript when it dropped VBA from Excel and did a very complete job. You can learn more here:
                    http://www.mactech.com/articles/mactech/Vol.23/23.02/2302AppleScript/index.html

                    We have used AppleScript for lot of projects where data is moved from FileMaker to custom formatted Excel documents.

                    The disadvantage is that AppleScript is OS X only. That said, you might only need a single (perhaps a robot) OS X machine in the mix.

                    The advantage to AppleScript is that it is:
                    * Free (if you are running OS X)
                    * Can talk to lots of applications, for example, the Finder, email clients, FileMaker, Excel, Word, etc, etc. meaning you can set up powerful multiple application workflows.

                    Hope that helps.

                    Tony White
                    http://www.twdesigns.com
                    http://FileMaker-Fanatics.com

                    • 7. Re: Filemaker to Excel... but they don't want 50+ columns...
                      ender

                      My programming knowledge doesn't extend much beyond being the Filemaker guy, so the references to Java, etc. are over my head. I wish I had time to remedy that.

                       

                      No multiple sheets. One Filemaker record > One Excel file. (Yes, file. It has to do with the corporate interim "solution".)

                      Not looking for any live connectivity.

                       

                      It has to be something that multiple users can do easily: Push a button to export one record from FM as an Excel file, somehow have that data be in certain cells, and save that where others can access it.

                       

                      I'm starting to wonder if we could just export from Filemaker to that 50+-column Excel file, then use Excel functions to move data to certain cells in a "template" that would be easier for them to work with than the loooong spreadsheet with confusing columns. Apparently that's the major issue.

                       

                      A plug-in is a possibility, including 360Works Scribe. This:

                      It will allow you to "open" an excel file, write any data from filemaker to any specified cell in the file, and then save the file as a new file.

                      ...does sound like what we want to do--although I want to make sure I'm not overlooking an "easier" way to accomplish this before I recommend spending $$.

                      • 8. Re: Filemaker to Excel... but they don't want 50+ columns...
                        mikebeargie

                        Make sure to factor in your labor in your cost estimates. The value of most plugins comes in their ability to save hours of development time in the “cheaper” alternatives.

                        • 9. Re: Filemaker to Excel... but they don't want 50+ columns...
                          David Moyer

                          sorry dude,  I'm not certain you currently have access to 14 - I made this super-simple demo ...

                          • 10. Re: Filemaker to Excel... but they don't want 50+ columns...
                            fmpdude

                            That's a good point, Mike.

                             

                            I would only add that FM plug-ins are only for FM, which might be OK for most.

                             

                            But, when I create a "workaround" for missing FM functionality, using other techniques, it is generic enough, usually, where I can use it elsewhere -- that is, in other non FM contexts ( as well as in FM).

                             

                            HOPE THIS HELPS.

                            • 11. Re: Filemaker to Excel... but they don't want 50+ columns...
                              beverly

                              This is almost similar to a solution for a client. They had a workbook solution (Excel) where the first tab (worksheet) was the data imported from .csv (from FileMaker). Then there were wonderful links on the other tabs to reference the imported data. They had many reports (each for a single user) that used the same template, but different data.

                               

                              It worked beautifully, even if it needed a little manual intervention (to import).

                               

                              The Scribe Plug-in (not available at the time) would definitely take the manual part out of the equation, once setup could be used again and again.

                              beverly

                              • 12. Re: Filemaker to Excel... but they don't want 50+ columns...
                                phil-hanson

                                Before you go getting all complicated, I just want to check that you are aware that you can *choose* which fields (columns) you send to excel if you select "Export <Table Name> Records", instead of "Save/Send <Table Name> Records as ..."

                                 

                                That way you can leave out the unwanted and confusing fields/columns like interface fields or unwanted related fields.

                                 

                                And you can name the worksheet.

                                • 13. Re: Filemaker to Excel... but they don't want 50+ columns...
                                  beverly

                                  +1 and the context makes a difference in a related export. from parent is different than from child(-ren).

                                  beverly

                                  • 14. Re: Filemaker to Excel... but they don't want 50+ columns...
                                    alangodfrey

                                    One Excel file per record? A single-row Excel file?  You can isolate one record and export just its data.
                                    You could have a found set of records and loop through them creating an Excel file per record.

                                    If you only want 10 of the 50 fields available you can just export those 10 - eg:  Field A, Field D, Field M, etc.

                                    If you want them in a different order (Field M, Field A, Field D) then re-arrange them in the export dialogue.

                                    If you want them to appear in the Excel sheet in Column A, [blank], [blank], Column F, etc, then you could create a second worksheet that references the exported columns and displays them in the sequence you want.

                                     

                                    The problem with that is that you would be over-writing your Workbook-with-calculations every time you created another export.  To get around that, either create the second sheet as a second single-worksheet workbook and reference from it back to the freshly exported workbook.

                                    or

                                    Create the two-sheet workbook as described, but do not export as Excel format.  Export in merge format, and import the merge data into the first worksheet.  You can make this happen automatically as soon as the user opens the workbook, so it appears seamless to them.

                                     

                                    The tools available make Filemaker and Excel work really well together - various export possibilities from Filemaker, scripting them, on-timer scripting them, automatically triggering the opening of an Excel workbook, having macros auto-running on open in Excel... it is a really powerful combo.  You can often make it that if the user blinks they might not even be aware they have slipped from a Filemaker table of data into a pivot chart presentation in Excel.

                                     

                                    I come back to the first condition, though: that your solution needs to involve a single Excel file per record (and I'm qualifying that with 'single row' - because if you are trying to export what looks like a header row followed by many item-line rows then you need to heed Beverly's advice about which table you start the export from) then I think that requirement would be well worth revisiting.

                                    1 2 Previous Next