topic LOAD Source File, then move it to another folder in QlikView

LOAD Source File, then move it to another folder

Tue, 27 Jan 2015 10:31:14 GMT

Hi,

My script loads data from Excel documents coming in at different times during the day.

Is there a simple way to move these files to another folder once loaded?

I want the source folder to contain new data only. While keeping the already loaded files in an "OLD" folder or similar.

Kind REgards,

Olle

Re: LOAD Source File, then move it to another folder

Tue, 27 Jan 2015 13:34:08 GMT

anyone?

Re: LOAD Source File, then move it to another folder

giakoum — Tue, 27 Jan 2015 13:44:22 GMT

you can use a dos command in your qvw :

EXECUTE robocopy.exe /SEC "\\<your path>" *.qvw /log:"\\>your path>\Logs\ApplicationCopy.txt" /fp

adjust the command according to your needs

Re: LOAD Source File, then move it to another folder

Tue, 27 Jan 2015 14:30:30 GMT

Thanks Ioannis.

Not really sure how in implement though. Can you pls give me an example using the below paths/script?

Thanks in advance,

Olle

PATHS:

Initial folder: C:\Users\xxxxx\Desktop\01.Raw_Data

Move to folder: C:\Users\xxxxx\Desktop\01.Raw_Data\OLD

SCRIPT:

SET vSourceData = '01.Raw_Data\';
SET vSaveQVD = '03.Final_Data\';

if FileSize('$(vSourceData)*.xls')>0 THEN

FINAL_TABLE_EXCEL:
LOAD
currency as CCY,
RIGHT([account no.],8) as ACC,
BIC as BANKNAME,
[value date] as VD,
IF(LEFT([CR/DR],1)='C',amount*-1,amount) as AMOUNT,
reference as REF,
filename() as FILENAME,
IF(status ='N','New transaction',IF(status='U','Updated transaction', IF( status='C', 'Cancelled transaction', IF(status='D', 'Deleted transaction','')))) as [TRANSACTION STATUS],
IF(BIC='DAKVDEFF' and currency = 'EUR','NO', IF(BIC='CEDELULL' and currency = 'EUR','NO','YES')) as [ILM RESPONSIBLE],
IF(RIGHT(filename(),3)='xls','EXCEL') as SOURCE

FROM $(vSourceData)*.xls
(biff, embedded labels, header is 3 lines, table is @1)Where Len(currency)=3;

DateTimeInfo_EXCEL:
LOAD
Timestamp#((DATE(@1, 'MM/DD/YYYY')&' '&TIME(@2, 'hh:mm:ss')),'DD/MM/YYYY hh:mm:ss') As [REPORT DATE]
,FileName() as FILENAME

FROM $(vSourceData)*.xls
(biff, no labels, header is 1 lines, table is @1) Where RecNo() < 2;

JOIN(FINAL_TABLE_EXCEL)
LOAD * RESIDENT DateTimeInfo_EXCEL;
DROP TABLE DateTimeInfo_EXCEL;

STORE FINAL_TABLE_EXCEL into $(vSaveQVD)EXCEL.qvd(qvd);
DROP TABLE FINAL_TABLE_EXCEL;

ENDIF

Re: LOAD Source File, then move it to another folder

giakoum — Tue, 27 Jan 2015 15:38:30 GMT

EXECUTE cmd.exe /C robocopy C:\Users\xxxxx\Desktop\01.Raw_Data C:\Users\xxxxx\Desktop\01.Raw_Data\OLD *.xls /MOV

Re: LOAD Source File, then move it to another folder

maxgro — Tue, 27 Jan 2015 20:32:31 GMT

execute cmd.exe /C move "c:\users\mgrossi\desktop\current\*.*" "c:\users\mgrossi\desktop\old";

Re: LOAD Source File, then move it to another folder

Wed, 28 Jan 2015 09:42:45 GMT

Works great, thanks a lot!