Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Load certain fields out of an Excel File

Hello

i'm new to Qlik Sense Desktop and i have a question:

Is it possible to load some certain data out of an Excel-File?

For example i have the following table in my Excel-File:

TestTest_1Test_2Data_1Data_2Country
12131512344321Ger
34567891112342US
73826358876166214332185Frau

I want to load all the fields that contain "Test" and "Data". So if there would be some more fields with these words i want to load them too.

Is that possible in Qlik Sense Desktop?

Greetings

6 Replies
dsharmaqv
Contributor III

Re: Load certain fields out of an Excel File

Selecting data from a Microsoft Excel file

You can load from data from a selected Microsoft Excel file. When you have selected fields, you can insert the script code required to load the fields into the script.

See: How to prepare Excel files for loading with Qlik Sense  

To start selecting data, do the following:

  1. Click ± on a Folder data connector in the data load editor.
  2. Select file from the drop-down list of available files in the folder and click Select.

Select data from is displayed and the list of Tables is populated with the sheets and named areas available in the selected Microsoft Excel file.You can also use a Microsoft Excel file as data source using the ODBC interface. In that case you need to use an ODBC data connection instead of a Folder data connection.

Selecting tables

The Tables list includes all sheets and named areas of the selected Microsoft Excel file.If you want to select all the fields in a table, do the following:

  • Check the box next to the table name.

If you want to select specific fields from a table, do the following:

  • Click the table name (not in the checkbox). Fields is updated with the available table content, and you can select the fields you require.

Tables that have all columns selected are indicated with a checkmark in the checkbox, while tables with some fields selected are indicated with a square in the checkbox and the number of selected fields to the right of the table name.

Selecting fields

Fields lists all fields available in the selected table. You may need to specify the following settings:Field names:

  • Embedded field names, if field names (headers) are stored in the first line of data.
  • No field names, if there are no field names.

Header size:

  • Set to the number of lines to ignore when loading data. 

Do the following:

  • Select the fields to include using the checkbox next to each field name.

When you have selected the fields to include from the selected table, you can continue to select fields from other tables in the same file.

Renaming fields

You can rename fields. This is particularly useful in the following two cases:

  • If you load two files containing a field with the same name, they will by default be linked and treated as one field in Qlik Sense. If you want to load them as separate fields, rename the fields so that they are different.
  • If you load two files containing a field that should be read as one field, but has different names in the respective files, you can rename them (in either file) to have identical names to load them as one field.

Do the following:

  • Click on the field header you want to rename, type the new name and press Enter.

The field is renamed, and the script preview is updated if the field is selected.Renaming a field corresponds to using as in a field definition in a LOAD statement.

Previewing the script

The statements that will be inserted are displayed in the script preview , which you can choose to hide by clicking Preview script.

Inserting the script

When you have finished selecting fields and want to generate your LOAD/SELECT statements in the script, do the following:

  • Click Insert script.

The Select data from window is closed, and the LOAD statements are inserted in the script in accordance with your selections.

beck110979
Valued Contributor III

Re: Load certain fields out of an Excel File

Hi Mchael,

but you have 6 Fields, do you mean the Field : Test and Data_1, give a bit more information of your expected output?

ahaahaaha
Honored Contributor

Re: Load certain fields out of an Excel File

Hi Michael,

In the attached QVF file, the solution variant.

Regards,

Andrey

beck110979
Valued Contributor III

Re: Load certain fields out of an Excel File

cool way of proceeding

omarbensalem
Esteemed Contributor

Re: Load certain fields out of an Excel File

Love it!

dcaivano
Contributor

Re: Load certain fields out of an Excel File

Hi,

I think the most straightforward way of achieving what you need is by transposing the table first. Try this:

FIELDS:

LOAD

  CONCAT(IF(WILDMATCH(@1, '*Test*', '*Data*'), @1), ',') AS FIELD

FROM [lib://MyConnection/MyFile.xlsx] (ooxml, no labels, table is MySheet, filters(transpose()))

GROUP BY 1;

LET vFIELDS = PEEK('FIELD', 0, 'FIELDS');

DROP TABLE FIELDS;

TABLE:

LOAD

  $(vFIELDS)

FROM [lib://MyConnection/MyFile.xlsx] (ooxml, embedded labels, table is MySheet);

Regards,

Diego.-