8 Replies Latest reply: Aug 3, 2015 1:22 PM by Виталий Чуприна RSS

    How to use "drop table"?

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

      Hi guys,

       

      I receive files with the same data, but with different structure and for this reason I use sheetname to determine type of file.

      Script works correct, but it dublicate data. I think it's happening because I don't drop Temp_Tables after first "IF", but when do this I can't use Temp_Tables for the second "if".

       

       

      Please advise some way. What I should change? 

       

      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),''),'#','.');

       

       

      IF(WildMatch((vSheetName),'*DETAIL*') AND NOT WildMatch((vSheetName),'*Print*','*Database*'))  THEN

       

      *

      *

      *

      *

      *

      ELSE

      IF (WildMatch((vSheetName),'*PREFALL*', 'PRE-FALL')) Then

      *

      *

      *

      *

      *

      END IF

      END IF

       

       

      NEXT

       

       

      DROP TABLE Temp_Tables;

        • Re: How to use "drop table"?
          Sunny Talwar

          May be this:

           

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

           

            // Read list of sheets

            Temp_Tables:

            sqltables;

           

          Temp1:

          LOAD Concat(DISTINCT TABLE_NAME, '|') as Concat

                    Count(DISTINCT TABLE_NAME, '|') as Count

          Resident Temp_Tables;

           

          LET vConcat = Chr(39) & Peek('Concat') & Chr(39);

          LET vCount = Peek('Count');

          DROP Tables Temp1, Temp_Tables;

           

          DISCONNECT;

           

           

            // Get just the file name

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

           

            // Enumerate sheets

            for iSheet = 1 to $(vCount)

            let vSheetName = SubField($(vConcat), '|', $(iSheet));

           

            let vSheetName = replace(replace(replace(vSheetName, chr(39), ''), chr(36),''),'#','.');

          • Re: How to use "drop table"?
            Krishna Nagulapally

            try as below

             

            CONNECT TO [Provider=Microsoft.Jet.OLEDB.4.0;Data Source=workbook.xls;Extended Properties="Excel 8.0;"];


            tables: 

            SQLtables;

            DISCONNECT; // Don't need ODBC connection anymore

             

             

            /*

            One of the fields loaded by SQLtables is "TABLE_NAME". This field will contain the sheet name.

            We will loop through this set of sheet names.

            */

            FOR i = 0 to NoOfRows('tables')-1

              /*

              Assign the TABLE_NAME to the variable "sheetName".

              TABLE_NAMEs that contain spaces will be enclosed in single quotes.

              The purgeChar function will remove any quotes.

              */

              LET sheetName = purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39));

              // Optional filtering logic to select certain sheets

              IF wildmatch('$(sheetName)', 'Sales*') THEN  // Only sheetNames that begin "Sales"

              Sales:   

              // Now that we have a sheet name, a standard biff (Excel) load can be used.

              LOAD *,

              '$(sheetName)' as Sheet  // Optionally, the sheetName value may be loaded as a field

              FROM workbook.xls (biff, embedded labels, table is [$(sheetName)]);

              END IF // End of optional sheet filtering

            NEXT

             

             

            DROP TABLE tables;   // The table list is no longer needed