How to load source files dynamically?

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY h:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

SET vFilePath = '..\QVD\Source files';
SET vQVDPath = '..\QVD';
LET vCurrentMonth = Text(Date(Today(), 'YYYY-MM'));




IF Alt(FileSize(vQVDPath & '\'& 'Table1.qvd'), 0) = 0 THEN
 Table1:
 LOAD *,
 Date(Floor(Date#(LATEST_DATE, 'DD/MM/YYYY hh:mm'))) AS LATEST_DATE_FORMAT,
 FileName() AS Source_FileName
 FROM
 [$(vFilePath)\*Table1.csv]
 (txt, codepage is 1252, embedded labels, delimiter is ',', msq);
 
 Concatenate(Table1)
 LOAD *,
 Date(Floor(Date#(LATEST_DATE, 'YYYY-MM-DD hh:mm:ss TT'))) AS LATEST_DATE_FORMAT, //2015-12-21 09:00:00 AM
 FileName() AS Source_FileName
 FROM
 [$(vFilePath)\*Table1.dat]
 (txt, codepage is 1252, embedded labels, delimiter is '|', msq, header is 1 lines);
ELSE
 Table1:
 LOAD
 *
 FROM $(vQVDPath)\Table1.qvd (qvd)
 WHERE LATEST_DATE_FORMAT < MonthStart(Today());
 
 
 IF Alt(FileSize('$(vFilePath)' & '\*$(vCurrentMonth)*Table1.dat'), 0) > 0 THEN 
 Concatenate(Table1)
 LOAD
 *,
 Date(Floor(Date#(LATEST_DATE, 'YYYY-MM-DD hh:mm:ss TT'))) AS LATEST_DATE_FORMAT,
 FileName() AS Source_FileName
 FROM
 [$(vFilePath)\*$(vCurrentMonth)*Table1.dat] //Source.2015-12-21_Table1
 (txt, codepage is 1252, embedded labels, delimiter is '|', msq, header is 1 lines);
 ENDIF
 
 IF Alt(FileSize('$(vFilePath)' & '\*$(vCurrentMonth)*Table1.csv'), 0) > 0 THEN 
 Concatenate(Table1)
 LOAD *,
 Date(Floor(Date#(LATEST_DATE, 'DD/MM/YYYY hh:mm'))) AS LATEST_DATE_FORMAT,
 FileName() AS Source_FileName
 FROM
 [$(vFilePath)\*$(vCurrentMonth)*Table1.csv]
 (txt, codepage is 1252, embedded labels, delimiter is ',', msq);
 ENDIF

ENDIF

STORE Table1 INTO $(vQVDPath)\Table1.qvd;
DROP TABLE Table1;
Exit Script;
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.