9 Replies Latest reply: Apr 11, 2017 8:40 AM by neelam sehrawat RSS

    Insert Excel Sheet name into a Field

    Nicolò Cogno

      Hi everybody,

       

      I'm uploading several Excel files in Qlik Sense. I know their name and I don't want to loop over all files, but just to read the name of the sheets (I don't know if I've been clear, for file I mean test.xlsx and the sheets are within the file like Sheet1, Sheet2,...) in order to put them in a field.

      So I've got this situation:


      [Table1]:

      LOAD *

      FROM [lib://Desktop\test.xlsx]

      (ooxml, embedded labels, table is Sheet1);

       

      and I want to read "Sheet1" and put it in a field.

       

      How could I do it? I tried with different macro using the ActiveDocument.ActiveSheet.GetProperties.Name method, but I did'n get what I wanted.

       

      Thanks a lot for your help,

       

      best regards

       

      Nicolò Cogno

        • Re: Insert Excel Sheet name into a Field
          Vladimir Komarov

          Nicolò,

           

          Your question is a bit confusing: Are you trying to extract the Sheet Name during the load and create a special field with it? Or are you trying to modify your script to load all sheets into different tables (named by each Excel sheet)  in one script?

           

           

          VK

          • Re: Insert Excel Sheet name into a Field
            neelam sehrawat

            Try this:

            This script loads data from all the sheets in excel and variable 'sheetName' creates a fields with all the sheet names.

             

            ODBC CONNECT32 TO [Excel Files;DBQ=$(file)];

             

             

            SheetNames:

            SQLtables;  // Loads all sheet names in the Excel file.

             

            DISCONNECT;

             

             

            SystemTable:

            NoConcatenate

            LOAD *

             

             

            Resident SheetNames

            Where Not Match(Upper(TABLE_TYPE), 'TABLE');

            DROP Table SheetNames;

             

             

             

             

             

            FOR index = 0 to NoOfRows('SystemTable')-1  // Loops for each sheet in the Excel file.

            LET sheetName = PurgeChar(PurgeChar(Peek('TABLE_NAME', index, 'SystemTable'), Chr(39)), Chr(36));

             

            Load * ,

              FileName() as File_Name,

              '$(sheetName)' as Sheet_Name

            From [$(file)]

            (biff, embedded labels, table is [$(sheetName)$]);

            NEXT index

            DROP TABLE SystemTable;

            NEXT

             

            Hope this helps.