0 Replies Latest reply: Oct 14, 2014 5:25 AM by safa guedria RSS

    Loop through Excel sheet +crosstable

    safa guedria

      Hello,

       

      I have to load a file Excel having many sheet, each sheet contains the same data.

      I made some transformation to my data:

       

      DataStock:

      CrossTable(Items , DataStock)

      LOAD F1 as DateStock,

        ....

      FROM

      [file.xlsx];

      (ooxml, embedded labels, table is [SEM 20], filters(

      Remove(Col, Pos(Top, 2)),

      Remove(Col, Pos(Top, 1)),

      Remove(Row, Pos(Top, 49)),

      Remove(Row, Pos(Top, 48)),

      Transpose(),

      Transpose(),

      Remove(Col, Pos(Top, 3)),

      Remove(Col, Pos(Top, 21)),

      Transpose()

      ));

       

       

      DataArrivee:

      CrossTable(Items, DataArrivee)

      LOAD

        date(F1) as DateArrivee,

           Adhérents,

           ...

      FROM

      [file.xlsx]

      (ooxml, embedded labels, table is [SEM 20], filters(

      Remove(Col, Pos(Top, 24)),

      Remove(Col, Pos(Top, 23)),

      Remove(Row, Pos(Top, 16)),

      Remove(Row, Pos(Top, 15)),

      Transpose()

      ));

       

      Join (DataTable)

      LOAD

      * Resident DataArrivee;

       

      DROP Table DataArrivee;

       

       

       

      But I want to do this for all the sheets, I have to apply a loop but I can't found how!

       

      I need your help please.

       

      Thank you