6 Replies Latest reply: Apr 26, 2017 10:09 AM by omar bensalem RSS

    Load certain fields out of an Excel File

    Michael Zimnol

      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

        • Re: Load certain fields out of an Excel File
          Deepak Sharma

          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.

          • Re: Load certain fields out of an Excel File
            beck bakytbek

            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?

            • Re: Load certain fields out of an Excel File
              Andrey Khoronenko

              Hi Michael,

               

              In the attached QVF file, the solution variant.

               

              Regards,

              Andrey

              • Re: Load certain fields out of an Excel File
                Diego Fernando Caivano

                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.-