Create folder , Move files and zip the folder automatically ?

[rohitk1609]

Hi techies,

I have a use case where I have 2 parent directories,

1. ABC
2. XYZ

Both Directories have sub folders with name in month-year as MMM-YYYY format and out of sub folders current month.csv files are available. Current month .csv files means file with current month timestamp in name, please check the attach image 

For folder ABC it looks like

note: csv files have timestamp in name 0102202001 which means this file belongs to Feb 2020,

Now what I need to do create an automatic process ,

• 1.On the 2nd day of each month, move ALL the files from previous month to corresponding ..\XXX-YYYY folder
• ZIP the file into XXX_YYYY.ZIP file
• Delete the ..\XXX_YYYY folder

So result should be:

I am open to use using cmd or powershell by Qlik.

I am hoping speedy response

Thanks in advance

Rohit

[Michael_Tarallo]

Hi Rohit - I am asking some of our experts.

Regards,
Mike

[Michael_Tarallo]

Hi Rohit - you may want to check these out:

As we think you can do this with Windows Powershell.

https://stackoverflow.com/questions/17366675/using-get-childitem-to-get-a-list-of-files-modified-in-...

and then look at "using the get-childitem cmdlet" section here

https://www.petri.com/copy-move-rename-files-using-windows-powershell

Let us know if you have any other questions

Regards,

Mike

[rohitk1609 · Accepted solution]

Thanks Mike for your prompt response.

I found the solution as:

Orgs:
LOAD
Orgs
FROM [lib://Data/FolderAndFilesAdminActivity/Orgs_List.xlsx]
(ooxml, embedded labels, table is Orgs);

let vLoopCount = NoOfRows('Orgs');

drop table Orgs;

set vLoopstep=1;
FOR vLoopstep = 1 to vLoopCount

Orgs:
LOAD
Orgs
FROM [lib://Data/FolderAndFilesAdminActivity/Orgs_List.xlsx]
(ooxml, embedded labels, table is Orgs);

let vDirectoryName = FieldValue('Orgs',$(vLoopstep));

// let vDirectoryName = subfield('$(Dir)','/',4);
let vSubDirectoryName =date(addmonths(now(),-1),'MMM-YYYY');
let vFileNameTime = date(addmonths(now(),-1),'MMYYYY');
// Load
// '$(vDirectoryName)' as DirectoryName
// AutoGenerate(1);


Execute cmd.exe /C mkdir C:\PhysicalData\Test\"Qlik Data"\lData\$(vDirectoryName)\$(vSubDirectoryName);
Execute cmd.exe /C xcopy C:\PhysicalData\Test\"Qlik Data"\Data\$(vDirectoryName)\W**$(vFileNameTime)**.***.csv C:\PhysicalData\Test\"Qlik Data"\Data\$(vDirectoryName)\$(vSubDirectoryName);
Execute "C:\Program Files\7-Zip\7z.exe" a C:\PhysicalData\Test\"Qlik Data"\Data\$(vDirectoryName)\$(vSubDirectoryName).zip C:\PhysicalData\Test\"Qlik Data"\Data\$(vDirectoryName)\$(vSubDirectoryName);
Execute cmd.exe /C del C:\PhysicalData\Test\"Qlik Data"\Data\$(vDirectoryName)\V**$(vFileNameTime)**.***.csv;
Execute cmd.exe /C rmdir /S /Q C:\PhysicalData\Test\"Qlik Data"\Data\$(vDirectoryName)\$(vSubDirectoryName);

NEXT vLoopstep

drop table Orgs;

[Michael_Tarallo]

OK I see you are using Legacy access mode with absolute directories.  Thanks for sharing. 

[rohitk1609]

is it any security issue in Legacy access mode ?

[Michael_Tarallo]

Hi Rohit - please check out this support article - there are a few things to consider:

https://support.qlik.com/articles/000034992

[rohitk1609]

I read it,

For most deployments, no, legacy mode is not recommended. It can be useful in some deployment scenarios where the security concerns are mitigated.

may be because we can impact server other resource which are directly related to Qlik.

I am good for  now

Thanks,

Rohit