Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Experts,
I have one folder like folder name(TOP5) .this folder having 5 excel sheets names like
2014_04_21.xlsx
2014_04_22.xlsx
2014_04_23.xlsx
2014_04_24.xslx
2014_04_25.xlsx
i need First 3 excels data
Expected output:-
2014_04_23.xlsx
2014_04_24.xslx
2014_04_25.xlsx
here i need this files data.in future if add more sheets i need only first 3;
Ex:-
2014_04_21.xlsx
2014_04_22.xlsx
2014_04_23.xlsx
-
-
-
-
-
-
-
-
2014_05_05.xslx
2014_05_06.xlsx
2014_05_07.xlsx
here i need 2014_05_05.xslx,2014_05_06.xlsx,2014_05_07.xlsx
files data.
In that case, you can use below code
Set vPath = 'C:\Users\Tamil\Desktop\New folder\';
Files:
LOAD Distinct
Date(Date#(FileBaseName(),'YYYY_MM_DD')) as FileDate,
FilePath() as FileName
FROM [$(vPath)*.xlsx]
(ooxml, embedded labels, table is Sheet1);
NoConcatenate
Latest:
First 3 LOAD
FileDate,
FileName
Resident Files order by FileDate Desc;
DROP Table Files;
Data:
LOAD '' as Test AutoGenerate 0;
LET NumRows = NoOfRows('Latest');
FOR i=1 to $(NumRows)
LET vFileName =FieldValue('FileName',$(i));
Concatenate (Data)
LOAD *
From $(vFileName) (ooxml, embedded labels, table is Sheet1);
NEXT
DROP Field Test;
DROP Table Latest;
Use a script like this:
For Each vFile in FileList('<filepath> \*.xlsx')
Sheets:
LOAD FileName() as Filename
From [$(vFile)]
(ooxml, embedded labels, table is Sheet1);
Next
Sheets2:
NoConcatenate
First 3 LOAD Filename
Resident Sheets
Order By Filename DESC;
For i = 0 To NoOfRows('Sheets2') - 1
Let vFile = Peek('Filename', i, 'Sheets2');
Results:
LOAD *
From [$(vFile)]
(ooxml, embedded labels, table is Sheet1);
Next;
DROP Table Sheets;
DROP Table Sheets2;
Set the filepath to the correct path for your source files and adjust the final load (in Results) to your requirements. Note that this script expects all the Excel files to have the same set of fields as it relies on auto-concatenation.
Hi Mahesh,
Try like below,
Set vPath = 'C:\Users\Tamil\Desktop\New folder\';
Files:
LOAD Distinct
Date(Date#(FileBaseName(),'YYYY_MM_DD')) as FileDate,
FilePath() as FileName
FROM [$(vPath)*.xlsx]
(ooxml, embedded labels, table is Sheet1);
NoConcatenate
Latest:
First 3 LOAD
FileDate,
FileName
Resident Files order by FileDate Desc;
DROP Table Files;
LET NumRows = NoOfRows('Latest');
FOR i=1 to $(NumRows)
LET vFileName =FieldValue('FileName',$(i));
LOAD *
From $(vFileName) (ooxml, embedded labels, table is Sheet1);
NEXT
DROP Table Latest;
Sample file attached for your sample data.
Hi Mahesh,
Make sure that Your Excel files are having same number of column with same field name in all excel, other wise above solution will not work.
In that case, you can use below code
Set vPath = 'C:\Users\Tamil\Desktop\New folder\';
Files:
LOAD Distinct
Date(Date#(FileBaseName(),'YYYY_MM_DD')) as FileDate,
FilePath() as FileName
FROM [$(vPath)*.xlsx]
(ooxml, embedded labels, table is Sheet1);
NoConcatenate
Latest:
First 3 LOAD
FileDate,
FileName
Resident Files order by FileDate Desc;
DROP Table Files;
Data:
LOAD '' as Test AutoGenerate 0;
LET NumRows = NoOfRows('Latest');
FOR i=1 to $(NumRows)
LET vFileName =FieldValue('FileName',$(i));
Concatenate (Data)
LOAD *
From $(vFileName) (ooxml, embedded labels, table is Sheet1);
NEXT
DROP Field Test;
DROP Table Latest;
Hi Tamil Nagraj,
Thanks for your Support. I got the output
THanks,
Mahesh
Great. Please mark the correct and helpful answers and close this thread. So that it may helps others in future.
Hi Mahesh,
Iam able to get the execute the recent 3 files. Can you please look into it and let me know if you have any issue.
You just need to change the path of the files in the Call function.
Thanks,
Sreeman
Hi Experts,
Thanks for your Support.
Hello Mahesh,
Could you please the mark the post that solves your problem by clicking on "Correct Answer" icon. So that the thread considered as closed.