7 Replies Latest reply: Sep 5, 2017 3:08 AM by Steve Caiels RSS

    Folder for data vs a single file

    Julie Sitzmann

      I would like to have my data prep studio workspace project pull from a folder with multiple files, rather than having to keep adding every file.   I would then add each months file to that folder.   Is that possible?   Or if not, how do i add an additional file to an existing workspace?   I'm learning slowly on this new version so bear with me if these are dumb questions.    Thanks

        • Re: Folder for data vs a single file
          Steve Caiels

          Hi Julie,

          The server product would allow you to automate this process.  It could automatically run when a new file arrives or process all files in the folder at specified intervals.  You wouldn't need to know the names or quantity of the files in the folder or provide any manual interaction.  If you need this to happen automatically, please take a look at Automator.

           

          If you are happy to manually add new files as they arrive, then there are two options:

           

          1.     To replace a single file.  Open the workspace and navigate to the Prep Data windows.  Click the drop down next to your exiting file name and edit the file path.

          2.     To replace all existing workspace reports (the old ones) with all new reports in a folder, click on one of the reports.  Press Ctrl-A to select all. Right click and remove reports. Select all the new reports in a Windows Explorer window and drag them onto the workspace in the left hand 'table' menu. You should see the border of the table name turn green before dropping all the reports into place.

           

          Regards,

          Steve.

          • Re: Folder for data vs a single file
            Grant Perkins

            Hi Julie,

             

            Is the folder in which the files reside a collection for a year (or perhaps a business reporting period of some sort)?

             

            I was wondering whether, in addition to Steve's suggested scenarios, there was an alternative possibility that you were collecting reports for a period in a common folder and were looking to have DPS automatically add all reports found in a specific folder, retaining any sources that already exist in the Workspace.

             

            The second part of such functionality would be having DPS pick a change list to apply to the file as it is loaded.

             

            If this is a once a month activity it could be argued that dragging the file to the open Workspace and selecting the required change list (or lists) is a pretty fast way to operate and further generic automation does not add much benefit. However if you are looking at something that happens several times a day or you have many reports that all need attention once a month it would be a different story and the full automation, as suggested by Steve, would likely make sense for several reasons.

             

            One other things that occurs to me is that if the report being analysed always had the same name when delivered, implying tat there is only ever one file with that name in the source folder,  the "Refresh Data Table" function would refresh the data with the latest input.

             

            If none of these suggestion entirely fit your purpose we should probably evaluate your preferred workflow to see how the options within DPS might be harnessed to most closely deliver what you would prefer. However if, ultimately, you would prefer a fully automated solution and the input files are consistent enough to allow that to work (Some sources, notably PDF generators, are not always so consistent) then full Automation would make sense even if you then further analyse the results in DPS.

             

            HTH.

             

             

            Grant

              • Re: Folder for data vs a single file
                Julie Sitzmann

                Thank you Grant and Steve for your comments.  Grant your comment:

                 

                I was wondering whether, in addition to Steve's suggested scenarios, there was an alternative possibility that you were collecting reports for a period in a common folder and were looking to have DPS automatically add all reports found in a specific folder, retaining any sources that already exist in the Workspace.

                This is exactly what I’m doing.   I have a folder for the current year and add a new report each month so the folder is a collection of the year to date month’s files.   Nothing is overwritten.  It’s a new report added each month and I have the project set up to append all the reports into one.   This is where I’m stuck.

                 

                Julie

                  • Re: Folder for data vs a single file
                    Grant Perkins

                    Hi Julie,

                     

                    Hmm.

                     

                    Once a month reports (assuming they do not take hours to process!) are possibly not easy to justify by themselves for Automation. But if the organization already uses Automator then the process can be defined rather well.

                     

                    If DPS is a preferred way forward and you already have a Workspace for the task and all of the changes you normally need in a change list (or lists) then it may well be really easy to simply open the Workspace, presumably populated with the files from the previous months as the year progresses, drag in the new monthly report, apply the change lists and that (assuming nothing much else is required ....) is the month dealt with, the monthly table having been created.

                     

                    To concatenate the new month analysis to previous months as a "year to date" record, DPS offer an "Append" feature under the Prep Data" tab. This should provide you with the functionality you need to create a concatenated table in the Workspace that gives you one reporting file for "YTD" without losing the month by month analysis and I think you would have a at least a couple of approaches to doing that.

                     

                    Why not have a look at it, see if it makes sense to you (The Append Helper should be useful) and then let's take things from there.

                     

                    HTH.

                     

                     

                    Grant

                     

                     

                     

                    @Steve: If you have an opportunity to chip in on this please do  - I suspect there may be much more capability to this feature than I have yet discovered.

                    • Re: Folder for data vs a single file
                      Steve Caiels

                      Hi Julie,

                       

                      The only fully automatic option you have for opening multiple files in DPS is via Automator.

                       

                      Assuming the reports share the same structure - in other words, they work with the same workspace and change list - you should just be able to drag the new report in each month and resave the workspace.,

                       

                      For example; here is a workspace with a single file:

                      0 - Capture.JPG

                      When a new report arrives, click "Add More Stuff" in the top left of the screen and drag the new report on top of the existing report.  The border should change green.

                      1 - Capture.JPG

                      After this, you should see both reports in the workspace and the table will be a concatenation of the two months.

                      2 - Capture.JPG

                       

                      Regards,

                      Steve.

                        • Re: Folder for data vs a single file
                          Grant Perkins

                          Ah!

                           

                          Good point Steve.

                           

                          Presumably, if one wanted to have separate months as tables as well as the concatenated YTD, one could just add the new report twice - once to the existing analysed expense report as described above and once into the Workspace? Or is that unnecessary?

                           

                          Perhaps the "Append" functionality is really intended to combine data from reports that are not of a consistent format?

                           

                          Time to experiment some more .....

                           

                           

                          Grant

                            • Re: Folder for data vs a single file
                              Steve Caiels

                              I think that's a fair point as well Grant!

                               

                              You can individually 'eyeball' and export records from individual reports that make up a single table, but you can't transform, combine or duplicate them.

                              Appending the tables would maintain easy access to the individual underlying tables. Or, as you say, you can open the reports twice. 

                               

                              Regards,

                              Steve.