Loading multiple excel files from One Drive location

[Emil_bros]

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

[Sra1bandi]

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

[Emil_bros]

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 

[Sra1bandi]

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

[Emil_bros]

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.

[Sra1bandi]

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 

[Emil_bros]

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

[vinieme12]

You can load all xlsm files without looping

Load * 

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

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

[marcus_sommer]

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