Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
jchang_snwl
New Contributor II

auto load all excel files in a root directory

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

1 Solution

Accepted Solutions
MVP
MVP

Re: auto load all excel files in a root directory

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

5 Replies
Not applicable

Re: auto load all excel files in a root directory

MVP
MVP

Re: auto load all excel files in a root directory

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

jchang_snwl
New Contributor II

Re: auto load all excel files in a root directory

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

MVP
MVP

Re: auto load all excel files in a root directory

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

vsudhakar
Contributor III

Re: auto load all excel files in a root directory

try this example

LOAD

     FIELDA,

     FIELDB

FROM

[DATA_*.xls]

Community Browser