idea Re: Get Excel Sheet Names in Excel Loading Data Connector - Status changed to: Closed - Archived in Suggest an Idea

Get Excel Sheet Names in Excel Loading Data Connector

simonaubert — Sat, 09 Jul 2022 14:04:20 GMT

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-with-file-name/ta-p/1481095 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

Re: Get Excel Sheet Names in Excel Loading Data Connector

Or — Wed, 16 Mar 2022 11:18:11 GMT

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

Re: Get Excel Sheet Names in Excel Loading Data Connector

simonaubert — Wed, 16 Mar 2022 11:44:57 GMT

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

Re: Get Excel Sheet Names in Excel Loading Data Connector

Or — Wed, 16 Mar 2022 11:55:10 GMT

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

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

Meghann_MacDonald — Wed, 02 Aug 2023 15:33:16 GMT

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.

Re: Get Excel Sheet Names in Excel Loading Data Connector - Status changed to: Closed - Archived

Ideation — Wed, 02 Aug 2023 15:33:18 GMT