Load multiple excel files from multiple folders

[Violent_Democracy]

Hi,

I have four folders that each contains roughly 250 Excel files. I need to load about 70 files from each folder, all files begin with the same filename, all files have the same header. Sometimes the file has no data (not even a header).  

I used 

Table1:
LOAD
[FacilityRef],
[Streetname],
[Suburb],
[TenantNo],
[StartDate]
FROM [lib://in/Fac*.csv]
(txt, utf8, embedded labels, delimiter is ',', msq);

And repeated this 3 times changing the Table 1 to Table2,3,4 etc and created a new connection for each table load (in2, in3, in4).

While this loads I get errors, I think from the blank files...

What I would like to try and achieve is where all files load and get no errors into 1 table. This way I can use a left join later on from another datasource, but 1 thing at a time.

Also I want to be able to have a table where I see the TenantNo by the FacilityRef (This can be a many to 1 relationship) and the StartDate.

I know this is a lot and I appreciate any assistance anyone can provide for me in getting this work.

Cheers

[jpenuliar]

This link could be a good start:

https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/ScriptControlS...

the examples given are pretty solid

[Violent_Democracy]

Thanks Jpenuliar,

Sorry, but I was even more confused by this first example used in the link you provided.

Thanks Though

[jonathandienst]

This is the script pattern based on your description:

// Create blank target file
Data:
LOAD '' as FacilityRef AutoGenerate 0;

//Loop over directories
For Each vDir in ('in', 'in2', 'in3', 'in4')

	//Loop over the files
	For Each vFile in FileList('lib://$(vDir)/Fac*.csv')
	
		//Skip blank files
		Set ErrorMode = 0;
		
		Concatenate(Data)
		LOAD
			FacilityRef,
			Streetname,
			Suburb,
			TenantNo,
			StartDate
		FROM [$(vFile)]
		(txt, utf8, embedded labels, delimiter is ',', msq);
		
		//Resume normal error handling
		Set ErrorMode = 1;
		
	Next vFile
Next vDir

Set vFile = ;
Set vDir = ;
			

[Violent_Democracy]

Thanks for your help here,

I ran into an error in Line 5 "For Each vDir in ('in', 'in2', 'in3', 'in4').

Debugging points to the first comma for some reason.

I might have to look into this further.

Cheers again.