topic Re: Loading Excel Files from multiple Folders in QlikView

Loading Excel Files from multiple Folders

rido1421 — Mon, 23 May 2016 09:41:19 GMT

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

Re: Loading Excel Files from multiple Folders

Kushal_Chawda — Mon, 23 May 2016 09:50:35 GMT

All your excel files having single sheet or multiple sheet?

Re: Loading Excel Files from multiple Folders

rido1421 — Mon, 23 May 2016 09:53:28 GMT

Single sheet...

Re: Loading Excel Files from multiple Folders

Mon, 23 May 2016 09:56:07 GMT

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

tamilarasu — Mon, 23 May 2016 10:08:41 GMT

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

Re: Loading Excel Files from multiple Folders

kavita25 — Mon, 23 May 2016 10:11:27 GMT

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;

Re: Loading Excel Files from multiple Folders

rido1421 — Mon, 23 May 2016 10:17:51 GMT

Hi Kavita

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

Re: Loading Excel Files from multiple Folders

Kushal_Chawda — Mon, 23 May 2016 10:19:28 GMT

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

Re: Loading Excel Files from multiple Folders

MindaugasBacius — Mon, 23 May 2016 10:20:22 GMT

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

Kushal_Chawda — Mon, 23 May 2016 10:25:04 GMT

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

Re: Loading Excel Files from multiple Folders

qlikview979 — Mon, 23 May 2016 10:28:10 GMT

Hi,

Can You Share Sample Excel files. Are you having one excel with multiple sheets right?

Re: Loading Excel Files from multiple Folders

MindaugasBacius — Mon, 23 May 2016 10:31:31 GMT

You are correct because it's not built to support multiple sheets.

It was said previously that there is only one sheet in the file

Re: Loading Excel Files from multiple Folders

rido1421 — Mon, 23 May 2016 10:34:19 GMT

Hi Tamil

I get the error. "Cannot open file 'Location of the file' The system cannot find the file specified"

Re: Loading Excel Files from multiple Folders

Kushal_Chawda — Mon, 23 May 2016 10:35:29 GMT

It will not also work if by chance,in some excel sheet name is different

Re: Loading Excel Files from multiple Folders

qlikview979 — Mon, 23 May 2016 10:39:59 GMT

Hi,

when all sheets having same fields

Try this

for each Vpeek in 'Sheet1','Sheet2','Sheet3','Sheet4';

T1:

LOAD ID,

NAME,

NUMBER

FROM

(ooxml, embedded labels, table is $(Vpeek));

NEXT Vpeek;

Re: Loading Excel Files from multiple Folders

MindaugasBacius — Mon, 23 May 2016 10:44:46 GMT

Didn't thought of that.

It have some limitations.

Re: Loading Excel Files from multiple Folders

tamilarasu — Mon, 23 May 2016 12:15:04 GMT

rido1421

Below script seems to be working fine for me. Attached sample file for your reference.

Sub ScanFolder(Root)

For each FileExtension in 'xlsx'
  For Each FoundFile in Filelist( Root & '\*.' & FileExtension) 
  Data: 
  Load  Cars, 
  Date, 
  Value,
  FileName() as FileName 
  FROM [$(FoundFile)] (ooxml, embedded labels, table is Sheet1);     
  Next FoundFile 
NEXT FileExtension
     
For Each SubDirectory in Dirlist( Root & '\*' ) 
  Call ScanFolder(SubDirectory) 
Next SubDirectory 
         
End Sub 

Call ScanFolder('C:\Users\Tamil\Desktop\New folder') ;