Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
gireesh1216
Creator II
Creator II

Load multiple excel sheets dynamically - script issue

Hello Team,

I am trying load multiple excel sheets via single excel file dynamically, below is the script is working fine in my local machine and same not working in QMC.

Could you please help me why the below script is not working on QMC and script is exiting automatically after FOR EACH file script in QMC.

Note: Loading excel file from SharePoint location

SCRIPT:

FOR EACH file in FileList('filepath\*.xlsx');

//In order to get the file information from SQLtables command making use of the ODBC connection format
ODBC CONNECT32 TO [Excel Files;DBQ=$(file)];


tables:
SQLtables;
DISCONNECT;

FOR i = 0 to NoOfRows('tables')-1
LET sheetName = purgeChar(purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39)), chr(36));
Table:
Load * ,
FileBaseName()as FIle,
FileDir() as Dir,
FileName() as File_Name,
'$(sheetName)' 
as Sheet_name
From $(file)(ooxmlembedded labelstable is [$(sheetName)]);

NEXT i

 

7 Replies
Rohan
Specialist
Specialist

Can you post the actual error you are getting in the logs from QMC?

 

Thanks & Regards

Rohan.

gireesh1216
Creator II
Creator II
Author

Not getting any error, after for each script automatically ending the script. It is not connecting to ODBC connection. 

 

Log file script:

FOR EACH file in FileList('filepath\*.xlsx');

Exist script;

 

 

Rohan
Specialist
Specialist

Hi,

try this :

for each file in filelist ('filepath'&'/*.xlsx')

let sheet='***';

LOAD
*
FROM [$(file)]
(ooxml, embedded labels, table is $(vsheet));

next file;

 

Thanks & Regards,

Rohan.

gireesh1216
Creator II
Creator II
Author

Not working in QMC.

Rohan
Specialist
Specialist

Hi,

check if your service user has the access to the required folder.

gireesh1216
Creator II
Creator II
Author

Yes able to run the Excel file in QMC but not working ODBC connection.

marcus_sommer

You couldn't apply file-functions against a SharePoint location. Therefore I assume you mean something else respectively a local Onedrive synchronization of the SharePoint content. This means further you need to check at first if the storage-location is available for the QMC machine + user. Just comment the entire load-statement from the loop and add TRACE $(file); to see if the path and the specified file-pattern is recognized.

Beside this there must be the right ODBC driver be installed + configured on the QMC machine + user to be able to perform such kind of load-statement.