Loading Excel Files from multiple Folders

[rido1421]

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

[qlikview979]

Hi,

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

[MindaugasBacius]

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

[rido1421]

Hi Tamil

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

[Kushal_Chawda]

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

[qlikview979]

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;

[MindaugasBacius]

Didn't thought of that.

It have some limitations.

[tamilarasu]

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