Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld Online 2021, May 10-12: Our Free, Virtual, Global Event REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
rohitk1609
Specialist III
Specialist III

Create folder , Move files and zip the folder automatically ?

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

Folder Structure.PNG

 

 

 

 

 

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:

Result.PNG

 

 

 

 

 

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

I am hoping speedy response

Thanks in advance

Rohit

Labels (2)
1 Solution

Accepted Solutions
rohitk1609
Specialist III
Specialist III
Author

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;

View solution in original post

7 Replies
Michael_Tarallo
Employee
Employee

Hi Rohit - I am asking some of our experts.

Regards,
Mike

Regards,
Mike Tarallo
Qlik
Michael_Tarallo
Employee
Employee

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

Regards,
Mike Tarallo
Qlik
rohitk1609
Specialist III
Specialist III
Author

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;

View solution in original post

Michael_Tarallo
Employee
Employee

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

Regards,
Mike Tarallo
Qlik
rohitk1609
Specialist III
Specialist III
Author

is it any security issue in Legacy access mode ?

Michael_Tarallo
Employee
Employee

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

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

Regards,
Mike Tarallo
Qlik
rohitk1609
Specialist III
Specialist III
Author

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