Use Excel Sheet name

jaymerry — Wed, 09 Feb 2011 15:27:14 GMT

Hello all,

Do you know if it's possible to get the name of the Excel sheet which is load in my script ?

I don't find any function anout it.

Thank you for your help,

Jaymerry

Use Excel Sheet name

prieper — Wed, 09 Feb 2011 15:54:35 GMT

The name of the Excelsheet, which you are loading is in the script. You may also write workbook- and sheet-name into variables and refer to them in the script.

If you access Excel with an ODBC-connection (not the default loader) you may read all sheetnames with SQLTABLES-command and store them into a table. Then you may inspect the possible sheetnames and select one or more, which are to be loaded.

Below you may find a copy from an application:

// ======================================================================================== Load Excel-Files from one directory
/* All Excel-files fitting into a certain pattern will be loaded
 individual sheets will be loaded if also fitting into certain pattern
 file-headers are *not* used, as have changed in the past
 each file will be documented with file- and sheetname as well as with creation-date of the file
*/

// ==============================================================Scan through all Files in a Directoy matching a certain pattern

 FOR each varFile in FileList (strFileList) // ===============strFileList has been defined under "Variables"
 ODBC CONNECT TO [Excel Files; DBQ=$(varFile)]; // ========================================DBQ= Name of Workbook

 tmp: // =====================================Read sheet-informations
 sqltables;

 SheetNames:
 LOAD
 *,
 recno() AS record;
 LOAD
 TABLE_NAME AS SheetName
 RESIDENT
 tmp
 WHERE
 WildMatch(TABLE_NAME, '*BD*')
 AND NOT WildMatch(TABLE_NAME, '*_Filter*', '*Print*', '*FilterDatabase*');
 DROP TABLE tmp;

 LET maxRec = PEEK('record', -1, 'SheetNames'); // ==============================Determine Last Table (for Loop)

 FOR i = 0 TO maxRec -1 // ========================Loop through all sheets and load data

 LET strSheet = Trim(PEEK('SheetName', i, 'SheetNames'));
 LET strConc = chr(39) & Upper(Mid(strSheet, 5, 3)) & chr(39); // ======= Logistic Concept (SUR/DEF) from sheetname
 LET strSiTy = chr(39) & Upper(Left(SubField(strSheet, ' ', 3), 4)) & chr(39); // ============ Read SiTy from sheetname
 LET strSheet = PurgeChar(strSheet, chr(39));
 LET strSheet = PurgeChar(strSheet, '_'); // ====================== used for designating sheet for reading
 Let strSheetName = chr(39) & strSheet & chr(39); // ================== used for recording filename for each entry
 If WildMatch(strSheet, '*DV20*', '*DV40*', '*HC40*', '*RE40*') THEN
 Data:
 LOAD
 $(strConc) AS Concept,
 $(strSiTy) AS SiTy,
 // @1 AS Region,
 @2 AS Port,
 ...
 FROM
 [$(varFile)]
 (biff, header is 5 lines, no labels, table is [$(strSheet)])
 WHERE
 Len(Trim(@2)) = 5;
 END If
 NEXT i
 DROP TABLE SheetNames;

NEXT varFile

HTH
Peter

Re: Use Excel Sheet name

maks248 — Tue, 07 Apr 2020 17:25:11 GMT

Hello. This is a good decision. Tell me how to apply it in Qlik Sense? Unfortunately, the connection string with variables does not work there.

topic Re: Use Excel Sheet name in QlikView

Use Excel Sheet name

Use Excel Sheet name

Re: Use Excel Sheet name