1 2 Previous Next 18 Replies Latest reply on Apr 2, 2014 6:55 AM by Vyke

    Feature suggestion:  Smarter conversion from Excel

    sgbotsford

      I'm in a trial at this point, so my approach is that of a newby. I will post some impressions as I go through this in hopes of making FM a better product.

       

      I currently have an inventory system based in excel. To reduce reentry I have two workbooks with 4 columns in the Inventory speadsheet based on lookups into the abbreviations worksheet.

       

      I was dissapointed in FM. FM didn't seem to be aware that *anything* was happening in the spread sheet, it just slurped it over as values.

       

      What I expected to happen:

       

      1. With the reference to the second workbook, offer to import that worksheet also.

      2. Replace instances of VLOOKUP in the first workbook with a relationship.

      3. Replace calcuated fields in the first workbook with calculated fields.

      4. Construct a checklist of things that would need to be checked to verify that it was working correctly.

      5. If a form existed in the source spreadsheet do a first approximation of that form in a layout.

      6. If parts of the source worksheet were protected, at least show them in a different colour in the layout to show that they needed checking.

      7. If parts of the source worksheet had validation, try to implement that validation, or at least mark them.

        • 1. Re: Feature suggestion:  Smarter conversion from Excel
          steveromig

          The best place to submit feature requests like this is here...

           

          http://www.filemaker.com/company/contact/feature_request.html

           

          Steve Romig

          FileMaker, Inc.

          • 2. Re: Feature suggestion:  Smarter conversion from Excel

            Hi "sg",

             

            Given the vast array of Excel implementaions that exist among users, I suspect your expectation of a seamless migration from any multi worksheet Excel solution to an equivalent solution in FileMaker is bordering on the very difficut, if not impossible.

             

            Were I to tackle the task that you've outlined, I'd be importing each worksheet into its own table as separate tasks, then create the relationship(s) making sure that key fields matched and the relationships would work as intended. Only then would I look at where AutoEnter calculations might replace the VLOOKUP, and see if field validations could be used to streamline data entry and updates. This is the core art and craft of FM development.

             

            There are so many development/developer decisions needed in the course of this task that I'd be reluctant to trust a fully automated process, even if it provided intelligent interrogation along the way.

             

            Your expectations were obviously vastly different.

             

            Regards,

             

            John

            • 3. Re: Feature suggestion:  Smarter conversion from Excel
              sgbotsford

              Valid points.  In any rich programming environment, there are many ways to do any one thing.  And I don't expect that the transition would be flawless.  However I would think that it would be possilbe to do a lot of it, some parts easily, some parts not at all.

               

              Eg:  Just having a formula taht references another wooksheet/workbook should be reason to bring in that item.

               

               

              If 1 is "Import a single worksheet" and 10 is "full working conversion"  I would expect a 3 or 4.

              • 4. Re: Feature suggestion:  Smarter conversion from Excel
                beverly

                Perhaps the XML of the Excel could be used in some way to "Get" the formulas.

                 

                Beverly

                • 5. Re: Feature suggestion:  Smarter conversion from Excel
                  sgbotsford

                  Since Open Office and Libra Office are open source software, parsing the

                  formulas should be fairly straight forward, regardless of the format of the

                  excel file.  In addition several other programs are able to read and at

                  least partially parse the results.  E.g.  iWorks Numbers can read and save

                  xls file.

                   

                  Respectfully,

                   

                  Sherwood of Sherwood's Forests

                   

                  Sherwood Botsford

                  Sherwood's Forests --  http://Sherwoods-Forests.com

                  780-848-2548

                  50042 Range Rd 31

                  Warburg, Alberta T0C 2T0

                  • 6. Re: Feature suggestion:  Smarter conversion from Excel
                    Malcolm

                    I find it hard to imagine a situation where the imagined conversion would be useful.

                     

                    I frequently see complex Excel spreadsheets. The owner wants to move to FileMaker because Excel isn't the tool for the job; not that they haven't tried to make it work.

                     

                    These spreadsheets have to be interpreted. The business rules have to be determined. The tricks have to be laid bare. Copying the formula from a cell may seem simple but consider this. Last week I had a sheet which calculated prices for produce. One of the columns used a formula. I imported the fields into filemaker and recreated the formula from the first row. I noticed anomalies. The person who used the spreadsheet had modified the formula in numerous rows. They did it because they could - and they wanted the same flexibility in FileMaker.

                     

                    Today I have a sheet in front of me that is used as a lookup data source and has eight different regions of the sheet used for data lookups. Is that the equivalent of eight tables in the one sheet? Or is it a single table with eight references to different keys?

                     

                    Take the opporunity to use FileMaker to generate a new tool that does a great job. Creating a facsimile of a tool designed in Excel is not going to give you that.

                     

                    Malcolm

                    • 7. Re: Feature suggestion:  Smarter conversion from Excel
                      sgbotsford

                      You make a good case.  I was hoping for a 'first kick at the cat' sort of

                      approach where if you had a multi-worksheet excel ap you would end up with

                      the bulk of your data, and the bulk of your relationships pre-made.

                       

                      Let's turn it around a different way then, since what I originaly wanted is

                      not possible:

                       

                       

                      Start from scratch.  Build tables (correct word?) and build the

                      relationships I need.  How do I populate a given table from a given chunk

                      of spreadsheet?

                       

                      Respectfully,

                       

                      Sherwood of Sherwood's Forests

                       

                      Sherwood Botsford

                      Sherwood's Forests --  http://Sherwoods-Forests.com

                      780-848-2548

                      50042 Range Rd 31

                      Warburg, Alberta T0C 2T0

                      • 8. Re: Feature suggestion:  Smarter conversion from Excel
                        Mike_Mitchell

                        Copy that chunk to a new worksheet and import from there.

                         

                        Mike

                        • 9. Re: Feature suggestion:  Smarter conversion from Excel
                          sgbotsford

                          Yeah.  That's what I'm finding.  Be nice if the import you could map

                          spreadsheet columns to table columns.

                           

                          Respectfully,

                           

                          Sherwood of Sherwood's Forests

                           

                          Sherwood Botsford

                          Sherwood's Forests --  http://Sherwoods-Forests.com

                          780-848-2548

                          50042 Range Rd 31

                          Warburg, Alberta T0C 2T0

                          • 10. Re: Feature suggestion:  Smarter conversion from Excel
                            ColinKeefe

                            The FTS (FileMaker Training Series) 13 Basics PDF/ebook is a great primer on getting started working with FileMaker.  While Lesson 6: Importing Data may at first glance feel light, it may be worth going through the document anyway, since many of the assumptions and questions you have might be answered in a quick read. 

                             

                            In short, you can map spreadsheet columns to table columns, and you can import different sheets in a workbook into new tables, generating the new tables on the fly.

                             

                            http://www.filemaker.com/support/training/fts.html

                             

                            See Activity 6.2, page 80.

                            • 11. Re: Feature suggestion:  Smarter conversion from Excel
                              sgbotsford

                              Thanks.  Good resource.

                               

                              New app.  New mindset.  Initially everything takes forever, becuase you

                              keep running into things you don't know.

                               

                              Respectfully,

                               

                              Sherwood of Sherwood's Forests

                               

                              Sherwood Botsford

                              Sherwood's Forests --  http://Sherwoods-Forests.com

                              780-848-2548

                              50042 Range Rd 31

                              Warburg, Alberta T0C 2T0

                              • 12. Re: Feature suggestion:  Smarter conversion from Excel
                                beverly

                                I've been making files for a long time (still having a Floppy disk for "FileMaker" from Forethought, and all). However I also keep running into things I don't know.

                                 

                                Welcome to the community, s!

                                 

                                There are some things we know not and sometimes the plug-in people fix that for us and sometimes the other developers suggest various solutions. Asking is the only way to get some suggestions whether they answer your question or not.

                                 

                                FileMaker is a toolkit and there are some fine craftspersons out there. There may be 10 ways to do the same thing. FileMaker seems to have the ability (with those crafty persons help) to communicate with other systems. It may be easy, it may take a lot of work. Just because it's not a drag-n-drop all-things Excel to FMP, does not mean that it can't be 'done'.

                                 

                                Hang in there, it gets easier...

                                Beverly

                                • 13. Re: Feature suggestion:  Smarter conversion from Excel
                                  hjgunn@mmedia.is

                                  What I have been discovering from having Excel spreadsheets thrown at me for "Filemaker conversion" I'm starting to do the following:

                                   

                                  Each Workbook imported into a specific table.

                                  If you want to link the data after the import there needs to be at least one key field in each table that is duplicated in  one or more of the other tables.

                                   

                                  I usually build the tables in Filemaker from scratch before importing into them.

                                   

                                  I then create the needed relations.

                                   

                                  I then add whatever other functionality is requested.

                                   

                                  ===

                                   

                                  The reason for me being here today is that I needed a Dynamic way to import data from multiple Excel files.

                                   

                                  For what it is worth I found a method and relisted it in :  https://fmdev.filemaker.com/message/143284#143284

                                   

                                  The original description is at: https://fmdev.filemaker.com/message/13254#13254

                                  • 14. Re: Feature suggestion:  Smarter conversion from Excel
                                    Vyke

                                    ^---- this, a thousand times this. If you think of Sheets in a workbook as separate tables, then you have a major issue taken care of right there. As for the users editing calculations, well you could build out a set of calculations that use user input to derive the values.

                                    1 2 Previous Next