Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hi all,
the following code is looping excel files in a folder and sub folder,
however, it is difficult to set the variable of the worksheet name.
(for each vSheet in 'a','b,'c*','d','e','f'.....) because the worksheet name is dynamic
other than legacy mode, and odbc connect (not work in the qliksense) <<< I try several days
any idea of this, thank you.
code:
-------------------------------------------------------------------------
sub DoDir (Root)
For Each Ext in 'xls'
For Each File in filelist (Root&'\*.' &Ext)
for each vSheet in 'a','b,'c*','d','e','f'.....
Table:
LOAD
*,
FROM $(File)(biff, embedded labels, header is 3 lines,table is $([vSheet]$)));
next vSheet
Next File
Next Ext
For Each Dir in dirlist (xxxx)
Call DoDir (Dir)
Next Dir
End Sub
Call DoDir (xxxxx)
I suggest you to take a look at Loading Multiple Excel Sheets Dynamically along with file name and sheet name
Try Something like below
Create a procedure
Sub IsUrlAvailable(vTempUrl,vTabNo)
LET vTempErrorMode=ErrorMode;
SET ErrorMode=0;
SET vTableNotFound='Table Not Found';
SET vIsFileNotFound=0;
SET vTempTableName='TEMP_T1';
$(vTempTableName):
First 3
NOCONCATENATE LOAD
*
FROM
[$(vTempUrl)]
(biff, embedded labels, table is '@$(vTabNo)');
LET vIsFileNotFound=(ScriptError='$(vTableNotFound)');
DROP Table '$(vTempTableName)';
SET ErrorMode=$(vTempErrorMode);
END SUB
SET vFile='YourFile.xls';
For i=1 to 100
call IsUrlAvailable('$(vFile)',$(i))
IF Not $(vIsFileNotFound) Then
LOAD
*
FROM '$(vFile)'
(biff, embedded labels, table is '@$(i)');
END IF
Next i;
hth