Read Connections through Loops and Load Statement in Loop?

[dmohanty]

Hi All,

I have different folders in which I have some .SHARED Files in each of them.

For example the folders are ABC, PQR, XYZ.

Inside folder ABC the shared files are ABC_1.shared, ABC_2.shared etc.

Inside folder PQR the shared files are PQR_1.shared, PQR_2.shared etc.

Inside folder XYZ the shared files are XYZ_1.shared, XYZ_2.shared etc.

Using one of the Power tool (QVServerObjectConnector.exe), we are able to read the .SHARED Files into the QVW and the connection String looks like (the folders are highlighted in Bold Red):

CUSTOM CONNECT TO "Provider=QvServerObjectConnector.exe;Folder=D:\QVWs\ABC\;XUserId=EFdBIMA;XPassword=APLBTeC;";

CUSTOM CONNECT TO "Provider=QvServerObjectConnector.exe;Folder=D:\QVWs\PQR\;XUserId=EFdBIMA;XPassword=APLBTeC;";

CUSTOM CONNECT TO "Provider=QvServerObjectConnector.exe;Folder=D:\QVWs\XYZ\;XUserId=EFdBIMA;XPassword=APLBTeC;";

Afterwards the Load Script is common, but the Storing of the QVD is as per the Folder name (highlighted in Yellow). Example below:

For folder ABC

CUSTOM CONNECT TO "Provider=QvServerObjectConnector.exe;Folder=D:\QVWs\ABC\;XUserId=EFdBIMA;XPassword=APLBTeC;";

BookmarkFields:
LOAD
ID as [Bookmark_ID],
autonumber(ID & FileName) AS Key,
BookmarkFieldName,
BookmarkFieldBookmarkName as [Bookmark]
SQL SELECT
BookmarkFieldBookmarkName,
BookmarkFieldName,
FileName,
ID,
BookmarkFieldBookmarkName
FROM BookmarkFields;

STORE BookmarkFields into D:\QVWs\Bookmark_ABC.qvd (qvd);
DROP Table BookmarkFields;

For folder PQR

CUSTOM CONNECT TO "Provider=QvServerObjectConnector.exe;Folder=D:\QVWs\PQR\;XUserId=EFdBIMA;XPassword=APLBTeC;";

BookmarkFields:
  LOAD
  ID as [Bookmark_ID],
  autonumber(ID & FileName) AS Key,
  BookmarkFieldName,
  BookmarkFieldBookmarkName as [Bookmark]
  SQL SELECT
  BookmarkFieldBookmarkName,
  BookmarkFieldName,
  FileName,
  ID,
  BookmarkFieldBookmarkName
  FROM BookmarkFields;

STORE BookmarkFields into D:\QVWs\Bookmark_PQR.qvd (qvd);
DROP Table BookmarkFields;

Here my requirement is -

1. How can I make the connections read in Loop (may be from INC files), just to change the Folder names dynamically ?
2. How the Load Script will be loaded in Loop (as the script is common) after its each corresponding Connection String and is stored as QVDs with the folder name suffixed as highlighted above?

Regards!!

[marcus_sommer]

Have a look here: Use database names from Excel sheet in a For Loop for Connection String.

- Marcus

[Gysbert_Wassenaar · Accepted solution]

Something like this:

For Each Dir in 'ABC','PQR','XYZ'

CUSTOM CONNECT TO"Provider=QvServerObjectConnector.exe;Folder=D:\QVWs\$(Dir)\;XUserId=EFdBIMA;XPassword=APLBTeC;";

BookmarkFields:
LOAD
ID as [Bookmark_ID],
autonumber(ID & FileName) AS Key,
BookmarkFieldName,
BookmarkFieldBookmarkName as [Bookmark]
SQL SELECT
BookmarkFieldBookmarkName,
BookmarkFieldName,
FileName,
ID,
BookmarkFieldBookmarkName
FROM BookmarkFields;

STORE BookmarkFields into D:\QVWs\Bookmark_$(Dir).qvd (qvd);
DROP Table BookmarkFields;

Next

You could also put the directory names in a table and loop over the values in the table:

load * inline [

Dir

ABC

PQR

XYZ ];

FOR Each Dir in FieldValueList('Dir')

Do stuff...

NEXT Dir

You can use any kind of source for that. The inline table is just an example.

[dmohanty]

Hi gwassenaar‌.

Great!!!  Both the ways you have suggested, have worked.

Many thanks and have a good one!