Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have found a few posts that are similar but the solutions aren't working or are way over my head....
The scenario is this...
I have a folder with weekly exports of data, the tables are always in the first sheet; HOWEVER, the sheet name is different each week as it has the date stamp in the name. Example below....
I am able to get the file names to load into a temp table, then trying to cycle through each file name to load data...Field names are always static.
I am getting an error on the colored section....
==========CODE HERE=============
// creates temp table (empty)
[temp]:
load * inline [
file_name
];
// add data to temp table
for each f in fileList('[FILE LOCATION REMOVED]')
trace "Adding new file to temp_2: $(f)";
if wildmatch('$(f)', '*GuestPM*.xls*') then
concatenate (temp)
load * inline [
file_name
$(f)
];
end if;
next f
// creates a second temp-table, which is the first table, but with ordered data
[temp_2]:
noconcatenate
load *
resident temp
where len(file_name) > 0
order by file_name desc
; // clean up the first temp table that we don't need anymore
drop table temp;
// loops through each value in the ordered dataset in temp_2 and loads the data from that Excel file
let no_of_rows=NoOfRows('temp_2') ;
trace NoOfRows = $(no_of_rows);
for i = 1 to NoOfRows('temp_2')
let f_name = peek('file_name', $(i), 'temp_2' );
//trace "{index: $(i), f_name: $(f_name)}";
if '$(f_name)' <> '' then
[GuestPM]:
load
[Property],
[Area Name],
[PM Template],
[Started],
[Completed],
[Work Load],
[Completed by]
//Date(Date#(Started,'m/d/YYYY hh:mm:ss'),'MM/DD/YYYY') as Date_Started,
//Date(Date#(Completed,'m/d/YYYY hh:mm:ss'),'MM/DD/YYYY') as Date_Completed
from [$(f_name)] (ooxml, embedded labels , table is //NEED Dynamic or Variable Sheet Name?)
;
endif
next
@mck-mthomas you can pass the empty sheet name as you always have one sheet and data is loaded in first sheet
Data:
Load 0 as Temp
AutoGenerate 0;
for Each vFile in FileList('lib://FilePath/*.xlsx')
if WildMatch('$(vFile)','*GuestPM*') then
Concatenate(Data)
LOAD
*
FROM [$(vFile)]
(ooxml, embedded labels, table is []);
Else
Trace "$(vFile) File Not Required";
EndIf
Next
Drop Field Temp;