1 2 Previous Next 15 Replies Latest reply on Aug 8, 2016 9:49 AM by beverly

    Merging recurring imported excel spreadsheets

    SeattleSquirrel

      Hi,

       

      Here's what I have currently: Each employee has a personal excel timesheet that lives in my OneDrive. I've not to date found a good option for auto-merge of these timesheets (I'm on a Mac and excel is a bit more limited). I create summary pivot tables from everyone's timesheets to get dept status overview. Right now I wait until end of month, then copy every person's time entries into a master sheet, and then generate the summary. I would like this info to be up to date daily but it would require a lot more work on my part - and time is not of abundance.

       

      Here's what I would like to do: I want to set up filemaker to do recurring imports of this data (click of a button!) which it does beautifully. However, it imports each person's timesheet into its own table. Thus I still don't have one location where the data is merged/consolidated. Is there a way for me to create a merged table that continues to be dynamic when I import new data?

       

      Grateful for help!!

        • 1. Re: Merging recurring imported excel spreadsheets
          ErikWegweiser

          Hi, SeattleSquirrel: It sounds like what you're currently doing is manually 'opening' the Excel file in FileMaker, thus 'importing' the data into a newly-created database file.

           

          If I understand correctly, you want to import data from numerous Excel files directly into an existing FileMaker database table, adding to the data in that table, and to automate that process.

           

          You can set-up a script to import an excel file into a table. This works best when the files being imported are always the same format/structure). It can be a little tricky, but is very common, normal practice.

           

          Look for the Import Records script step. Here you can specify a file by path and specify the field mapping. I would start by setting the path to and field mapping for an exemplar file. Later, you can remove the path so the user selects the file each time OR you can keep the path, so long as each time you import a file, it has the same name and location. You'll probably want to perform the import without a dialog, UNLESS you need to manually tweak the field mapping for different files each time (but that would reduce the effectiveness of automation).

           

          Good luck.

          • 2. Re: Merging recurring imported excel spreadsheets
            SeattleSquirrel

            Hi Erik,

             

            Well, I have that part set up. The files are already importing, and the Import function was nice enough to create scripts to enable import updates with the click of a button. But Sussie's timesheet imports into one table, while Mike's into another. In the end I'd like to end up with one auto-generated merged table that contains all employees data. Not sure if that makes sense, let me know if not!

             

            Thank you so much for taking the time to answer my question - much, much appreciated!

            • 3. Re: Merging recurring imported excel spreadsheets
              beverly

              If the scripts are created, can you just edit them to point to the same table, then call them in succession (with a "controller" script, perhaps)?

               

               

              beverly

              • 4. Re: Merging recurring imported excel spreadsheets
                SeattleSquirrel

                Hm, that's interesting! If I did that, and then tried to update it daily, what would happen? Would it save over data?

                • 5. Re: Merging recurring imported excel spreadsheets
                  beverly

                  Are you "updating" existing data or importing new records each time? I would think this would be new "daily" records. Why would you need to remove/delete yesterday? or would you?

                  beverly

                  • 6. Re: Merging recurring imported excel spreadsheets
                    SeattleSquirrel

                    I used the FM option in the Import dialogue box "Set up as automatic recurring import" - that then created a table layout with a button that I click to import. Not sure if that answers your question?

                     

                    You are right, I don't want to delete anything from yesterday. The timesheets are a running list (excel spreadsheet) of all time entries over time so I basically just want to make sure FM has included all the latest entries too.

                     

                    This part all seems to work fine on individual tables, question is just how I merge / consolidate all the employees entries into one table. I can't even do this in excel (mac) without some labor intensive workarounds or manual copy and paste.

                    • 7. Re: Merging recurring imported excel spreadsheets
                      beverly

                      Maybe make a backup and try to move one of the "imports" to add to another import (and change the table to be the correct one).

                       

                      Can you post any of the script(s) for feedback?

                      Once you merge the scripts they work.

                       

                      beverly

                      • 8. Re: Merging recurring imported excel spreadsheets
                        SeattleSquirrel

                        I may be in over my head here - not sure how to do this:

                         

                        move one of the "imports" to add to another import (and change the table to be the correct one).

                         

                        I've attached the script that FM creates automatically when I set up the "automatic recurring imports"

                        Screen Shot 2016-05-17 at 16.47.46.png

                         

                        So basically I have one script each for all the different timesheets now.

                        • 9. Re: Merging recurring imported excel spreadsheets
                          beverly

                          OK, so lines 4 &5:

                          Show All Records

                          Delete All Records

                          These clean out the previous record EACH time, so you want to disable them.

                           

                          Line 2:

                          Go to Layout ...

                          This going to be different for each import (for now), because the layout is tied to the table/table occurrence

                           

                          Line 6 is the key feature and would need to be revised so that the table points to the same one each time. I would need to be changed (along with line 2 - Layout) to be one layout one table for each of the different imports.

                           

                          First, can you verify that you have the SAME fields in each of the new tables? Because that is very import to allow a single table for the imports.

                           

                          beverly

                          • 10. Re: Merging recurring imported excel spreadsheets
                            KarlAmundson

                            SeattleSquirrel,

                             

                            Hello, Just trying to follow the thread, Excel imports. 

                             

                            Do it every day.  We have set up a series of imports from Excel spreadsheets, by creating a recurring import. 

                             

                            First, give you some simple quick solutions that work. 

                             

                            Take your Excel spreadsheet worksheet and open it as a FileMaker file and set first line to field names. 

                            (Just works well) You will take the Excel spreadsheet and convert it to an coma delimited .csv file for import later...

                             

                            Import the FileMaker pro converted spreadsheet making sure your change the FileMaker Pro Table and File Name of your converted file.  Open and change the names so your layouts and table are your preferred names.

                            Import and create an recurring import, it will creates a ER diagram table, Layout and Script.

                            So, now you have the table, layout and a script, with not data yet , no problem. 

                            Go back to your excel spreadsheet and save is as a coma delimited .csv file.  You will have to save all the spreadsheets as a .csv to it import, change the script location and type to .csv...

                            Now go to your newly created layout and import the excel.csv file... 

                            You will find a script on the layout. 

                            Remember to turn off the delete script step - unless you are importing fresh data each import,  added each spreadsheet data into the table and record.

                            The script will need to be modified both for map data, (script debugger is a friend) Script step. 

                             

                            FileMaker 14 server will run this script if you set in up in the backups.  Place excel spreadsheet.csv in the location \\servername\Program Files\FileMaker Server\Data\Documents\Folder (this location is deep in the filemaker folders)

                            I have about 15 imports running automatically at 4:00 a.m. every day...

                             

                            Hope this helps, I have some friends with MacTutor in Clearwater, who help me.

                             

                            Karl Amundson

                            • 11. Re: Merging recurring imported excel spreadsheets
                              SeattleSquirrel

                              yes, coorect! All spreadsheets are identical. I'll try this tomorrow. If I do delete all - what's the difference in end the result?

                              • 12. Re: Merging recurring imported excel spreadsheets
                                SeattleSquirrel

                                thank you! The spreadsheets have functions such as linking to an external sheet so the employee will be able to solve a project code based on a predefined list. I don't think I can do that w csv files? Would this work with regular excel format?

                                • 13. Re: Merging recurring imported excel spreadsheets
                                  beverly

                                  The predefined list must be imported into FileMaker (once) into a new table for "lookup" with a relationship between the 'time sheets' and the project code list. Yes, you can use .csv as import into FileMaker!

                                  beverly

                                  • 14. Re: Merging recurring imported excel spreadsheets
                                    GJ1159

                                    Anyone have an issue when doing the first initial import for the recurring file?

                                     

                                    I have my spreadsheet saved with 20 files just to test out the functionality of this feature, and every time I do the import, it will take empty cells from the spreadsheet and import 40 records...20 with data and 20 blank.

                                     

                                    I thought maybe it was how I was saving my excel sheet, but I tried every possible way and on top of that you can't actually delete empty cells on the spreadsheets, every cell you delete it creates a new one anyway...

                                     

                                    Any ideas, suggestions, tips would be awesome!

                                    1 2 Previous Next