Exclude files from load, depending on their header.

[varmekontrol]

Hi

I want to know if it is possible to exclude files depending on the headers in the file. 

Say I have two different files with different headers in the same folder (in this case I have millions and more like 6 different filetypes) 

File 1:  Headers

SerialNo

Created

Volume1

Volume2

Volume3

Volume4

File 2: Headers

SerialNo

Created

Heat1

Heat2

Heat3

Heat4

Temperature

Delta-T

ErrorCode

I am looking for something like

load

SerialNo,
Created,
Volume1,
Volume2,
Volume3,
Volume4

from *.csv where header = Volume1 or Volume2 

And then I would make another load 

load 
SerialNo,	
Created,	
Heat1,	
Heat2,	
Heat3,	
Heat4,	
Temperature,	
Delta-T,
ErrorCode,

from *.csv where header = Heat1

I am trying to achieve something like another open question I have in the community, just instead of separating the content in a folder, depending on the headers. -> Here 

[mrybalko]

---

@varmekontrol wrote:

Hi

I want to know if it is possible to exclude files depending on the headers in the file. 

---

Yes it is possible. You can read first row and determine header types. But it is required to load every file in your huge storage.

Is your task conversion numerous csv to one qvd for every header type? 

[varmekontrol]

Thank you for your answer @mrybalko 

My task is to use qlik to filter out the different 6 types of files that are in the folder. The files are from an automated system that delivers them daily to a folder, and the system does not know the difference between the different files. 

From that filtering, I need to filter out a table with all the files with that specific header. Then repeat for the last 5 types. 

[mrybalko · Accepted solution]

@varmekontrol 

Find example in attachment.  I assume that you have enough RAM to load all csv at once. 

I suggest to create individual qvw for every csv type and split qvds if they exceed 200 MB

[varmekontrol]

@mrybalko  

Thank you for the blueprint and description of how to solve this problem. 

I have 20+ GB ddr4 ram, so should not be a problem as I think I will also limit the historical data by only a couple of week., 

Thank you for all the work, and it works like a charm 🙂 

[varmekontrol]

@mrybalko 

Is there any way to prevent the script from generating an error when running into an empty file? 

There were no fields to load in the given source.
LOAD *
FROM [C:\Users\sorensda\Documents\QlikView\Alarm og Rapport system\Convert\Input\0006046949_60749040_valuereport_20190317033736_2105.csv]
(txt, utf8, embedded labels, delimiter is ';', msq)

[mrybalko]

You can check file size using filesize()

FOR Each vCurFile in FileList('Input\*.csv')
	
	IF FileSize('$(vCurFile)') > 0 THEN
		//QV uses by default autoconcatenate to tables with the same fields set	
		LOAD *
		FROM [$(vCurFile)]
		(txt, utf8, embedded labels, delimiter is ';', msq);
		
	ENDIF //IF FileSize('$(vCurFile)') > 0 THEN
	
NEXT //FOR Each vCurFile in FileList('Input\*.csv')