topic auto load all excel files in a root directory in QlikView

auto load all excel files in a root directory

Anonymous — Wed, 24 Feb 2016 19:16:42 GMT

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

Re: auto load all excel files in a root directory

Wed, 24 Feb 2016 19:45:12 GMT

Take a look at this thread loop through to load all files from a folder and its subfolders?

Re: auto load all excel files in a root directory

maxgro — Wed, 24 Feb 2016 19:50:47 GMT

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:')

Re: auto load all excel files in a root directory

Anonymous — Wed, 24 Feb 2016 21:05:40 GMT

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

Re: auto load all excel files in a root directory

maxgro — Wed, 24 Feb 2016 22:48:01 GMT

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')

Re: auto load all excel files in a root directory

Anonymous — Thu, 25 Feb 2016 05:11:20 GMT

try this example

LOAD

FIELDA,

FIELDB

FROM

[DATA_*.xls]