Problem loading multiple Excel worksheets

[#1]

I am trying to load mutiple worksheets from the same Excel spreadsheets.  I can see that the lines are being fetched properly however, the first worksheet links correctly and subsequent ones do not.  If I comment out the top spreadsheet then the next spreadsheet works fine but not subsequent ones.  Basically, only the first load works all others do not.  I have no idea what to try.  I have copied an example below.  Thanks for any help!

Oh, on QV v. 10 and Excel 2010.  myTable contains a corresponding %Key1

LEFT JOIN (myTable)

LOAD

    field1   AS [%Key1],

    field2,

    field3

FROM

    (ooxml, embedded labels, table is [worksheet1]);

LEFT JOIN (myTable)

LOAD

    field1   AS [%Key1],

    field2,

    field3

FROM

    (ooxml, embedded labels, table is [worksheet2]);

LEFT JOIN (myTable)

LOAD

    field1   AS [%Key1],

    field2,

    field3

FROM

    (ooxml, embedded labels, table is [worksheet3]);

[#2]

Are you doing the left join for some reason?

Why don't you just make a table concatenating all the excel files...

Excel:

LOAD

    field1   AS [%Key1],

    field2,

    field3

FROM

    (ooxml, embedded labels, table is [worksheet1]);

Concatenate

LOAD

    field1   AS [%Key1],

    field2,

    field3

FROM

    (ooxml, embedded labels, table is [worksheet2]);

Concatenate

LOAD

    field1   AS [%Key1],

    field2,

    field3

FROM

    (ooxml, embedded labels, table is [worksheet3]);

[#3]

Concatenate works ( I tried something similar just after I wrote this, thanks!), but still curious why the left join would not.  Either should be a viable option.

What I did:

FOR EACH vSheet IN 'worksheet1', 'worksheet2', 'worksheet3'

    Excel:

    LOAD

           field1   AS [%Key1],

              field2,

              field3

    FROM

        (ooxml, embedded labels, table is $(vSheet));

NEXT;

[#4]

Maybe because when you do the 1st join, your result table has field2 and field3.

The next join, doesn't add any fields to the result table.

For instance

my table has %Key1 with 1

Sheet1 has

field1 field2 field3

1        1       1

Sheet 2 has

field1 field2 field3

  1       2      2

After first join, result is

1      1      1

When you make the 2nd join, left join searches for the combination 1   1    1 can't find any matches.

And even if find a match, won't be adding any lines.

[mdmukramali]

Dear,

if you want to load multiple worksheets from the same excel sheet then you can use for loop syntax

like

FOR  loop=1 to 3

LOAD Sno as Snumber,

     Sname,

     Amount

FROM

[MULTIPLE SHEETS.xlsx]

(ooxml, embedded labels, table is Sheet$(loop));

NEXT;

hope this one will help you.

[Anonymous]

Adding to the above from Frederico:
Unlike SQL, where you specify explicitly on which fields to join, in QV it is different - join is happening on ALL common fields, in this case on field1(%Key1), field2, and field3.