topic Re: Load multiple excel sheets dynamically - script issue in QlikView

Load multiple excel sheets dynamically - script issue

gireesh1216 — Fri, 05 May 2023 06:00:49 GMT

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

Re: Load multiple excel sheets dynamically - script issue

Rohan — Fri, 05 May 2023 06:34:22 GMT

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

Thanks & Regards

Rohan.

Re: Load multiple excel sheets dynamically - script issue

gireesh1216 — Fri, 05 May 2023 06:45:45 GMT

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;

Re: Load multiple excel sheets dynamically - script issue

Rohan — Fri, 05 May 2023 07:22:56 GMT

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.

Re: Load multiple excel sheets dynamically - script issue

gireesh1216 — Fri, 05 May 2023 12:06:09 GMT

Not working in QMC.

Re: Load multiple excel sheets dynamically - script issue

Rohan — Fri, 05 May 2023 12:16:04 GMT

Hi,

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

Re: Load multiple excel sheets dynamically - script issue

gireesh1216 — Fri, 05 May 2023 12:41:20 GMT

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

Re: Load multiple excel sheets dynamically - script issue

marcus_sommer — Mon, 08 May 2023 12:14:50 GMT

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.