Skip to main content
Announcements
Global Transformation Awards! Applications are now open. Submit Entry
cancel
Showing results for 
Search instead for 
Did you mean: 
mattcorke
Contributor II
Contributor II

Adding new columns and sheets to existing import from Excel

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

 

5 Replies
PrashantSangle

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,

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
mattcorke
Contributor II
Contributor II
Author

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

rangam_s
Creator II
Creator II

Remove the auto-generated script (it will be generated once you go to "Data manager" view).

Just unlock and remove that auto-generated script page, then you will see the new fields automatically (after reload - Make sure to give * instead of fields names).

If in-case, you want to the fields information then go to "Data model viewer". so that it will not generate autogenerated script.

mattcorke
Contributor II
Contributor II
Author

Thank you. I got it to work, although I did have to reestablish connections in the 'Open Hub'.

What is the best way of adding additional Excel sheets / files to the data model?

Thanks

Matt

rangam_s
Creator II
Creator II

Better to use additional file then sheets, bcz if it is sheet you need to call it one by one or use ODBC connection to read all the sheets.

In-case of files, you can call then by using file name pattern.