script

[Anonymous]

Hello

Script logic

please help me in this regard.

[Anonymous]

any suggestions?

[tamilarasu]

Hi John,

Check the below link. You might get some idea.

Loading from multiple Excel files and multiple sheets

[manojkulkarni]

Two ways to get the latest file.

1. If file is getting created on weekly basis, then while loading you can take file having max created date in that period.

Load all the file, store period & excel create time to identify maximum

2. Get only numbers like 12 from P1 W2 Finance.xls and store in one column & next column will be your filename. Then take maximum of that.

You table will have data like below

Period Week FileName

1         2       P1 W2 Filename

1         3       P1W3 Filename

etc

[Anonymous]

Manoj,

Thanks for the response

I think you have misunderstand my requirement

if p1week1, p1week2,p1week3 data i want to store only p1week3 data not all the data because p1week3 consists of week1 and week2's data.

[Anonymous]

Hello thanks for the response.

I am looking into it but couldnot able to figure out.

[vikasmahajan]

Check with this Kush has given nice script

Load excel files dynamic and check whether excel file is changed or modify if yes load into qvd

HTH

Vikas

[tamilarasu]

Fine. Check the below one.

// Change the path
Set vPath = 'C:\Users\Tamilarasu.Nagaraj\Desktop\New folder (4)';

For Each vFile in Filelist('$(vPath)\*.xlsx')

Let vFileBase =  Replace(subfield('$(vFile)','\',SubStringCount('$(vFile)','\')+1),'.xlsx','');

Temp:
LOAD Mid('$(vFileBase)',5,1) as WeekNumber,
Mid('$(vFileBase)',2,1) as PeriodNumber,
subfield('$(vFile)','\',SubStringCount('$(vFile)','\')+1) as FileName
AutoGenerate 1;

Next vFile

Max:
LOAD
Max(WeekNumber) as MaxWeek,
Max(PeriodNumber) as MaxPeriod
Resident Temp;

LET vMaxWeek = peek('MaxWeek',-1,'Max');
LET vMaxPeriod = peek('MaxPeriod',-1,'Max');
DROP Tables Max,Temp ;

Let vMaxWeekile = 'P'& vMaxPeriod & ' W' & vMaxWeek & ' - Financials.xlsx';
Let vSheetName = 'Week ' & vMaxWeek;

Data:
LOAD [Client Reference],
     [Previous Weeks Total Incurred],
     [Current Total Incurred],
     [Incurred Movement],
     [Current Net Paid],
     [Current Remaining Reserve],
     [CL Claim Reference],
     [Accident Date],
     [Driver Name],
     Garage,
     Route,
     Type,
     [Incurred Status]
FROM
[$(vMaxWeekile)]
(ooxml, embedded labels, header is 2 lines, table is [$(vSheetName)]);

Store Data into 'Backup_P'$(vMaxPeriod)'W'$(vMaxWeek).qvd (qvd);

[tamilarasu]

I Guess, you have deleted the attachments for confidential reason. So, I have also deleted your attachments (Samples) from my reply.

[PradeepReddy]

check the attachment, it might be helpf