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
Hi,
Can You Share Sample Excel files. Are you having one excel with multiple sheets right?
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
Hi Tamil
I get the error. "Cannot open file 'Location of the file' The system cannot find the file specified"
It will not also work if by chance,in some excel sheet name is different
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;
Didn't thought of that.
It have some limitations.
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') ;