Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
All your excel files having single sheet or multiple sheet?
Single sheet...
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
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') ;
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;
Hi Kavita
It reloads but doesn't bring any data in...
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') ;
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)\')
I think this will not work in case if new sheet is added (may be blank or any other) in same excel