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.
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 = ;
Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
