Script explanation

[nareshthavidishetty]

Hi,

Actually need an detailed explanation for the below script like how each script line works,

LET vFilePath = 'D:\Qlikview_dev\Qlikview__Template\Developers\nar\budget';

Data:

LOAD

'' AS Dummy

AutoGenerate (0);

FOR EACH file in FileList('$(vFilePath)\*.xlsx');

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

SheetNames:

SQLtables;

DISCONNECT;

FOR index = 0 to NoOfRows('SheetNames')-1

LET sheetNamez = PurgeChar(PurgeChar(Peek('TABLE_NAME', index, 'SheetNames'), Chr(39)), Chr(36));

Concatenate(Data)

Tab:

Load *,

  Month( GltPostingDate) as GltMonth,

    Year( GltPostingDate) as GltYear,

  GltAcctUnit&'-'&GltAccount&'-'&GltSubAccount as SheetName,

  '$(sheetNamez)' as Sheet_names

From $(file)(ooxml, embedded labels, table is [$(sheetNamez)]);

NEXT index

DROP TABLE SheetNames;

NEXT

DROP FIELD Dummy;

[avinashelite]

This script is used to load Multiple Excel files dynamically from a folder with the file names and sheet names ...

// initialising the path where the excels are stored

LET vFilePath = 'D:\Qlikview_dev\Qlikview__Template\Developers\nar\budget';

// dummy table , actually this not required

Data:

LOAD

'' AS Dummy

AutoGenerate (0);

// to get the all the files list in the folder

FOR EACH file in FileList('$(vFilePath)\*.xlsx');

// connecting for the files through the ODBC connection , this has advantages 

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

SheetNames:// table name

SQLtables;// its a SQL function

DISCONNECT;

// to read all the sheets in the excel

FOR index = 0 to NoOfRows('SheetNames')-1

LET sheetNamez = PurgeChar(PurgeChar(Peek('TABLE_NAME', index, 'SheetNames'), Chr(39)), Chr(36));

Concatenate(Data)

Tab:

Load *,

  Month( GltPostingDate) as GltMonth,

    Year( GltPostingDate) as GltYear,

  GltAcctUnit&'-'&GltAccount&'-'&GltSubAccount as SheetName,

  '$(sheetNamez)' as Sheet_names

From $(file)(ooxml, embedded labels, table is [$(sheetNamez)]);

NEXT index

DROP TABLE SheetNames;

NEXT

DROP FIELD Dummy;

[jolivares]

This script in a few words is reading multiples excel files with multiples or not sheets inside and create a table named "Tab"

Step by Step:

1.- Assign a file path to a variable : "vFilePath"

2.- Create a table named "Data" with a field Dummy

3.- Make a For loop to iterate over each file

4.- Coonect to a file that is reading

5.- Assign each sheet name

6.- Make a For loop to read each sheet in every file

7.- Assign each sheet name to a variable "sheeNamez"

8.- Read each sheet  and concatenate to the table "Data"

9.- Drop the field Dummy from the table "Data"

[sunny_talwar]

Avinash R wrote:

// dummy table , actually this not required

Data:

LOAD

'' AS Dummy

AutoGenerate (0);

Avinash‌ I am going to disagree with you on this, I think the dummy table is required unless you add a variable to below statement instead of Concatenate(Data)

Concatenate(Data)

Tab:

Load *,

  Month( GltPostingDate) as GltMonth,

    Year( GltPostingDate) as GltYear,

  GltAcctUnit&'-'&GltAccount&'-'&GltSubAccount as SheetName,

  '$(sheetNamez)' as Sheet_names

From $(file)(ooxml, embedded labels, table is [$(sheetNamez)]);

NEXT index

DROP TABLE SheetNames;

NEXT

[avinashelite]

sunindia‌

Since the table structure is same Qlikview will automatically concatenate to the TAB table rite??  why do we need the Data table