How to modify my script , When i need change my raw data file name,I don't need to edit QV script ?

[paulyeo11]

Hi All

I got the script from Staffan , May i know how to modify the below script , so that when i change the raw data file name , i can change it at excel file , with out open and edit QVW file.

  set vFile1 = '$(vRAWPath)Q_Payment_KL';

  SET vFile2 = '$(vRAWPath)Q_Payment_Penang';

  SET vFile3 = '$(vRAWPath)Q_Payment_TID';

Meaning the above script need to change. so that it will refer to XLS file for file name..  

Hope some one can share with me how to do it ?

Thank.

SET vDevelopment = 30;

IF $(vDevelopment) = 0 THEN

ELSEIF $(vDevelopment) = 30 THEN

  SET vRAWPath = 'C:\TEST\'; //local folder

//==============Hardcodedlist====================

  LET vNoOfFiles=3;

  set vFile1 = '$(vRAWPath)Q_Payment_KL';

  SET vFile2 = '$(vRAWPath)Q_Payment_Penang';

  SET vFile3 = '$(vRAWPath)Q_Payment_TID';

//============================================

  else

END IF

for i =1 to vNoOfFiles

For each vFileName in FileList('$(vRAWPath)*.txt')

if(subfield('$(vFileName)','.',1) = '$(vFile$(i))') then

ARSummary:

LOAD left(FileBaseName(), 4) AS Report5_pay,

  FileBaseName() as SOURCE,

  @1:9T as [cust_id],

  @10:38T as [company_pay],

  if(right(@81:99T,1)='-', '-' & left(@81:99T, len(@81:99T)-1),@81:99T) as curren

FROM "$(vFileName)" (ansi, fix, no labels, header is 0, record is line); 

endif

Next vFileName

next i

[paulyeo11]

my QVW

[stabben23]

Hi Paul,

you need to self find a way to sort the files out, we cant help you with that.

Why do you want thoose 3 you have right now for ex? what is the unique with the files you want to load?

And you dont need to start a New thread for all "fix" in the script.

[paulyeo11]

Hi Staffan

Thank you for teach me the correct approach. I will try myself.

As I want to develop the script and in case file name change , user can manage by themself. Also for develop stage I can change the file name with out touch the script directory section . As it will be very Long list of file , using excel is easy maintenance.

Okay I will not start New thread in future also.

Paul Yeo

DIrector

TDS Technology (S) P/L

Whatsapp +659:261804

[stabben23]

Hi Paul,

I can change the script so you can use an Excel list instead. You still need to add New filenames in excellist but not in script.

[stabben23 · Accepted solution]

Here you go. Make sure that the fileName in Your Excel have the same name as they have from where you fetch them.

Q_Payment_TPO is just a dummy from my side.

Start to add all FileName in Excel then just run the script.

[paulyeo11]

Hi Steffan

Wow you are very fast . Take you about 20 mins , For me i need to take may be 20 days.

As i ready don't know where to start. Appreciated very much.

Paul

[stabben23]

Great Paul,

if you work as an consultant you will earn a lot of more Money than me, 20 days against 20 min

[sunny_talwar]

Hahahahah

[paulyeo11]

Hi Staffan

Sorry for started new thread. ( Some people alway ask me start new thread , may be they want more point ).

After i add SET vInputPath = 'C:\TEST\EXCEL'; I get error msg unable to open my excel file.

I want to have diff folder to keep the excel file , as i don't want to mix raw data file and excel file.

The above link i have the detail info which i try but it does not work. Hope you can let me know where the issue ?

Once again thank you for your help.

Paul