Field not found from xlsx doc

[johnmorgan]

I'm pretty new to qlik and trying to debug a load script I inherited.  I have a local data folder with an excel doc (excel 2016, so xslx).  I try to load data from the doc and the first column in the document is not found.   I've tried everything I can think of as far as converting to csv, different versions of excel docs and it seems to always com back to the same thing, anyone have an idea?

Here's the relevant part of the load script:

UserInfo:

LOAD

    UserName,

    Role as UserRole,

    "Group" as UserGroup,

    "Rad Admin" as RadAdmin,

    upper("Group") as REDUCTION

FROM [lib://LocalData (r3_john.morgan)/CMB003AuthorGroups_Tagged.xlsx]

(ooxml, embedded labels, table is Staff);

Here's the Output:

4:11:06 PM

Lines fetched: 79

Field 'UserName' not found

Here's sample of the top of the file:

UserName	FamilyName	GivenName	Group	Rad Admin	Role
system	L000000	F0000	OTHER	NO	User
mmodal	L000001	F0001	OTHER	NO	User
shuttle	L000009	F0009	OTHER	NO	User

Any help would be awesome.  Thanks in advance

[ToniKautto · Accepted solution]

This error should indicate that the referred field name does not exist. The reason could vary.

• Naming is case sensitive. Check that your script field name is spelled exactly as in source.
• Source field name has blank character that changes the fieldname from "UserName" to "UserName "
• The load targets incorrect source table
• XLSX file has header, meaning there are empty lines above the intended header row

For example the attached XLSX file can be loaded with a load statement like this...

LOAD
    UserName,
    FamilyName,
    GivenName,
    "Group",
    "Rad Admin",
    Role
FROM [lib://MyFolderConnection/QC-281312.xlsx]
(ooxml, embedded labels, table is Sheet1);

You can do a wildcard load to see which field names the Qlik engine gets from the source. You can comment the current filed definitions and add a wildcard star in the load statement...

LOAD *
//     UserName,
//     FamilyName,
//     GivenName,
//     "Group",
//     "Rad Admin",
//     Role
FROM [lib://MyFolderConnection/QC-281312.xlsx]
(ooxml, embedded labels, table is Sheet1);

You can evaluate the loaded field names for example through Data Model Viewer.

[johnmorgan]

The sheet name in excel was different than than the load script, I didn't even think to look at that until I saw "table as Sheet1" in your sample above.  Thanks!

[carolinaoliveiral]

Hi John, 

I had the same problem and I found the key, please try using the following code: 

SECTION Application;

LOAD

    UserName,

    Role as UserRole,

    "Group" as UserGroup,

    "Rad Admin" as RadAdmin,

    upper("Group") as REDUCTION

FROM [lib://LocalData (r3_john.morgan)/CMB003AuthorGroups_Tagged.xlsx]

(ooxml, embedded labels, table is Staff);