10 Replies Latest reply: Nov 14, 2017 10:13 AM by Branislav Bujnak 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:


      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



          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?




          • 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)];




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







            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;



            Hope this helps.