Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Partner
Partner

Store multiple folder excel files into multiple QVDs Challenge

Hi guys,

I have an automated extraction every week, in a zip or rar file, with the following structure :

  • AllTablesEXCEL 
    • Group1
      • Group1_1.xlsx
      • ...
      • Group1_N.xlsx
    • ...
    • GroupN

And I'd like to get a final structure like the following one :

  • AllTablesEXCEL 
    • Group1.qvd
    • ...
    • GroupN.qvd

Where in each Group qvd is stored all the excel files inside the subfolder

How could I do that?

I've done a sample of my structure in case someone could help me with that.

Best regards, Marcel

 

Labels (4)
1 Solution

Accepted Solutions
MindaugasBacius
Valued Contributor III

Re: Store multiple folder excel files into multiple QVDs Challenge

There are the script, pal:

SET ErrorMode = 0;

SUB DoDir(Root)

FOR each File in filelist( Root & '\*.xlsx')

TRACE '$(File)';

	Data:
    LOAD *
    	,FileBaseName()
    	,RowNo()
    FROM
    [$(File)]
    (ooxml, embedded labels, table is Hoja1);
    
NEXT File


FOR each Dir in Dirlist (Root&'\*')
     CALL DoDir(Dir)
NEXT Dir

END SUB 

CALL DoDir('C:\Users\Mindaugasb\OneDrive - UAB Biovela\Profile\Downloads\AllTablesEXCEL')

SET ErrorMode = 1;

STORE Data into 'C:\Users\Mindaugasb\OneDrive - UAB Biovela\Profile\Downloads\Data.qvd' (qvd);

Take a look at the out coming result:

Screenshot_1.jpg

 

I have added few additional lines to the file name and the lines counter.

4 Replies
Partner
Partner

Re: Store multiple folder excel files into multiple QVDs Challenge

You will somehow need to loop through your folders and fetch all files in each folder.
FOR EACH _folder.dir in DirList(some path)
...
NEXT

I'm too lazy to re-write this logic every time I need it. I always reuse a SUB where I just need folder and filter parameters to fetch my files. It is pretty similar to the qvc.listfiles in the QlikView Components project, its one of my favorites SUBs in that project. I really recommend you to check it out if you have not already have done so. https://github.com/RobWunderlich/Qlikview-Components
???
Celebrating my 300 accepted solution in the Qlik Community
???
MindaugasBacius
Valued Contributor III

Re: Store multiple folder excel files into multiple QVDs Challenge

There are the script, pal:

SET ErrorMode = 0;

SUB DoDir(Root)

FOR each File in filelist( Root & '\*.xlsx')

TRACE '$(File)';

	Data:
    LOAD *
    	,FileBaseName()
    	,RowNo()
    FROM
    [$(File)]
    (ooxml, embedded labels, table is Hoja1);
    
NEXT File


FOR each Dir in Dirlist (Root&'\*')
     CALL DoDir(Dir)
NEXT Dir

END SUB 

CALL DoDir('C:\Users\Mindaugasb\OneDrive - UAB Biovela\Profile\Downloads\AllTablesEXCEL')

SET ErrorMode = 1;

STORE Data into 'C:\Users\Mindaugasb\OneDrive - UAB Biovela\Profile\Downloads\Data.qvd' (qvd);

Take a look at the out coming result:

Screenshot_1.jpg

 

I have added few additional lines to the file name and the lines counter.

Partner
Partner

Re: Store multiple folder excel files into multiple QVDs Challenge

Thanks for the try @MindaugasBacius 

Your output is the following :

Output1.png

And my desired output is the following :

Output2.png

Where each folder is fully stored in a QVD.

Regards, Marcel.

Partner
Partner

Re: Store multiple folder excel files into multiple QVDs Challenge

Hello @MindaugasBacius

I've done it thanks to your script, thank you, I trully apreciate it.

I've just move the data store after the for next file loop

Regards, Marcel.