4 Replies Latest reply: May 15, 2014 10:08 AM by Olly Bond RSS

    export to directory based on key field

    LCampbell _

      I have one report that I need to split out as Excel files to 140 locations.  I can use the keyfield in the filename to create one report per location but these exports also need to go to a specific directory based on the key field.  How can I get the export to go to a specific folder/directory based on a key field?  I am using Data Pump 10.5. 

       

      Example: C:\LocationA\locationA.xls, C:\LocationB\locationB.xls, etc

       

      Thanks.

        • export to directory based on key field
          Olly Bond

          Hello LCampbell,

           

          In 10.5, the export options allow you to specify &[keyvalue], or &[filter], in the location of an export, as well as the first value of a field, summary name, measure title, and process name. So if you can construct your project(s) so that the location is used as a naming convention for one of these, automating the distribution to location-based subfolders should be OK.

           

          HTH,

           

          Olly

            • export to directory based on key field
              LCampbell _

              I have a location field.  I also have a calculated field in my data containing "CCB-" and the location number ("CCB-LocationA" as example).  I have my model doing an export of summary where "each distinct value of the first key makes a separate file or table" using the calculated value.  The Export to folder is C:\Temp\&[firstvalue.Location]\.  The file naming is &[keyvalue].

               

              I end up with one file for each keyvalue in a single folder for the first location in the complete master data.  In other words I get C:\Temp\LocationA\CCB-LocationA.xls and C:\Temp\LocationA\CCB-LocationB.xls etc with everything in the LocationA folder.

               

              If I change it to export of summary where "each distinct value of the first key makes a separate file or table" with Export to folder as C:\Temp\&[keyvalue\ and the file naming also as &[keyvalue].  I end up with all of the location files in a folder called "&[keyvalue]", C:\Temp\&[keyvalue]\CCB-LocationA.xls, C:\Temp\&[keyvalue]\CCB-LocationB.xls, etc.

               

              How do I get to C:\Temp\LocationA\CCB-LocationA.xls and C:\Temp\LocationB\CCB-LocationB.xls?  or even C:\Temp\CCB-LocationA\CCB-LocationA.xls and C:\Temp\CCB-LocationB\CCB-LocationB.xls???

                • export to directory based on key field
                  Bill Watson

                  what we have done in the past in cases like this is to think a bit more laterally..

                   

                  first step - using your existing model output all your different exports to a single directory, for examples sake say C:\Data\

                   

                  in the model create a calculated field called BATCHLINE with something like the following formula:

                   

                  'MOVE "C:\Data\CCB-''.XLS" "C:\TEMP\CCB-''\CCB-''.XLS"'

                   

                  Then hide this field and create a summary with just this field as the key value. export this summary as a space delimited text file but give it the extension .BAT

                   

                  then just execute the resultant batch file. Your exports should end up in the directories you need them to be.

                   

                  once you have this all set up, just add execution of the batch file to the end of your main process.

                    • export to directory based on key field
                      Olly Bond

                      Hello LCampbell, Hello Bill,

                       

                       

                      I was thinking of a two stage process using an export distribution followed by an input distribution on a second project which used the FILE() function, but a twelve hour day at the office prevented me from trying this out.

                       

                      I've used the technique of a summary with a calculated field as a batch command a lot in a documentation tool I've been working on, as well as many other projects. It's also very useful if you don't have DataPump and are relying on doing everything with batch files.

                       

                      I like the use of ' instead of " in the calculated field expression - I've always used " as a delimiter and had to hack it with CHR(34) to get Monarch to insert quotes.

                       

                      Best wishes,

                       

                      Olly