4 Replies Latest reply: Aug 15, 2014 12:21 PM by Alam Nizami RSS

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

    Alam Nizami

      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

      [H:\Information Technology\QLIKVIEW_DROPBOX\ANProjects.xlsx]

      (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:

       

      ProjectNameDatabaseName
      Project1project1_db
      Project2project2_db
      Project3project3_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.
          Enrique Colomer

          Hi

          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.
            Massimo Grossi

            do you mean this?

             

            t:

            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.
                Alam Nizami

                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.
                  Alam Nizami

                  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;