Concatenate Load data from multiple files

[dsharmaqv]

Hi

I am trying to load data from multiple files in one single table.

Can some fone assist me how do I execute the loop for reading multiple files from a folder.

For Example :

Table A:

Load (Data from All the files with Name starting with TableA)

Table B

Load (Data from All the files with Name starting with TableB)

Folder Name Xyz

File Name format(Table?_<name>_DDMMYYYY_MMHHSS)

TableA_XYZ_31052016_121730

TableA_XYZ_30062016_120855

TableB_SDG_31052016_121730

TableB_SDG_30062016_120855

Also I am extracting year and Month from file name to populate month and year fields for table A and B both.

Please assist.

[tamilarasu · Accepted solution]

Hi Deepak,

You can try below script.

[Table A]:
LOAD *,
Year(Date#(Subfield(FileBaseName(),'_',3),'DDMMYYYY')) as Year,
Date(Date#(Subfield(FileBaseName(),'_',3),'DDMMYYYY'),'MM') as Month
FROM
[FilePath\TableA_*.xlsx]
(ooxml, embedded labels, table is Sheet1);

[Table B]:
LOAD *,
Year(Date#(Subfield(FileBaseName(),'_',3),'DDMMYYYY')) as Year,
Date(Date#(Subfield(FileBaseName(),'_',3),'DDMMYYYY'),'MM') as Month
FROM
[FilePath\TableB_*.xlsx]
(ooxml, embedded labels, table is Sheet1);

[dsharmaqv]

Great Raju Ji! It works.

Sorry I forgot to mention one condition.

I have to verify the weather file is in standard formate, if it is not then I have to move that file in junk folder and if it is in proper format then I have to move it to Archive folder after load.

Many thanks in Advance!

[tamilarasu]

Hi Deepak,

There is no default Qlikview functions available to move the files from one folder to another folder. But you can use console command in load script. Here is the sample code.

SUB DoDir(Root) 

FOR Each File in Filelist( Root & '\*.xlsx') 

  Let vFile = Upper(SubField(File,'\',SubStringCount(File,'\')+1));

If '$(vFile)' Like 'TABLE*_***_*' THEN

  Table:
  LOAD *,
  Year(Date#(Subfield('$(vFile)','_',3),'DDMMYYYY')) as Year, 
  Date(Date#(Subfield('$(vFile)','_',3),'DDMMYYYY'),'MM') as Month 
  FROM 
  [$(File)] 
  (ooxml, embedded labels, table is Sheet1);

  EXECUTE cmd.exe /c move  "$(File)" "$(vArchive)";
ELSE

  EXECUTE cmd.exe /c move  "$(File)" "$(vJunk)";
ENDIF

NEXT File 

END SUB  

//********************************* 
CALL DoDir($(vSource))
//********************************* 

Make sure that the "Can Execute External Programs" option on the Settings tab is enabled.

Also, Goto Settings -> User Preferences -> Security tab ->  Add tick mark in "Script (Allow Database Write and Execute Statements)

How To Run:

Paste all your files in the folder named as "Source Files". After that you can run the application. If the file is in proper format, it will be moved to "Archive" folder. If not, all files will be moved to "Junk" folder.

I have attached sample test file. You can run and see how it works.

Note: I am not sure about your field names in your Table A and Table B files. If both the files have same headers, then Qlikview will automatically concatenate the tables together. So the above script concatenate all the files into single table  (i.e files like "Table*_***_*") .