Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
nareshthavidishetty
Creator III
Creator III

Script explanation

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;

4 Replies
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
Specialist
Specialist

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