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

[Kushal_Chawda]

All your excel files having single sheet or multiple sheet?

[rido1421]

Single sheet...

[#4]

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

[tamilarasu]

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]

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]

Hi Kavita

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

[Kushal_Chawda]

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]

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

[Kushal_Chawda]

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