duplicate entries when reading (3) worksheets in workbook

[#1]

Can someone please explain why the script below when executed, generates duplicate entries in the first table for a workbook with (3) different worksheets?  The data for the 2nd and 3rd tables all appear to be fine.

ODBC CONNECT32 TO [Excel Files;DBQ=M:\excel\Closed incidents requests tasks.xlsx];

tables:

SQLTables;

DISCONNECT;

Tables1:

load Distinct *,

replace(TABLE_NAME,chr(39),'') as NEW

resident tables;

DROP Table tables;

let var=NoOfRows('Tables1');

FOR i = 0 to $(var)-1

let sheetName=subfield(peek('NEW', i,'Tables1'),'$',1);

[$(sheetName)]:

NOCONCATENATE

LOAD Distinct *

FROM



(ooxml, embedded labels, table is [$(sheetName)]);

NEXT i

DROP Table Tables1;

[settu_periasamy]

script seems to be fine. Which one is your Table1?

Can you post your excel workbook with dummy data?

[#3]

Yes, the name of Table1 changes as the script loops through the workbook.

For some odd reason the data in the first table has duplicates but if I reduce the number of tables from (3) to (2) everything works fine.  Let me work or uploading the source spreadsheet with some dummy data. (See attachment)

[ziadm]

Hi Load Distinct * will result in loading the distinct values of each column in the loading order

This should return a distinct rows but not a distinct field value

LOAD  DISTINCT Country,CustomerName,City FROM Customers;

The result

Country	CustomerName	City
Argentina	Cactus Comidas para llevar	Buenos Aires
Argentina	Océano Atlántico Ltda.	Buenos Aires
Argentina	Rancho grande	Buenos Aires
Austria	Ernst Handel	Graz
Austria	Piccolo und mehr	Salzburg
Belgium	Maison Dewey	Bruxelles
Belgium	Suprêmes délices	Charleroi
Brazil	Comércio Mineiro	São Paulo
Brazil	Familia Arquibaldo	São Paulo
Brazil	Gourmet Lanchonetes	Campinas
Brazil	Hanari Carnes	Rio de Janeiro
Brazil	Que Delícia	Rio de Janeiro
Brazil	Queen Cozinha	São Paulo
Brazil	Ricardo Adocicados	Rio de Janeiro
Brazil	Tradição Hipermercados	São Paulo
Brazil	Wellington Importadora	Resende
Canada	Bottom-Dollar Marketse	Tsawassen
Canada	Laughing Bacchus Wine Cellars	Vancouver

if this is not what you want then you have to load

Load Distinct FieldName

[#5 · Accepted solution]

Ok...I suppose that I shouldn't be using LOAD DISTINCT as this isn't what I want.  Take a look at the attached sample workbook which should help clarify what I'm attempting.  Please note, each tab typically contains somewhere from 1K to 10K records.

[settu_periasamy]

I  think you are talking about this one

When you debug, you can see sheet name [_xlnm#_FilterDatabase] table.

you can simply ignore this.. like

if not WildMatch(sheetName,'*xlnm*') then

the entire script would be

ODBC CONNECT32 TO [Excel Files;DBQ=M:\excel\Closed incidents requests tasks.xlsx];
tables:

SQLTables;

DISCONNECT;

Tables1:

load Distinct *,

replace(TABLE_NAME,chr(39),'') as NEW

resident tables;

DROP Table tables;

let var=NoOfRows('Tables1');

FOR i = 0 to $(var)-1

let sheetName=subfield(peek('NEW', i,'Tables1'),'$',1);

if not WildMatch(sheetName,'*xlnm*') then

[$(sheetName)]:

NOCONCATENATE

LOAD Distinct *

FROM



(ooxml, embedded labels, table is [$(sheetName)]);

ENDIF

NEXT i

DROP Table Tables1;

[#7]

Thanks for your assistance with this issue.  It's greatly appreciated.