Why data is duplicated?

[vchuprina]

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 ...

[marcus_sommer]

It looked that you are loading the data twice. I suggest that you adds filebasename(), $(vSheetName), recno + rowno and a loop-counter to your loads and then you could see from where which data comes and if you expected it this way.

- Marcus