Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
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
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.
Every item in variables-list needs single-quotes and the complete variable needs double-quotes and then it worked - i have tested it.
- Marcus
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
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.
For me it worked - I assume there is somewhere a little difference.
- Marcus
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;
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!