Suggest an Idea

[simonaubert]

Hello all,

Here an idea for all Excel+Qlik lovers : I would like to retrieve easily the list of Sheet Names for an Excel File.

Use case : I got dozens and dozens of excel files. Some of them contains a Version sheet, some not. I want to know the version where there is a version sheet and to identify the excel files where it's missing.


I know there is this turnardound https://community.qlik.com/t5/QlikView-Documents/Loading-Multiple-Excel-Sheets-Dynamically-along-wit...  but it's really not trivial at all for a user and only work on QlikView.

Alteryx proposes it and it really helped.

Best regards,

Simon

[Or]

It would indeed be handy to be able to dynamically read all sheets in an Excel file without resorting to the clunky QV workaround.

Insofar as checking whether a sheet exists, I think you could actually do that in QS with a workaround, though it's thoroughly clunky. This version requires there be content in the first column A but could likely be adapted to anything as long as the sheet contains some sort of data in a specific column.

// Here we would create a loop to read through all the files and a variable to contain the current file name, used a single file for testing
Set vFileName = 'lib://AttachedFiles/Book1.xlsx';
Set ErrorMode = 0;
Excel:
Load '' as B // Just to make sure we have an Excel table, though I don't think this is actually necessary, force of habit
Autogenerate(1);
CONCATENATE
LOAD
A, '$(vFileName)' as FileName
FROM ['$(vFileName)']
(ooxml, no labels, table is Schrodinger);

Let vExists = FieldNumber('A','Excel');

Set ErrorMode = 1;

IF vExists >0 THEN
Load '$(vFileName)' as File
, 'Has Schrodinger''s Sheet' as HasSheet
Resident Excel;
ELSE
Load '$(vFileName)' as File
, 'Has no Schrodinger''s Sheet' as HasSheet
Resident Excel;

END IF

Drop Table Excel;

// Here we would loop to the next file

[simonaubert]

Hello @Or  . Thanks for the answer. 🙂

I actually implemented something very similar for my use case but that only works because I know the sheet name. And it's dirty, I would prefer to not use the errormode.

SUB Version_Fichier_Excel(p_Table_Entree,p_Champ_Contenant_Le_Chemin)
NoConcatenate
TMP_Fichiers_Excel:
LOAD 
  $(p_Champ_Contenant_Le_Chemin) as Fichier_Excel
RESIDENT [$(p_Table_Entree)]
where right(Fichier_Complet_Liste,5)='.xlsx';

let v_Nb_Fichier_Excel=NoOfRows('TMP_Fichiers_Excel');

NoConcatenate
Version_Fichier_Excel:
LOAD 
  text(Null()) as [Fichier_Excel],
  text(null()) as [Version Fichier Excel]
Autogenerate(0);

FOR i_excel_file=0 to $(v_Nb_Fichier_Excel)-1

    let v_fichier_excel=peek('Fichier_Excel',$(i_excel_file),'TMP_Fichiers_Excel');
	set errormode=0;

    NoConcatenate
    TMP_Version_Fichier_Excel:
    LOAD
        MaxString(F1) as [Version Fichier Excel]
    FROM [$(v_fichier_excel)]
    (ooxml, embedded labels, table is Version)
    where F1<>'Version';

    Concatenate(Version_Fichier_Excel)
    LOAD *,
    text('$(v_fichier_excel)') as [Fichier_Excel]
    RESIDENT TMP_Version_Fichier_Excel;

    DROP TABLE TMP_Version_Fichier_Excel;
	set errormode=1;
    
Next i_excel_file;

DROP TABLE TMP_Fichiers_Excel;
END SUB;

Best regards,

Simon

[Or]

@simonaubert Yup, it's clunky and limited, but it should work for that specific use case. I already added my like to this idea, so perhaps Qlik will add this at some point in the future and let us easily read the contents of all sheets in a file or all sheets within a directory.

One other potential approach, which is also clunky but in a different way, is to set up a PowerQuery task to generate all of the sheet names and then use that in Qlik. I'm not an expert on this but I assume it could be set up to automatically update the sheet names on a scheduled basis.

[Meghann_MacDonald]

From now on, please track this idea from the Ideation portal. 

Link to new idea

Meghann

NOTE: Upon clicking this link 2 tabs may open - please feel free to close the one with a login page. If you only see 1 tab with the login page, please try clicking this link first: Authenticate me! then try the link above again. Ensure pop-up blocker is off.