Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

rido1421
Contributor III

Loading Excel Files from multiple Folders

Hi There

I need to be able to load all files from all folders within a specific folder.

I have tried the below piece of script. but when running it returns no data.

I have the folder structure like this.

C:\Users\Rido\Desktop\Test\2016\Jan

my file name is TestData.xlsx and is in the Jan folder

I need to be able to pull all the files from my Test folder no matter the year no matter the month .

 

let vAllPath='C:\Users\Rido\Desktop\Test\*.xlsx';

for each File in filelist (vAllPath)


AllDATA:
LOAD Cars,
Date,
Value
FROM
$(File)
(
ooxml, embedded labels, table is Sheet1);

next File

  Any Assistance is appreciated

16 Replies

Re: Loading Excel Files from multiple Folders

All your excel files having single sheet or multiple sheet?

rido1421
Contributor III

Re: Loading Excel Files from multiple Folders

Single sheet...

Not applicable

Re: Loading Excel Files from multiple Folders

Hi,

There is this bit of code here Loading data from multiple xls-files from folder with subfolder as long as the files all have the same extension .xlsx this seems to work for me and can be modified to suite your needs

Re: Loading Excel Files from multiple Folders

Hi

Try this,

Sub ScanFolder(Root)

For Each FoundFile in Filelist( Root & '\*.' & xlsx)

   Data:

   Load  Cars,

   Date,

   Value

   FROM [$(FoundFile)] (ooxml, embedded labels, table is [Sheet1]);    

  Next FoundFile

      

For Each SubDirectory in Dirlist( Root & '\*' )

   Call ScanFolder(SubDirectory)

Next SubDirectory

        

End Sub

Call ScanFolder('C:\Users\Rido\Desktop\Test') ;

kavita25
Valued Contributor

Re: Loading Excel Files from multiple Folders

Try This.. Hope it helps you.



Directory 'C:\Users\Rido\Desktop\Test;

For each File in filelist ('*.xlsx')


AllDATA:
LOAD Cars,
Date,
Value
FROM
$(File)
(
ooxml, embedded labels, table is Sheet1);

next File;



rido1421
Contributor III

Re: Loading Excel Files from multiple Folders

Hi Kavita

It reloads but doesn't bring any data in...

Re: Loading Excel Files from multiple Folders

LET vQVDFilePath ='..\QVD';

sub ScanFolder(Root)

for each FileExtension in 'xlsx'

for each FoundFile in filelist( Root & '\*.' & FileExtension)

ODBC CONNECT TO [Excel Files;DBQ=$(FoundFile)];

Temp:

LOAD *;

SQLtables;

DISCONNECT;

Data:

LOAD * INLINE [

junk ];

FOR i = 0 TO NOOFROWS('Temp')-1

LET vSheetName = PURGECHAR(PURGECHAR(PEEK('TABLE_NAME', i, 'Temp'), CHR(39)), CHR(36));

CONCATENATE(Data)

LOAD  *,

      FILEBASENAME() AS FileName,

     '$(vSheetName)' AS Sheet_name

FROM $(FoundFile)(ooxml, embedded labels, table is [$(vSheetName)]);

NEXT i

DROP TABLE Temp;

DROP FIELD junk;

                                 

next FoundFile

next FileExtension

for each SubDirectory in dirlist( Root & '\*' )

      call ScanFolder(SubDirectory)

next SubDirectory

Set ErrorMode=0;

Drop Field A;   

Set ErrorMode=1;

STORE Data into $(vQVDFilePath)\FullData.qvd;

DROP Table Data;

end sub

Call ScanFolder('D:\Root') ;

MindaugasBacius
Valued Contributor II

Re: Loading Excel Files from multiple Folders

I am using these statements:

LET path = 'your path';

SUB DoDir(Root)

FOR each File in filelist( Root & '\*.xlsx')

  Data:

  LOAD *

  FROM

  [$(File)]

  (ooxml, no labels)

  ;

NEXT File

FOR each Dir in Dirlist (Root&'\*')

     CALL DoDir(Dir)

NEXT Dir

END SUB

CALL DoDir('$(path)\')

Re: Loading Excel Files from multiple Folders

I think this will not work in case if new sheet is added (may be blank or any other) in same excel