Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Folks,
Is this right way of defining a loop????
For each vSheet in 'OEE','Line 1','Line 2','Line 3'
Plant:
LOAD
left(B,3) as Plant
FROM
[amit.xlsx]
(ooxml, no labels, table is [Data $(vSheet)])
where RowNo() < 1;
Thanks,
AS
Then try like:
For each vSheet in '(OEE)',('Line 1)',('Line 2)','(Line 3)' // parenthesis
Plant:
LOAD
left(B,3) as Plant
FROM
[amit.xlsx]
(ooxml, no labels, table is [Data$(vSheet)]) // No space
where RowNo() < 1;
NEXT vSheet
You are missing a 'NEXT' at the end.
You missed the NEXT at the end
For each vSheet in 'OEE','Line 1','Line 2','Line 3'
Plant:
LOAD
left(B,3) as Plant
FROM
[amit.xlsx]
(ooxml, no labels, table is [Data $(vSheet)])
where RowNo() < 1;
NEXT
Hi Tresesco,
I do have Next at the bottom which I missed , My concern here whether this [Data $(vSheet)] is fine or not????
What is Data for?
Is your sheet name Data Line 1? If your sheet name is simply OEE, Line 1, Line 2... etc.
Use
For each vSheet in 'OEE','Line 1','Line 2','Line 3'
Plant:
LOAD
left(B,3) as Plant
FROM
[amit.xlsx]
(ooxml, no labels, table is [$(vSheet)])
where RowNo() < 1;
NEXT vSheet
It seems fine. You might check for appropriation of space in the sheet name.
Yes it will work, just try it and you'll see. On condition that the sheets are named 'Data OEE', 'Data Line 1' and so on.
What happens is this: before each successive execution of the LOAD statement, the script engine will pre-scan the text of the LOAD statement (including the table name and semicolon), and replace all occurrences of dollar substitution strings with their respective values. The funny thing is, you can replace everything with a $(name) tag, even the table name, the LOAD keyword and the semicolon. Not that it serves any use
Peter
I'm having one excel sheet with name as Data(OEE), Data(Line 1)....and so on
Thanks
AS
Use
For each vSheet in 'OEE','Line 1','Line 2','Line 3'
Plant:
LOAD
left(B,3) as Plant
FROM
[amit.xlsx]
(ooxml, no labels, table is [Data($(vSheet))])
where RowNo() < 1;
NEXT vSheet
Then try like:
For each vSheet in '(OEE)',('Line 1)',('Line 2)','(Line 3)' // parenthesis
Plant:
LOAD
left(B,3) as Plant
FROM
[amit.xlsx]
(ooxml, no labels, table is [Data$(vSheet)]) // No space
where RowNo() < 1;
NEXT vSheet