1 Reply Latest reply: Jan 5, 2016 5:47 PM by Marco Wedel RSS

    loading different field names for different file names in loop

    Martijn Noorda

      Hello everybody,

       

      I would like to start off by wishing you all a happy new year!

       

      My first question of 2016 is about looping using if conditions.

       

      I have multiple csv files from which I would like to load different field names, depending on which csv.

       

      I have written a loop that can load all fields from all csv files by simply using making a table that includes all filenames in the folder and the header sizes per csv file.

       

      for i= 0 to noofrows('Mwoah')-1

       

       

      LET myfilename = peek('SourceFile',$(i),'table1');

      LET headersize = peek('Headersize',$(i),'table1');

       

      load *

       

      from [$(myfilename)]

      (txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is $(headersize) lines);

       

      NEXT

       

      However, I would like to amend this script so that if the filename contains a certain text string, it should load specific fields.

       

      I want to use an if...then clausule for this.

       

      I was thinking about using this:

       

      for i= 0 to noofrows('table1')-1

       

      LET myfilename = peek('SourceFile',$(i),'table1');

      LET headersize = peek('Headersize',$(i),'table1');

       

      IF substringcount($(myfilename),'TRD_ETD_VAL')>0 then

       

      nieuwetabel:

       

      load filename() as Filename,

      [Party 1 Transaction ID] as [Transaction ID],

      [Message Type],

      [Transaction Type],

      Action,

      [UTI Prefix (Pos)],

      [UTI Value (Pos)] as UTI

       

      from [$(myfilename)]

      (txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is $(headersize) lines);

       

      elseif substringcount($(myfilename),'TRD_ETD_TX')>0 then

       

      load filename() as Filename,

      [Party 1 Transaction ID] as [Transaction ID],

      [Message Type],

      [Transaction Type],

      [Action],

      [UTI Prefix (TX)] as [UTI Prefix],

      [UTI Value (TX)] as UTI

       

      from [$(myfilename)]

      (txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is $(headersize) lines);

       

      endif;

       

      NEXT

       

       

       

      When I load this script I get the error message that the line elseif substringcount($(myfilename),'TRD_ETD_TX')>0 then is false. What do I need to do to get this script working? Note that the loop should check whether the filename contains a certain string, and if it does it should load the appropriate file names, if it does not contain the string it should proceed to test whether the filename contains another text string and if it does it should load appropriate file names, if it does not, it should proceed to the next condition and so on, and so forth.