6 Replies Latest reply on Jun 30, 2014 3:38 AM by SalmanAlsudairy

    Import an Excel file-each cell in the file relate to a location specific tree

    SalmanAlsudairy

      Title

      Import an Excel file-each cell in the file relate to a location specific tree

      Post

           I would like to import two excel sheets into FM.  The first sheet contain the type of tree while the second contains the year the tree was planted.  The link between the two sheets are the raw and column addresses, thus cell BW50 in sheet one specify the type while cell BW50 in the other specify the year.  I plan to develop a database so that I can streamline the budgeting process both in terms of outputs as well as inputs.  More specifically, the amount the tree produces depends on the variety as well as the age of the tree.  Also the cost of the inputs varies based on those variables.  There are other factors such as the soil composition and moisture which necessitate an ability to track location specific variables.  AT this time, however, my focus is to be able to import two excel sheets.  Appreciate any suggestions on how I can do this systematically.

        • 1. Re: Import an Excel file-each cell in the file relate to a location specific tree
          SteveMartino

               There are a few ways.  First I would ask why the information has to start on spreadsheets instead of FM.  Do all the spreadsheets have the same data in the same cells?  But if it has to, the easiest (for me) is to create a 3rd sheet (sheet1) in the workbook that pulls over all the data you need from the other 2 sheets-with column labels across the top, and the data in the rows.

               Then you can import just that sheet into your database.  I would use a copy of the DB until you are sure it works as expected.

               The first time you import manually, lining up the fields on the spreadsheet to the fields in FM, selecting 'Use First Row as field labels".  If you get this to work, then make a script that does the import automatically, and attach it to a button.

          • 2. Re: Import an Excel file-each cell in the file relate to a location specific tree
            SalmanAlsudairy

                 I already have the information in excel sheets for budgeting purposes but the process is not efficient.  Each tree is in a particular location that is associated with a particular excel cell number.  So the sheet "Type" contains the type of tree in cell A10 and the Sheet "Year Planted" and in the same cell location provides the year the tree was planted - also A10.

                 You suggest that I combine the two sheets into one.  The question then is how do I do that and even if that occur, then FM needs to identify each cell as a particular record.

            • 3. Re: Import an Excel file-each cell in the file relate to a location specific tree
              SteveMartino

                   Easiest way for me to help is if you could show the Excel Workbook, or a simplified version with just a few records.  The second part, importing to FM then is pretty easy, but I don't know how much of the information you need, how you want the information, what do you want to do with it, and how the fields/relationships are set up in FM.  It is usually (in the few times I have done it) easy to set up then it is to explain.

                   But the more info you provide, the easier someone (or myself) can assist.

              • 4. Re: Import an Excel file-each cell in the file relate to a location specific tree
                FentonJones

                      

                     I'm a little confused about what you've got; partly because it does not seem that difficult, if you've done an Import from Excel to FileMaker before. If there is more than one "sheet" in an Excel file, then FileMaker will automatically who "Display Worksheets", and let you pick which one you want to Import. You can only Import one "worksheet" at a time, with its own "Specify import order:" choice (they may appear the same, but, in Excel, are not the same).

                      

                     [ Or, you could do as Steve said, and paste one "sheet" into the other. I don't know your data, so can't really say which is best.]

                      

                     So, if you have 2 "worksheets" in an Excel file, that's 2 "imports" into FileMaker (in either the same or different tables). This can be down manually, or can be scripted (likely best if you have to do it more than twice). If you wanted to add only the "field(s)" to existing FileMaker records, and you have a proper ID in both (FileMaker table records & Excel sheets), then you could use the option to "Update matching records". Otherwise use the default "Add new records".

                • 5. Re: Import an Excel file-each cell in the file relate to a location specific tree
                  SalmanAlsudairy

                       I will be more than happy to send you a subset of the file but, indicative of my limited knowledge, I was only able to send an image of the two sheets.  The first raw in each sheet captures the tree columns in the farm while the first column captures the raws in the farm.  Also each excel cell in both sheets provides information relevant to one tree (Type and Year Planted)

                  • 6. Re: Import an Excel file-each cell in the file relate to a location specific tree
                    SalmanAlsudairy

                         Now I do not see the images that I sent.