5 Replies Latest reply: Mar 2, 2015 10:18 AM by Peter Cammaert RSS

    Writing .bat file in script?

    Vignesh Kumar

      Hi Everyone,

       

      I have a scenario Where i have excel file in a source directory

      I need to check the File name in alphabetical order and need to separate the files and move or copy into certain folders.

       

      File like

      Example

                  A.xlsx,

                  B.Xlsx

                  C.XLSX and so on

       

      Now i want to generate the QVD to do this process.

       

      I do Have sample script,

       

      BatFile:

      Load

       

      'Move E:\Testsource\*.xlsx E:\TestDestination\' As [@echo off]

       

      Execute E:\Automove.bat;

       

      Its moving the entire file to the destination folder. Kindly give me some idea or guidance to solve it.

       

      Thanks in Advance

      Vignesh

        • Re: Writing .bat file in script?
          Peter Turner

          Hello Vignesh,

           

          The way i like todo this is to make a string of your DOS command, and then tell QlikView to execute the command, such as:

           

          Let vDosCommand = 'CMD /s /c "Move E:\Testsource\*.xlsx E:\TestDestination\"';

          EXECUTE $(vDosCommand);

           

          The dos commands are online if you google them, but the CMD is command prompt

          /s is silent mode

          /c is close after execute

          anything within the double quotes " " is the actual command.

          You can also string multiple dos commands together by adding the && to the end followed by another dos command.

           

          You'll also need to set QlikView to 'Can Execute External Programs' in the settings of the edit script window.

          One thing to note is that QlikView does not get a message back from this command prompt saying if the command worked ok, but you can check this from the logs if needed.

            • Re: Writing .bat file in script?
              Vignesh Kumar

              Hello Peter,

               

              Thanks its working but What i exactly needed is, I have a Folder Name called Testsource in that i have lots of excel files.

              I need to create a new folder and need to move or copy some of the file in that.

              For Example,

              I have Excel files starting in alphabet A to E, so i need to move or copy those file alone in a new folder.

               

               

              Is it possible in Qlikview.. If its possible can you guide me how to solve in this using Condition.

               

              Thanks in advance,

              Vignesh

                • Re: Writing .bat file in script?
                  Peter Cammaert

                  Create an INLINE table with source specifications (e.g. A*.xlsx, B*.xlsx etc.) in the first column and target (sub)directories in the second column. This is what you will be editing whenever some files should go somewhere else.

                   

                  Then create a loop around Peter's execute statement, like:

                   

                  FromToSpecs:

                  LOAD * INLINE [

                  From, To

                  A*.XLSX, Directory1

                  B*.XLSX, Directory1

                  :

                  E*.XLSX, Directory1

                  F*.XLSX, Directory2

                  :

                  ];

                   

                  FOR i = 0 TO NoOfRows('FromToSpecs') - 1

                    LET vFromFileMask = 'E:\Testsource\' & peek('From', $(i), 'FromToSpecs');

                    LET vDestination = 'E:\TestDestination\' & peek('To', $(i), 'FromToSpecs') & '\';

                    EXECUTE CMD.EXE /c /s COPY "$(vFromFileMask)" "$(vDestination)";

                  NEXT


                  If you want to move the files over, use MOVE instead of COPY.

                    • Re: Writing .bat file in script?
                      Peter Turner

                      Hello,

                      Peter'C example will give you more flexibility and would be the best to use here.

                       

                      I was trying to find if you can run a dos command such as dir [a-e]*.xlsx but it doesn't appear you can.

                      Else just a long string such as

                      Let vDosCommand = 'CMD /s /c

                      "Move E:\Testsource\A*.xlsx E:\TestDestination\" &&

                      "Move E:\Testsource\B*.xlsx E:\TestDestination\" &&

                      "Move E:\Testsource\C*.xlsx E:\TestDestination\" &&

                      "Move E:\Testsource\D*.xlsx E:\TestDestination\" &&

                      "Move E:\Testsource\E*.xlsx E:\TestDestination\" ;

                      This should work but i'd go for Peter'C example instead.