How to load multiple tabs with one xls file

[prma7799]

Hello All,

How to load multiple tabs with one xls file.

PFA...

[arulsettu]

check this

Load Multiple excel sheets using For loop

[prma7799]

Hi Arul,

I already tried this but i am getting error.

Thanks

[arulsettu]

what error

[prma7799]

Cannot locate table in BIFF file

Concatenate(NPL)

LOAD * FROM

(biff, embedded labels, table is  [V 1] )

[jagan]

Hi,

Try like this

Data:

LOAD RecNo() AS ID,

  [Inv Date],

     Month    

FROM

[NPL.xls]

(biff, embedded labels, table is V1$);

LEFT JOIN(Data)

LOAD RecNo() AS ID,

  Total,

     Apr,

     May

FROM

[NPL.xls]

(biff, embedded labels, table is V2$);

LEFT JOIN(Data)

LOAD RecNo() AS ID,

     Jun,

     Jul,

     Sep,

     Oct,

     Nov,

     Dec,

     Jan,

     Feb,

     Mar

FROM

[NPL.xls]

(biff, embedded labels, table is V3$);

Hope this helps you.

Regards,

jagan.

[prma7799]

Hello Jagan,

This is again we are fetching data manually for all tabs.

We can't achieve  this in one load statements as you mention here Load Multiple excel sheets using For loop  ?

[jagan]

HI,

Try this script

LET vStartSheetNumber = 2;

LET vEndSheetNumber = 3;

Data:

LOAD RecNo() AS ID,

  [Inv Date],

     Month    

FROM

[NPL.xls]

(biff, embedded labels, table is V1$);

FOR index = vStartSheetNumber TO vEndSheetNumber

LEFT JOIN(Data)

LOAD RecNo() AS ID,

  *

FROM

[NPL.xls]

(biff, embedded labels, table is [V$(index)$]);

NEXT

[prma7799]

again same error  come

annot locate table in BIFF file

NPL:

LOAD RecNo() AS ID,

  [Inv Date],

     Month   

FROM

(biff, embedded labels, table is V1$)

[settu_periasamy]

Hi,

Another One..

DIRECTORY;
LET vStartSheetNumber = 1;
LET vEndSheetNumber = 3;
LET vExcelFileName = 'NPL';

// Generate Empty table
NPL:
LOAD RecNo() as Key,
          '' AS [Inv Date],
  '' AS Month
AutoGenerate(0);

FOR index = vStartSheetNumber TO vEndSheetNumber
  if index=1 then
  JoinType='Concatenate'
  ELSE
     JoinType='Left Join'
    ENDIF
    $(JoinType)(NPL)
    LOAD RecNo() as Key,* FROM
    [$(vExcelFileName).xls](biff, embedded labels, table is V$(index)$);
NEXT