topic Re: Use database names from Excel sheet in a For Loop for Connection String. in QlikView

Use database names from Excel sheet in a For Loop for Connection String.

Mon, 26 Jan 2026 18:19:17 GMT

Hi,

I am a newbie to Qlikview and coding in general. I need some help figuring out how to do a for loop.

What I have is an Excel spreadsheet with two columns ProjectName and DatabaseName.

I brought in the excel file like this..

ANProjects:

LOAD

ProjectName,

DatabaseName

FROM

(ooxml, embedded labels, table is Sheet1);

Which brings the columns in to Qlikview, Now I want to create a For loop that will use the database names row by row in the connection string.

Example connection string, blue area is where I need to connect the Database.

OLEDB CONNECT32 TO [Provider=ABCDEDB.1;Persist Security Info=True;User ID=BI_QLIKVIEW;Initial Catalog=$(databaseName);Data Source=ABCD1P;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=00000;Use Encryption for Data=False;Tag with column collation when possible=False] (XPASsword is BLANK);

So I do not know how to load the database row by row which might involve a variable.

Each database will give the project more fields which are associated with different project names.

Instead of having to do a new connection string each time, and defining the fields I need I want to automate the process by pulling in all fields from each database, in a For Loop.

Example excel spread sheet:

ProjectName	DatabaseName
Project1	project1_db
Project2	project2_db
Project3	project3_db

Please let me know if I am not making myself clear.

Thank you

Alam

Re: Use database names from Excel sheet in a For Loop for Connection String.

ecolomer — Fri, 15 Aug 2014 15:40:56 GMT

Here you are an example:

// list files "classment.html" en hard disc

DIRECTORY C:\Mis lugares Web\F1\www.statsf1.com\es;

Set Root='C:\Mis lugares Web\F1\www.statsf1.com\es';

FOR any=1950 to 2013;

FOR Each Dir in DirList ('$(Root)\$(any)'&'\*.')

FOR Each File in FileList ('$(Dir)'&'\class*.html')

Res:

LOAD

'$(any)' as GP_Año,

Mid('$(Dir)',47) as Pais_f,

Pos as GP_Posición,

N° as GP_Num,

Piloto as GP_Piloto,

Chasis as GP_Equipo,

Motor as GP_Motor,

Vuelta as GP_Vueltas,

F7 as GP_Tiempo,

Pts as GP_Puntos

FROM [$(File)]

(html, utf8, embedded labels, table is @1)

where N°>0;

NEXT File

NEXT Dir

NEXT any

Re: Use database names from Excel sheet in a For Loop for Connection String.

maxgro — Fri, 15 Aug 2014 15:42:21 GMT

do you mean this?

load * inline [

ProjectName, DatabaseName

Project1, project1_db

Project2, project2_db

Project3, project3_db

];

// fill variable with field values row by row

For i = 0 To NoOfRows('t')-1

let vDatabaseName=peek('DatabaseName', $(i));

let vProjectName=peek('ProjectName', $(i));

trace $(vDatabaseName) $(vProjectName);

// .......... use connection string ???

next;

Re: Use database names from Excel sheet in a For Loop for Connection String.

Fri, 15 Aug 2014 15:46:59 GMT

Let me try this and I will get back to you.

Thanks

Re: Re: Use database names from Excel sheet in a For Loop for Connection String.

Fri, 15 Aug 2014 16:21:26 GMT

Thank you Massimo

This does work it runs through each database and connects to each one. Now I need to figure out how to take the data in for each database by Concatenating the table called (Defects) I created.

Let me know if you can help further, thanks.

I have this so far.

// fill variable with field values row by row

For i = 0 To NoOfRows('ANProjects')-1

let vDatabaseName=peek('DatabaseName', $(i));

let vProjectName=peek('ProjectName', $(i));

trace $(vDatabaseName) $(vProjectName);

// .......... use connection string ??? // Added the new variable from the for loop.

OLEDB CONNECT32 TO [Provider=ABCDEDB.1;Persist Security Info=True;User ID=BI_QLIKVIEW;Initial Catalog=$(vDatabaseName);Data Source=ABCD1P;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=00000;Use Encryption for Data=False;Tag with column collation when possible=False] (XPASsword is BLANK);

Let vDefectsTableName = 'Defects'; // Define Table Name

$(vDefectsTableName):

// Load everything in to table variable

LOAD *;

SQL SELECT *

FROM $(vDefectsTableName);

// Get information from Table in database

SELECT *

FROM tablename; /*Defect*/ //Table in each database is the same name.

next;