2 Replies Latest reply: Mar 16, 2015 10:10 AM by Ralf Narfeldt RSS

    Load fields from xlsx files with condition

    Андрей Шепель

      Good day!

       

      I'm loading two xlsx files from directory.

       

      First file have one field Date

      Second file Have one field YearMonth.

       

      I want to make one field Year, by function Year(Date) from first file and Right(YearMonth,4) from second.

       

      My Script:

      SET ThousandSep=' ';

      SET DecimalSep=',';

      SET MoneyThousandSep=' ';

      SET MoneyDecimalSep=',';

      SET MoneyFormat='# ##0,00р.;-# ##0,00р.';

      SET TimeFormat='h:mm:ss';

      SET DateFormat='DD.MM.YYYY';

      SET TimestampFormat='DD.MM.YYYY h:mm:ss[.fff]';

      SET MonthNames='янв;фев;мар;апр;май;июн;июл;авг;сен;окт;ноя;дек';

      SET DayNames='Пн;Вт;Ср;Чт;Пт;Сб;Вс';

      LET vToDay = today(1)-1;

       

      Directory;

       

      FOR Each File in filelist ('*.xlsx')

        Main:

        LOAD

      if(SubStringCount(FileBaseName(),'Date')=1, Year(Date),

            if(SubStringCount(FileBaseName(),'YearMonth')=1, Left(YearMonth,4) )) as Year

       

        FROM

        '$(File)'

      (ooxml, embedded labels, table is Sheet1);

      NEXT File;

       

       

      But in result i have error:

      Field not found <YearMonth>...

       

      Is there any way to solve this problem?

       

      Thanks