2 Replies Latest reply: Nov 11, 2017 8:32 AM by John Morgan RSS

    Field not found from xlsx doc

    John Morgan

      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:

       

            

      UserNameFamilyNameGivenNameGroupRad AdminRole
      systemL000000F0000OTHERNOUser
      mmodalL000001F0001OTHERNOUser
      shuttleL000009F0009OTHERNOUser

       

       

      Any help would be awesome.  Thanks in advance

        • Re: Field not found from xlsx doc
          Toni Kautto

          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.

          2017-11-11 10_26_56-Qlik Sense Desktop.png