topic Re: Generic Excel column not found in App Development

Generic Excel column not found

williams596 — Thu, 06 Jan 2022 11:38:29 GMT

Hi, I'm using the following script to try and load the fifth column from a number of spreadsheets with 31 different sheets named 1-31:

For i=1 to 31;

LOAD
F5
FROM [{Folder Location}/*.xlsx]
(ooxml, embedded labels, table is [$(i)]);

The issue is for some of these spreadsheets and/or sheets it's saying field 'F5' not found. I thought this would be a generic load of column 'E' so surely all sheets should have this?

Thanks

Re: Generic Excel column not found

Frank_Hartmann — Thu, 06 Jan 2022 12:44:10 GMT

Try this:

Set ErrorMode=0;

For i=1 to 31

LOAD
F5
FROM [{Folder Location}/*.xlsx]
(ooxml, embedded labels, table is [$(i)]);

next i;

hope this helps

Re: Generic Excel column not found

williams596 — Thu, 06 Jan 2022 12:57:52 GMT

Thanks for the response, it now runs through to completion however with 4 errors so I suspect 4 sheets are being missed out of the 12x31 sheets. I can work with this for now as only a small proportion however if you/anyone might know the root cause of the errors it would be most helpful.

Thanks

Re: Generic Excel column not found

rwunderlich — Thu, 06 Jan 2022 19:15:55 GMT

Your Load specifies "embedded labels". That means Qlik expects Fieldnames to appear in line 1 of the file. "Fn" is the generated name used when column n is blank. So I suspect your issue that 4 of your files have a value in cell E on line 1.

-Rob