Read Loop Element into Column

[matthewjbryant]

Hi All,

I am loading multiple sheets from an Excel doc into my report, but need to know which sheet the data comes from. This means I need a column in every row with the sheet name. This is what I'm trying, but it doesn't work:

SET i = JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, DEC;

FOR Each m in $(i);

  LOAD

  text('$(m)') as Month,

  A,

  B

   FROM

   [DOC.xlsx]

   (ooxml, embedded labels, Table is [$(m)]);

NEXT

The data loads, but Month is blank on every row. How can I do this?

[marcus_sommer · Accepted solution]

Your syntax for the variables aren't quite correct - try this:

SET i = "'JAN', 'FEB', 'MAR', 'APR', 'MAY', 'JUN', 'JUL', 'AUG', 'SEP', 'OCT', 'NOV', 'DEC'";

FOR Each m in $(i);

   LOAD

  '$(m)' as Month, rowno() as RowNo,

  A,

  B

   FROM

   [DOC.xlsx]

   (ooxml, embedded labels, Table is [$(m)]);

NEXT

- Marcus

[matthewjbryant]

Hi Marcus Sommer,

Thanks for the reply, but as soon as I place any of the elements past JAN in quotes I get a 'Field not found' error because the sheet cannot be loaded. If I just put JAN in quotes the first 12th of all the row numbers have 'JAN' populated in them.

[marcus_sommer]

Every item in variables-list needs single-quotes and the complete variable needs double-quotes and then it worked - i have tested it.

- Marcus

[jonathandienst]

Hi

Does not need the outer ". This is sufficient:

SET i = 'JAN', 'FEB', 'MAR', 'APR', 'MAY', 'JUN', 'JUL', 'AUG', 'SEP', 'OCT', 'NOV', 'DEC';

Having said that, it works with or without the outer quotes. However, the inner single quotes are necessary.

HTH

Jonathan

[matthewjbryant]

Thanks Jonathan Dienst and Marcus Sommer for trying to help, but the query only works without any quotes - I too have tested it! The query will run with JAN in quotes, but no more than that for some reason. I guess this is the difference between using a set and using a variable.

[marcus_sommer]

For me it worked - I assume there is somewhere a little difference.

- Marcus

[sudeepkm]

Can you please try like below?

set errormode=0; // setting the error mode to 0

// create a table keeping all the possible sheet names

tmpinline:

load * Inline [

sheetname

JAN

FEB

MAR

];

// load the total count of sheet names

LET vSheetCount = NoOfRows('tmpinline');

// load each files in a loop assume that we have book1 and book2.xlsx

for each vFiles in filelist('Book*.xlsx')

    // load each sheet until total sheet counts

     for i = 0 to $(vSheetCount)-1

    // store the sheet name

        let vSheetName = peek('sheetname',$(i),'tmpinline');

        LOAD

      '$(vSheetName)' as sheetname,FileName() as filename,*

      FROM  [$(vFiles)] (ooxml, embedded labels, Table is '$(vSheetName)');

    NEXT i

next vFiles

drop table tmpinline;

[matthewjbryant]

Thanks for your help Marcus. It turns out the main problem was with the spreadsheet. Once I gave them all names and edited my LOAD to match your script worked a treat. Cheers mate!