1 Reply Latest reply: May 17, 2010 12:43 AM by Zaman Ferdous RSS

    Left Join - multiple files with timestamps in file name

      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?

        • Left Join - multiple files with timestamps in file name

          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