Left Join - multiple files with timestamps in file name

[#1]

Hi,

I need to expand the following script to incorporate multiple files which I receive daily. Note that the daily files has Account ID's in it which I want to filter out by doing a Left Join.

LOAD Account_ID,
Client
FROM
[Data.xls](....);

Left Join LOAD
Account_ID,
'31/03/2010' as Date
FROM [CRM\Value_20100331.txt](...);

------

To load the daily files, I changed the last line of script to:
FROM [CRM\Value_*.txt](...);

1. How do I assign the timestamp in the file name to [Date]
2. How do I change the code to ensure that the Left Join function keeps applying with the first file and not continously on eachother as I add daily CRM files?

[#2]

Hi,

You can solve your problem by the following way:

01. Make a folder like C:\CRM and put your all .txt file here. If you want to pick the date from the file name then

give the file name like this: 150510.txt, 160510.txt

02. Write the code like this;

Directory (C:\CRM);

For each TextFile in filelist ('*.txt')

sFileName=right('$(TextFile)',10);

sFileName=left(sFileName,6);

FileDate=makedate('20' & right(sFileName,2),Mid(sFileName,3,2),Left(sFileName,2));

Load

'$(FileDate)' as crm_date,

Account_ID,

Name

From $(TextFile) (txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

Next TextFile;

Left Join LOAD Account_ID,

Addr,

Addr1

FROM

C:\CRM\List.xls

(biff, embedded labels, table is Sheet1$);

03. No need to change script every day.

It may help you,

Thanks

Zaman