Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Thanks Jpenuliar,
Sorry, but I was even more confused by this first example used in the link you provided.
Thanks Though
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 = ;
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.