Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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