Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
rohitk1609
Valued Contributor 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
Highlighted
rohitk1609
Valued Contributor III

Re: Create folder , Move files and zip the folder automatically ?

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
Highlighted
Employee
Employee

Re: Create folder , Move files and zip the folder automatically ?

Hi Rohit - I am asking some of our experts.

Regards,
Mike

Regards,
Mike Tarallo
Qlik
Highlighted
Employee
Employee

Re: Create folder , Move files and zip the folder automatically ?

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
Highlighted
rohitk1609
Valued Contributor III

Re: Create folder , Move files and zip the folder automatically ?

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

Highlighted
Employee
Employee

Re: Create folder , Move files and zip the folder automatically ?

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

Regards,
Mike Tarallo
Qlik
Highlighted
rohitk1609
Valued Contributor III

Re: Create folder , Move files and zip the folder automatically ?

is it any security issue in Legacy access mode ?

Highlighted
Employee
Employee

Re: Create folder , Move files and zip the folder automatically ?

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
Highlighted
rohitk1609
Valued Contributor III

Re: Create folder , Move files and zip the folder automatically ?

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