1 Reply Latest reply: Jul 29, 2015 7:42 AM by Marcus Sommer RSS

    Why data is duplicated?

    Виталий Чуприна

      Hi guys,

       

      Can you please help.

      I have two types of files. In one file I have 3 sheets and data from second and third sheets is duplicated.  Data for this file should load by second tab, but it duplicated when I have file that loaded by first tab.


      Also i tried to connect to ODBC and use IF else, but had the same result


      How can I resolve this issue.

       

      First tab

       

      FOR each vFile in Filelist ('$(vDataFolder)\*.xls')

       

      // connect to eaach Excel file

        // sqltables seems to add a random $ sign and single quotes

       

       

      ODBC CONNECT32 TO [Excel Files;DBQ=$(vFile)];

       

        // Read list of sheets

        Temp_Tables:

        sqltables;

       

       

      DISCONNECT;

       

       

        // Get just the file name

        let vFileName = mid(vFile, index(vFile, '\', -1) + 1);

       

        // Enumerate sheets

        for iSheet = 0 to NoOfRows('Temp_Tables') - 1

        let vSheetName = peek('TABLE_NAME', iSheet, 'Temp_Tables');

       

        let vSheetName = replace(replace(replace(vSheetName, chr(39), ''), chr(36),''),'#','.');   // sqltables seems to add a random $ sign and single quotes

       

       

      IF ((WildMatch(upper(vSheetName),'*PREFALL*','PRE-FALL', 'BA', 'FALL')) AND NOT WildMatch((vSheetName),'*Print*','*Database*', '*Detail*'))  THEN

       

      //*******************************

      // Get Header row number

      //******************************

      T1:

      LOAD @1,

           rowno() AS rownumber

      FROM

      [$(vFile)]

      (biff, no labels, table is [$(vSheetName)$]);

       

       

      NoConcatenate

      T2:

      LOAD * , 1 AS one

      Resident T1

      WHERE WildMatch(@1,'*VNDR*')

      ;

       

      Drop Table T1;

       

       

      Let vRowNum = peek('rownumber',0,'T2')-1;

      Drop Table T2;

       

      //******************************

      // Get Detail Data

      //******************************

      Data:

      LOAD

      .

      .

      .

      .

      .

         

      FROM

      [$(vFile)]

      (biff, embedded labels, header is $(vRowNum) lines, table is [$(vSheetName)$])

      WHERE NOT ISNULL([VNDR STYLE] ) AND TRIM([VNDR STYLE]) <> '' AND  [VNDR STYLE] <> 'TOTAL' AND NOT ISNULL([COLOR]) AND TRIM([COLOR]) <> ''

      ;

       

       

      END IF

       

       

      next

       

      DROP TABLE Temp_Tables;

      next

      FOR each vFile in Filelist ('$(vDataFolder)\*.xls')

       

       

      // connect to eaach Excel file

        // sqltables seems to add a random $ sign and single quotes

       

       

       

       

      Second tab

      ODBC CONNECT32 TO [Excel Files;DBQ=$(vFile)];

       

        // Read list of sheets

        Temp_Tables:

        sqltables;

       

       

      DISCONNECT;

       

       

        // Get just the file name

        let vFileName = mid(vFile, index(vFile, '\', -1) + 1);

       

        // Enumerate sheets

        for iSheet = 0 to NoOfRows('Temp_Tables') - 1

        let vSheetName = peek('TABLE_NAME', iSheet, 'Temp_Tables');

       

        let vSheetName = replace(replace(replace(vSheetName, chr(39), ''), chr(36),''),'#','.');   // sqltables seems to add a random $ sign and single quotes

       

      IF( (WildMatch(upper(vSheetName),'*Detail*')) AND NOT WildMatch((vSheetName),'*Print*','*Database*','*Summary*'))  THEN

      //*********************************

      // Get Header Row To Start At

      //*********************************

      Temp:

      LOAD @1,

           @2,

           @3,

           @9,

           RowNo() AS RowNumber

          

          

      FROM

      [$(vFile)]

      (biff, no labels, table is [$(vSheetName)$]);

       

       

      T2:

      NoConcatenate

      LOAD * Resident Temp

      WHERE WildMatch(@2,'*Vendor Style*')

      ;

       

      DROP Table Temp;

       

       

      LET vRowStart = peek('RowNumber',0,'T2');

       

      DROP TABLE T2;

       

      //************************************

      //   Get the totals

      //************************************

      Data:

      LOAD Distinct

      .

      .

      .

      .

       

      FROM

      [$(vFile)]

      (biff, no labels, Header is $(vRowStart), table is [$(vSheetName)$])

      Where @1 <> 'GRAND TOTAL'  AND ISText(@9) AND @10 <> 'Cls';

       

       

      END IF

      next

       

      DROP TABLE Temp_Tables;

      next

       

      Store data into ...