Daily Load New Files only

[#1]

Source Folder: c:\sourcedata

ServerLog20160801.txt

ServerLog20160605.txt

ServerLog20160307.txt

ServerLog20160713.txt

// New files will be added few times a week.

How do I write script that allows me to:

First Run:

• During first run loads all files that exist and create ServerLog_Full.qvd.
• Create list of records of files processed in Processed_ServerLog_FilesList.qvd

Daily Incremental Run:

• During daily run read new files that will come in, compare it with files already processed (Processed_ServerLog_FilesList.qvd) and concatenate new data only to ServerLog_Full.qvd also appends newly processed files list to Processed_ServerLog_FilesList.qvd

Thanks

[m_woolf]

Instead of messing with figuring out which files are new, you could create a batch file that runs on successful completion of the load.

That batch file would move all files into a backup folder.

[adamdavi3s]

Something along the lines of this, sorry I've typed it on the fly so its likely to need some tweeking!

//Check for the log qvd
LET logqvdfile = 'C:\Processed_ServerLog_FilesList.qvd';
LET baseQVDFileSize = FileSize('$(logqvdfile )');

IF  baseQVDFileSize > 0 THEN
log:
LOAD filename, loaded
from [C:\Processed_ServerLog_FilesList.qvd] (QVD);

ELSE
ENDIF

//check for the fact qvd

LET factqvdfile = 'C😕ServerLog_Full.qvd';
LET baseFactFileSize = FileSize('$(factqvdfile )');

IF  baseFactFileSize > 0 THEN
log:
LOAD filename, loaded
from [C😕ServerLog_Full.qvd] (QVD);

ELSE
ENDIF

//load in the new files files
for each File in filelist ('c:\sourcedata\*.txt')

test:
LOAD $(File) as filename
AUTOGENERATE 1
WHERE NOT EXISTS filename;
next File;

LET CKNumRows=NoOfRows('test');
FOR p=1 to $(CKNumRows)
  LET vFileChecker=Peek('filename',$(p),'test'); 
    LOAD
blah blah blah
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

NEXT p;

concatenate(log)

LOAD filename, now() as loaded
resident test;

store log into ;
store fact into  ;

drop table test;
drop table log;
drop table fact;

Please remember to mark this as helpful or the correct answer if I have helped you or answered your question.

[#4]

Thanks

Couple of questions:

Where is Fact: table in the script, what will itl load when we do a store.

second. I am getting syntax error where it says WHERE NOT EXIST filename. What filename are we refering to here.

1. test: 
2. LOAD $(File) as filename 
3. AUTOGENERATE 1 
4. WHERE NOT EXISTS filename; 
5. next File;

[adamdavi3s]

I've got a little time this morning so let me whip up a test file for you

[adamdavi3s · Accepted solution]

Sorry got waylay-ed.

I am sure someone could make this more efficient but this works:

Its a cool example for me to blog as well so thanks

Please remember to mark this as helpful or the correct answer if I have helped you or answered your question.

Let vFilePath='E:\';

//Check for the log qvd 
LET logqvdfile = '$(vFilePath)qvds\Processed_ServerLog_FilesList.qvd'; 
LET baseQVDFileSize = FileSize('$(logqvdfile)'); 

IF  baseQVDFileSize > 0 THEN 


TRACE logfile found, loading it;
log: 
LOAD filename, loaded 
from [$(vFilePath)qvds\Processed_ServerLog_FilesList.qvd] (QVD); 

ELSE 
ENDIF 

//check for the fact qvd 

LET factqvdfile = '$(vFilePath)qvds\ServerLog_Full.qvd'; 
LET baseFactFileSize = FileSize('$(factqvdfile)'); 

IF  baseFactFileSize > 0 THEN  //our fact QVD exists already so we are good to go

TRACE factfile found, loading it;
  fact: 
  LOAD filename,
  [1],
      [2],
      [3]
  from [$(vFilePath)qvds\ServerLog_Full.qvd] (QVD); 



  for each File in filelist ('$(vFilePath)sourcedata\*.txt') 

  test:  //preceeding load required for our not exists test
  load filename
  WHERE NOT EXISTS(filename);

  LOAD '$(File)' as filename 
  AUTOGENERATE 1 
  ; 
  next File; 

  LET CKNumRows=NoOfRows('test'); 
  FOR p=0 to $(CKNumRows)  -1
  LET vFileChecker=Peek('filename',p,'test');

  fact: 
  LOAD '$(vFileChecker)' as filename,
  [1],
      [2],
      [3]
  FROM
  '$(vFileChecker)'
  (txt, codepage is 1252, embedded labels, delimiter is ',', msq); 

  NEXT p;

  concatenate(log) 

  LOAD filename, now() as loaded 
  resident test;


drop table test; 


ELSE //our fact QVD doesn't exist already so we can't check filename


TRACE factfile not found so building it;


  for each File1 in filelist ('$(vFilePath)sourcedata\*.txt') 
  fact:
  LOAD '$(File1)' as filename, [1],
      [2],
      [3]
  FROM
  '$(File1)'
  (txt, codepage is 1252, embedded labels, delimiter is ',', msq);

  NEXT File1

TRACE logfile not found so building it;

  log: 
  LOAD filename, now() as loaded 
  resident fact; 





ENDIF




store log into [$(vFilePath)qvds\Processed_ServerLog_FilesList.qvd]; 
store fact into  [$(vFilePath)qvds\ServerLog_Full.qvd]; 




drop table log; 
drop table fact;

[#7]

Thanks for code

I am not planning to have filename inside of my Fact.qvd. can we avoid this.

[#8]

can you please provide bit of explaination on how this code works. Got lost mid way thru. thx

[adamdavi3s]

Hi Hanns,

I added a few more comments on my blog so that might help but let me know which bit in particular you would want extra notes on

http://qlikanddirty.com/2016/08/26/loading-only-new-files-with-logfiles/

There could be a way to work it without the filename but it is always handy for auditing as well. Obviously you don't have to load all fields from a QVD so the only harm in having it is storage space