9 Replies Latest reply on Oct 4, 2012 1:21 PM by philmodjunk

    Excel import question?

    LukeMoore

      Title

      Excel import question?

      Post

           Is there any way to import just specific cells of various Excel worksheets (within one workbook) into a single Filemaker record. We are using FMP 11 Advanced. I've searched a little but I think my lack of proper terms has limited the results I'm seeing. Thanks guys!

            

           Luke

        • 1. Re: Excel import question?
          philmodjunk

               If the cells are different columns of the same row, this can be done. You can specify a range for that row in Excel and then import from that range into Filemaker. You can then select the specific cells in that row to match up to selected fields in the FileMaker database.

          • 2. Re: Excel import question?
            LukeMoore

                 So, one row = one record? No way around that? And I guess that leaves the multiple spread sheets out of the equasion?

            • 3. Re: Excel import question?
              philmodjunk

                   I think you need to describe what you are trying to do in more detail.

                   The standard import records operation is one spreadsheet row = one FileMaker record, but there are a number of options for working around that and it's certainly possible to import from multiple spreadsheets.

              • 4. Re: Excel import question?
                LukeMoore

                     I'm looking at importing data, some ranges and some individual cells, from an excel workbook with several sheets into a single record in my database. I've got sales reports that are generated daily and I have no access to the raw data or the ability to change those reports. For example, I want to take cell A1 from  worksheet 1 cell F3 from worksheet 2 and so on. What kind of workarounds are out there? I started making a macro in excel that grabs the data and puts it on a new worksheet in one single row then exports that sheet for import. That is going to be a pain use regularly. Any other ideas are very welcome.

                     Here's a side question, Some of our excel data moves within the individual spreadsheet up or down a few rows every week depending the number of employees that work that week. Is there any way to pin down the data intelligently?

                • 5. Re: Excel import question?
                  philmodjunk
                       

                            For example, I want to take cell A1 from  worksheet 1 cell F3 from worksheet 2 and so on. What kind of workarounds are out there?

                       You can set up a script that uses one import records operation to import data from a cell in worksheet 1 and another that imports data from a cell in worksheet 2.

                       

                            Some of our excel data moves within the individual spreadsheet up or down a few rows every week depending the number of employees that work that week. Is there any way to pin down the data intelligently?

                       My ignorance with regards to Excel may be showing, but might it be possible to define a range that moves with this data? If so, then you can import from the named range.

                       If not, then you may need to import into a temporary table in FileMaker, perform a find to isolate the records you want, and then do a second import records to import this found set into your final destination. Please note that such a process can be fully automated in a script.

                       You might also investigate setting up an ODBC connection to your excel file. This may make it possible to access the data in the excel spreadsheet as though it were from a FileMaker table.

                  • 6. Re: Excel import question?
                    LukeMoore

                         Those are all great ideas. I'll experiment with those ideas. Do you by have any examples of a script I can use to import a single cell of data from an excel file?

                    • 7. Re: Excel import question?
                      philmodjunk

                           Whether importing one cell or all of the cells in a row, it's the same import records scritp step. The difference is in the field to column mapping where you would map only a single column to a single field. And you'd have to define a range to limit the import to just one row in that worksheet.

                      • 8. Re: Excel import question?
                        LukeMoore

                             Can you tell me how to define a range to import just one row?

                        • 9. Re: Excel import question?
                          philmodjunk

                               I don't remember all the steps for setting up a named range in excel--that's a feature of the Excel program, not FileMaker.

                               You Open the Excel file and define the range, giving it a name. Then you select that range when setting up the Import Records step.