topic Re: Writing .bat file in script? in QlikView

Writing .bat file in script?

Mon, 02 Mar 2015 13:13:14 GMT

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 — Mon, 02 Mar 2015 13:28:22 GMT

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?

Mon, 02 Mar 2015 13:53:13 GMT

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 — Mon, 02 Mar 2015 14:52:04 GMT

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)";

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

Re: Writing .bat file in script?

peter_turner — Mon, 02 Mar 2015 15:11:19 GMT

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.

Re: Writing .bat file in script?

Peter_Cammaert — Mon, 02 Mar 2015 15:18:21 GMT

In DOS times, regular expressions were Unix-only territory. Unfortunately...