Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have all excel file in one folder.I need to give the folder in one path by giving like * to get the all excel file in Script .
please give me the correct approach to get the full folder in single path by using foreach condition.I have browsed in internet but i couldn't get correct idea.Please help me.
Thanks in advance!!!
First approach as you need using for each
for each vFile in FileList('..\*.xlsx')
TableName:
load
'$(vFile)' as SourceFile,
*
from [$(vFile)]
(ooxml, embedded labels, table is Sheet1);
next
second approach simply
LOAD *
FROM
[Your excel path\*.xlsx]
(ooxml, embedded labels, table is Sheet1);
I have tried like you send but it shows me error file not found but the file is there and in the same path it is located.My code has any error.Please share your comments
Code used:
Directory F:\QVDOCS\QVSOURCEDOCS\test\Dashboard Name\StaticData;
For Each ExcelFile in FileList ('*.xlsx')
StaticDatas:
LOAD *
FROM
[$(XLSPath)Excel Name.xlsx]
(ooxml, embedded labels, table is Internal);
Next ExcelFile;
Store StaticDatas into $(QVDPath)StaticExcel.qvd
As Anil suggested, try using From ..... *.xlsx.
The following has worked for us for years.
Note that I'm adding the filename to the load to provide source tractability and to keep the data unique.
HrsWagesbyProject_Extract:
LOAD
Filename() AS FileName,
Project, // KEY
Hours,
Wages
FROM $(vExternalSourcePath)\ADP - Hours and Wages by Project\*.xls (biff, embedded labels, table is Sheet1$);