Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
maxgro
MVP
MVP

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

View solution in original post

5 Replies
Not applicable
Author

maxgro
MVP
MVP

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

Anonymous
Not applicable
Author

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

maxgro
MVP
MVP

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

Anonymous
Not applicable
Author

try this example

LOAD

     FIELDA,

     FIELDB

FROM

[DATA_*.xls]