Skip to main content
Announcements
Marching toward a simplified navigation! READ ON
cancel
Showing results for 
Search instead for 
Did you mean: 
varmekontrol
Creator
Creator

Exclude files from load, depending on their header.

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

SerialNoCreatedVolume1Volume2Volume3Volume4

 

File 2: Headers

SerialNoCreatedHeat1Heat2Heat3Heat4TemperatureDelta-TErrorCode

 

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 

1 Solution

Accepted Solutions
mrybalko
Creator II
Creator II

@varmekontrol 

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

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

View solution in original post

6 Replies
mrybalko
Creator II
Creator II


@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
Creator
Creator
Author

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
Creator II
Creator II

@varmekontrol 

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

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

varmekontrol
Creator
Creator
Author

@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
Creator
Creator
Author

@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
Creator II
Creator II

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')