Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi ,
There is a folder which contains multiple excel files. Each excel file has data in varying number of tabs. e.g. Excel File A has data in 4 tabs, Excel file B has data in 12 tabs. The number of excel files present in that folder would also vary from time to time.
How do i load data from all these excels with varying number of tabs?
Hi Flipside,
To solve this issue, I've changed the variable :
Let vSheet = replace(left(Peek('TABLE_NAME', i, 'Sheets'), len(Peek('TABLE_NAME', i, 'Sheets'))-1),chr(39)&chr(39),chr(39));
But I have an issue just below when I load my Tab Name fields...
...
LOAD
'$(vSheet)' as [Tab Name],
*
From [$(vFile)] (ooxml, embedded labels, table is $(vSheet));
...
François
I was just about to add (because I found this out too) that you will need to wrap square brackets around the tab name because the quote character will throw an error, so using Anand's example ...
(ooxml, embedded labels, table is [ $(vSheet) ] );
flipside
Hi Francois,
You can try this expression also by create variable if the sheet names and where you get problem in the script.
SET vSheetNames = 'Sheet1', 'Sheet2','Sheet3','a','b','c';
for each vSheetName in $(vSheetNames)
Data:
LOAD *,
'$(vSheetName)' AS SheetName,
FileName() as FileName
FROM
[Mulitple Data.xlsx]
(ooxml, embedded labels, table is [$(vSheetName)]);
NEXT
Regards
Anand
Anand,
I find your solution very good, but in French language, we have many words who contains simple apostrophe (like Chiffre d'affaires)...
Your solution doesn'st work in this case...
Do you have an (great) idea ?
I've attached some files if you want try something.
Thanks,
François
If this not work:
Let vSheet = replace(left(Peek('TABLE_NAME', i, 'Sheets'), len(Peek('TABLE_NAME', i, 'Sheets'))-1),chr(39)&chr(39),chr(39));
Try this:
LET vSheet = PurgeChar(Peek('TABLE_NAME', i, 'Sheets'),chr(39)
Work Ok for me.
Hi Lyudmila,
Thanks for your help !
Have fun with QV,
François