Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
One of root directory keeps all excel files and they are organized in a hierarchical structure like:
root --- vendor A --- year2010 -- *.xls
| |
| +-- year 2011 -- *.xlsx
| ...
---vendor B --- year2011 -- *.xls
|
+--year2012 -- *.xls
|
+--year2013 -- *.xlsx
.....
....
How to write a script to load all *.xls or *.xlsx files under root directory, and once a file has been loaded it won't be loaded again?
Thanks,
Josh
I added to the script the store of loaded excel files
SUB DoDir (Root)
FOR Each Ext in 'xlsx'
let filelist='$(Root)'&'\a*.' &'$(Ext)';
FOR Each File in filelist('$(filelist)')
// check file already loaded
tmp: NoConcatenate First 1 LOAD * Resident f where FileName = '$(File)';
if NoOfRows('tmp')=1 then
trace file already loaded;
else
trace file new;
// load excel file
LOAD * from [$(File)] (ooxml, embedded labels, table is Sheet1);
// add file to loaded file
Concatenate(f) load '$(File)' as FileName AutoGenerate 1;
endif;
DROP Table tmp;
NEXT File
NEXT Ext
FOR Each Dir in dirlist (Root&' \*' )
call DoDir (Dir)
NEXT Dir
// store the names of loaded files
STORE f into filelist.qvd (qvd);
ENDSUB
// main, get loaded files
if len(FileSize('filelist.qvd'))> 0 then
f: load * from filelist.qvd (qvd);
ELSE
f: load null() as FileName AutoGenerate 0;
ENDIF;
CALL DoDir ('C:\Users\mgrossi\Downloads')
Take a look at this thread loop through to load all files from a folder and its subfolders?
I think
you can start from this code in the QlikView help (search "for each")
and replace Ext, the load with a load from excel and the CALL
SUB DoDir (Root)
FOR Each Ext in 'qvw', 'qva', 'qvo', 'qvs'
FOR Each File in filelist (Root&' \*.' &Ext)
LOAD
'$(File)' as Name,
FileSize( '$(File)' ) as Size,
FileTime( '$(File)' ) as FileTime
autogenerate 1;
NEXT File
NEXT Ext
FOR Each Dir in dirlist (Root&' \*' )
call DoDir (Dir)
NEXT Dir
ENDSUB
CALL DoDir ('C:')
Thank you all for the solutions, but they only resolve the first part of my question.
How to keep away from a file been loaded multiple time if I run this load script on a root on a regular base.
Thanks,
Josh
I added to the script the store of loaded excel files
SUB DoDir (Root)
FOR Each Ext in 'xlsx'
let filelist='$(Root)'&'\a*.' &'$(Ext)';
FOR Each File in filelist('$(filelist)')
// check file already loaded
tmp: NoConcatenate First 1 LOAD * Resident f where FileName = '$(File)';
if NoOfRows('tmp')=1 then
trace file already loaded;
else
trace file new;
// load excel file
LOAD * from [$(File)] (ooxml, embedded labels, table is Sheet1);
// add file to loaded file
Concatenate(f) load '$(File)' as FileName AutoGenerate 1;
endif;
DROP Table tmp;
NEXT File
NEXT Ext
FOR Each Dir in dirlist (Root&' \*' )
call DoDir (Dir)
NEXT Dir
// store the names of loaded files
STORE f into filelist.qvd (qvd);
ENDSUB
// main, get loaded files
if len(FileSize('filelist.qvd'))> 0 then
f: load * from filelist.qvd (qvd);
ELSE
f: load null() as FileName AutoGenerate 0;
ENDIF;
CALL DoDir ('C:\Users\mgrossi\Downloads')
try this example
LOAD
FIELDA,
FIELDB
FROM
[DATA_*.xls]