Load multiple excel sheets dynamically - script issue

[gireesh1216]

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)(ooxml, embedded labels, table is [$(sheetName)]);

NEXT i

[Rohan]

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

Thanks & Regards

Rohan.

[gireesh1216]

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]

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]

Not working in QMC.

[Rohan]

Hi,

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

[gireesh1216]

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.