5 Replies Latest reply: Aug 16, 2018 5:56 AM by Rangam Seshadri RSS

    Adding new columns and sheets to existing import from Excel

    Matt Corke

      Hi there,

       

      I'm sure this is relatively simple, but I can't find anything that clearly shows what I need to do. Help greatly appreciated!

       

      I have a folder with several Excel documents in. Within Qlik Sense I have a data connection to these files. When I add new data from these files I use the 'Load data' button within the Data Load Editor screen. This all works fine.

       

      However I would like to add three new columns across two Excel sheets, plus import data from an additional Excel file. I have tried modifying the Load script to include the names of the new columns but this isn't working.

       

      I also have two Auto-generated sections in the Data Load Editor. The first one is not locked. The second one is locked and says the 'script is auto-generated by the data manager'.

       

      Below is the script from the first auto-generated section I want to add two new fields to (‘Graduation year’ and 'Current pupil’).

       

      LOAD

          Name,

          Surname,

          "Forename (Firstname)",

          "Preferred Name",

          Gender,

          Form,

          "Academic House",

          "School ID",

          "CAT4 Non-Verbal",

          "CAT4 Quantitative",

          "CAT4 Verbal",

          "CEM NV",

          "CEM Score",

          "CAT4 Overall",

          "CAT4 Spatial",

          "CEM Maths",

          "CEM Vocab",

          "SEN flag",

          "EAL Flag",

          "EAL / SEN Flag"

      FROM [lib://Humanities data/Pupil general info and assessment data.xlsx]

      (ooxml, embedded labels, table is [Pupil general info]);

       

      And attached is the relevant script from the second auto-generated script.

       

      If I can figure out these two new fields, I'll try the same approach with the third.

       

      Many thanks in advance,

      Matt

       

       

       

       

        • Re: Adding new columns and sheets to existing import from Excel
          Prashant Sangle

          If you required all fields from excel sheet, then use * like below

           

          Load *

          from [lib://Humanities data/Pupil general info and assessment data.xlsx]

          (ooxml, embedded labels, table is [Pupil general info]);

           

          So that if tomorrow, new field added in your file then you don't required to add it over script.

           

          You can unlock the auto generated script by clicking on unlock button.

           

          Regards,

            • Re: Adding new columns and sheets to existing import from Excel
              Matt Corke

              Thanks for the reply. I tried that for the new tables but it didn't import the new columns from the excel sheets. I unlocked the second auto-generated section and deleted that... However, upon import of the data this screwed everything up - circular references, loads of synthetic keys etc.

               

              The code in the data manager auto-generated section looks like this:

               

              [Pupil general info_temp_4b2a3a0c-bf27-7f05-d6f1-9c7c7b2f]:

              LOAD

              [School ID],

              [EAL Flag] AS [Pupil general info.EAL Flag],

              [Preferred Name] AS [Pupil general info.Preferred Name],

              [Surname] AS [Pupil general info.Surname],

              [Forename (Firstname)] AS [Pupil general info.Forename (Firstname)],

              [Name] AS [Pupil general info.Name],

              [Gender] AS [Pupil general info.Gender],

              [Form],

              [Academic House],

              [CAT4 Non-Verbal],

              [CAT4 Quantitative],

              [CAT4 Verbal],

              [CEM NV],

              [CEM Score],

              [CAT4 Overall],

              [CAT4 Spatial],

              [CEM Maths],

              [CEM Vocab],

              [SEN flag],

              [EAL / SEN Flag]

              RESIDENT [Pupil general info];

              DROP TABLE [Pupil general info];


              [Pupil assessment data_temp_49bf7142-1b67-d70a-b139-30317187]:

              LOAD

              [Teacher] AS [Intials-Teacher],

              [School ID],

              [Subject] AS [Pupil assessment data.Subject],

              [Preferred Name] AS [Pupil assessment data.Preferred Name],

              [Year group] AS [Pupil assessment data.Year group],

              [Subject ID],

              [Surname] AS [Pupil assessment data.Surname],

              [Forename (Firstname)] AS [Pupil assessment data.Forename (Firstname)],

              [Full name_Ass],

              [Date],

              [Assessment name],

              [Assessment type],

              [% achieved]

              RESIDENT [Pupil assessment data];

              DROP TABLE [Pupil assessment data];


              Then at the bottom of the page after various other table import scripts is this:

              RENAME TABLE [Compiled data_temp_7b356be2-df77-32f6-7b1d-dd5c9ad8] to [Compiled data];

              RENAME TABLE [Pupil general info_temp_4b2a3a0c-bf27-7f05-d6f1-9c7c7b2f] to [Pupil general info];

              RENAME TABLE [Pupil assessment data_temp_49bf7142-1b67-d70a-b139-30317187] to [Pupil assessment data];

              RENAME TABLE [Pupil report data_temp_214c91a8-e221-cb42-ef13-3526c096] to [Pupil report data];

              RENAME TABLE [Sheet1_temp_fc37c3ab-dc1f-0adb-924c-c65df854] to [Sheet1];

              RENAME TABLE [SEN data_temp_7fae213e-4352-33c9-f795-71cb22b1] to [SEN data];

              RENAME TABLE [EAL data_temp_14984169-2bae-bc16-52fa-980addcb] to [EAL data];

              RENAME TABLE [Sheet1-1_temp_d544a15b-f4da-a6fc-316c-98e365cf] to [Sheet1-1];

               

              Below this is the autocalendar code.

               

              Do I need to change any of this as well?

               

              Thanks for your help.

              Matt