topic Re: Loading multiple excel files from One Drive location in App Development

Loading multiple excel files from One Drive location

Emil_bros — Fri, 20 May 2022 06:19:06 GMT

Hi Colleagues,

I have a strange issue with loading multiple excel files using One Drive connection (local one drive folder). All files have same format and layout but different names.

My script:

for each File in ('lib://OneDrive_location/*.xlsm')

VB23:
LOAD
2023 as Year,
Country,
Value
from $(File) (ooxml, embedded labels, header is 15 lines, table is VB2023)
next File
;

as a result I get error message that Country field can't be found - don't know what can be the reason as in my understanding script looks fine and should work.

Thank you in advance,

Emil

Re: Loading multiple excel files from One Drive location

Sra1bandi — Fri, 20 May 2022 06:23:56 GMT

Check in the excel file whether the country name is same as in Qlik script or not?

Re: Loading multiple excel files from One Drive location

Emil_bros — Fri, 20 May 2022 06:26:10 GMT

yes, I've checked that. It's not the problem with field names for sure - I have tried to load other fields in first place but with same result - always first loaded field had an error message

Re: Loading multiple excel files from One Drive location

Sra1bandi — Fri, 20 May 2022 06:30:35 GMT

If you have all the old files also please check the country name with upper and lower cases in all the files

because i am faced same issue

in one of the file the name is changed when i am renamed then it is worked fine

Re: Loading multiple excel files from One Drive location

Emil_bros — Fri, 20 May 2022 06:36:08 GMT

fair point and thank you however it's not the case - I have checked naming. I have also tried to load each file separately and it works this way so names and formats are identical in all the files.

Re: Loading multiple excel files from One Drive location

Sra1bandi — Fri, 20 May 2022 06:40:57 GMT

Other wise check with the sheet names which are present in Excel file

may be sheet name mismatch also it will possible to get the similar error

Re: Loading multiple excel files from One Drive location

Emil_bros — Fri, 20 May 2022 06:49:36 GMT

again, that's not the case - I have loaded all files separately using same script and all was fine

Re: Loading multiple excel files from One Drive location

vinieme12 — Fri, 20 May 2022 07:57:35 GMT

You can load all xlsm files without looping

Load *

FROM 'lib://OneDrive_location/*.xlsm'

(ooxml, embedded labels, header is 15 lines, table is VB2023);

Re: Loading multiple excel files from One Drive location

marcus_sommer — Mon, 23 May 2022 05:45:21 GMT

You are loading the files completely with a wildcard which means each file with *.xlsm will be taken - if there are different ones or maybe also temporary files starting with a ~ or similar (depending on your system-settings you may not even see them). Therefore specifying a filename-pattern like: Sales*_??????_*.xlsm might avoid the access on wrong files.

Beside the already mentioned suggestion to check if always an appropriate sheet and all listed field-names are there and also correctly spelled you may run into an issue with the specified header-lines. If an excel-row is completely empty and not involved in any function, naming, formatting or similar stuff this row will be skipped in regard to the counting - means in some files may the header lines lower than 15.

Another issue happens if there are fields multiple times included because these field-names becomes counted and get a suffix like Country_1, Country_2 and then the load won't see a field Country anymore and run into an error.

It's quite hard to detect all these possibilities manually especially by a lot of files but you could track where the error happens, for example with something like:

for each File in ('lib://OneDrive_location/*.xlsm')

trace $(File);

VB23:
LOAD
2023 as Year,
Country,
Value
from $(File) (ooxml, embedded labels, header is 15 lines, table is VB2023)
next File
;

within the progress window and within the document-log you could now see which files were successfully loaded and where it breaks. The failing file could you then grab with the table-wizard which will then show what's really there.

- Marcus