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 ...
Press LIKE if the given solution helps to solve the problem.
If it's possible please mark correct answers as "solutions" (you can mark up to 3 "solutions").
