How to use "drop table"?

[vchuprina]

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;

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

[krishna_2644]

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

[vchuprina]

I try use your script, but received following issue.

Could you please look at this

Error in expression:

Count takes 1 parameter

Temp1:

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

      Count(DISTINCT TABLE_NAME, '|') as Count

Resident Temp_Tables

[sunny_talwar]

my bad

Temp1:

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

      Count(DISTINCT TABLE_NAME) as Count

[vchuprina]

Sunny thank you for your help, now it works, but shows following issue. It seems that subfield functions doesn't work

Script line error:

let vSheetName = SubField(''BA Detail$'FilterDatabase|'BA Detail$'Print_Titles|'BA Summary$'Print_Titles|'F16 Detail$'|'F16 Detail$'FilterDatabase|'F16 Detail$'Print_Titles|'F16 Summary$'|'F16 Summary$'Print_Titles|'Prior Detail$'FilterDatabase|'Prior Detail$'Print_Titles|'SPIN Data$'FilterDatabase|'Total Recap$'FilterDatabase|'Total Recap$'Print_Area|'Total Recap$'Print_Titles', '|', 1)

[vchuprina]

What is the reason of issue? Maybe qlikview has limited  string length

[sunny_talwar]

Actually I need to think about this a little

[vchuprina]

Sunny, you still haven't any ideas?