Connect to ODBC/OLEDB and LOAD from sources in cycle

[#1]

Hello.

I have a bunch of database from which I need to export data into QlikView:

QlikView.1207.sqlite3

QlikView.1208.sqlite3

QlikView.1209.sqlite3

QlikView.1210.sqlite3

QlikView.1211.sqlite3

QlikView.1212.sqlite3

QlikView.1301.sqlite3

QlikView.1302.sqlite3

QlikView.1303.sqlite3

QlikView.1304.sqlite3

QlikView.1305.sqlite3

.........................

They are of exactly the same structure. How do I create a "OLEDB CONNECT TO" / "LOAD" / "CONCATENATE" statements so not to repeat the same over and over again?

Currently I just type the same "OLEDB CONNECT TO" / "LOAD" / "CONCATENATE" set for each of the files.

[luis_pimentel]

1º Store your database names into a INLINE table.

2º With a FOR loop and a NoOfRows function you can iterate for each one of the INLINE rows.

3º Store each row value into a variable with the Peek function.

4º Use your variable into your "OLEDB CONNECT TO" / "LOAD" / "CONCATENATE" sentence.

Hope that helps.

-Luis.

[martinpohl]

see attached two examples.

One by a list of values, the other by a generic list of values, depending on year/month information

Regards

[luis_pimentel]

Aux:

LOAD * INLINE [

     DBName

     QlikView.1207.sqlite3

     QlikView.1208.sqlite3

     QlikView.1209.sqlite3

];

FOR nrow = 0 TO noOfRows('Aux') - 1

     LET vBD = peek('BDName',$(nrow),Aux);

     OLEDB CONNECT TO $(vBD);

     ......

Next;

[Gysbert_Wassenaar]

You could create a sub routine and use the file name as parameter. Something like:

Sub LoadSQLite(FileName)

     oledb connect to "Provider=....;Data Source="&FileName

     select * from ...tablename...;

     disconnect;

End sub

Call LoadSQLite('QlikView.1207.sqlite3');

Call LoadSQLite('QlikView.1208.sqlite3');

....

Call LoadSQLite('QlikView.1305.sqlite3');

[#6]

Thanks everyone. I've did it this way:

FOR Each File in filelist ('C:\Users\root\QlikView projects\Edoff test\QlikView.1*.sqlite3')

LET dbName = '[Provider=MSDASQL.1;Persist Security Info=False;Extended Properties="DSN=QlikView DDD;Database=' & File & ';StepAPI=0;SyncPragma=;NoTXN=0;Timeout=;ShortNames=0;LongNames=0;NoCreat=0;NoWCHAR=0;FKSupport=0;JournalMode=;OEMCP=0;LoadExt=;BigInt=0;JDConv=0;"]';

    OLEDB CONNECT TO $(dbName);

    LOAD........

SQL SELECT *

FROM sales;

NEXT File