topic Store multiple folder excel files into multiple QVDs Challenge in QlikView

Store multiple folder excel files into multiple QVDs Challenge

marcel_olmo — Sat, 16 Nov 2024 04:41:37 GMT

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

Re: Store multiple folder excel files into multiple QVDs Challenge

Vegar — Tue, 22 Jan 2019 23:11:03 GMT

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

Re: Store multiple folder excel files into multiple QVDs Challenge

MindaugasBacius — Wed, 23 Jan 2019 06:50:01 GMT

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:

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

Re: Store multiple folder excel files into multiple QVDs Challenge

marcel_olmo — Fri, 25 Jan 2019 20:53:57 GMT

Thanks for the try @MindaugasBacius

Your output is the following :

And my desired output is the following :

Where each folder is fully stored in a QVD.

Regards, Marcel.

Re: Store multiple folder excel files into multiple QVDs Challenge

marcel_olmo — Sat, 26 Jan 2019 06:45:23 GMT

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.