We have a file (edw2iceyyyy-mm-dd-hh-mm-ss) that is loaded to location on daily basis (see attachment).
Is it possible to load multiple files using variables?
For example, I would like to load the most recent 10 files and I wondered if there was a simple way to do this?
Any help would be much appreciated.
Thank you for the responses. I think adding the * solves my issue.
One other question I have, is there a way to either split the below data when loaded or in the qvw file?
I'm trying to show for example; RPBQV, 0000000000012015083141, OXD, 201508 as separate columns.
Try this script:
LOAD SubField(A, ' ', 1) as 1,
SubField(A, ' ', 2) as 2,
SubField(A, ' ', 3) as 3,
SubField(A, ' ', 4) as 4
(ooxml, no labels, table is Sheet1);
FOR EACH file in FileList('filepath\edw2ice*.xlsx');
//In order to get the file information from SQLtables command making use of the ODBC connection format
ODBC CONNECT32 TO [Excel Files;DBQ=$(file)];
FOR i = 0 to NoOfRows('tables')-1
LET sheetName = Peek('TABLE_NAME', i, 'tables');
Load * ,
FileDir() as Dir,
FileName() as File_Name,
'$(sheetName)' as Sheet_name,
SubField(Field,' ', 1) as Field1,
SubField(Field,' ', 2) as Field2,
SubField(Field,' ', 3) as Field3,
SubField(Field,' ', 4) as Field4
From $(file)(ooxml, embedded labels, table is [$(sheetName)]);