Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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),''),'#','.');
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
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
my bad
Temp1:
LOAD Concat(DISTINCT TABLE_NAME, '|') as Concat,
Count(DISTINCT TABLE_NAME) as Count
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)
What is the reason of issue? Maybe qlikview has limited string length
Actually I need to think about this a little
Sunny, you still haven't any ideas?