Dynamic loading of tables from Data Base

[harishicon]

Hello All,

I have one excel which is containing the tables name . The Tables name which are mentioned should be picked up from the database and two flags to be added in the table along the fields name  . Name of tables can grow in excel.

For Example:- In Excel there is Table name

Maxx_MIS_H where Maxx  is Table name, MIS is BU Code and H is Ledger.

Now Maxx should be picked up and MIS and H will be add as column in form of flag along with table's fields.

Please guide me how can I pick the table name form excel and connect with the data base as well as how can i make flag in the table as mentioned above dynamically.

Thanks in advance....

[boorgura]

If it is one value in excel:

you can capture the values in variables.

ExcelRead:

LOAD Value

frOM <excelfile> ;

LET vValue = peek('Value',0, 'ExcelRead');

LET vTable = subfield(vValue, '_', 1)

LET vBU = subfield(vValue, '_', 2)

LET vLedger = subfield(vValue, '_', 3)

LOAD *,

     '$(vBU)' as BU,

     '$(vLedger)' as Ledger;

SQL SELECT *

FROM '$(vTable)'

In case of multiple table names from excel file - you will have loop thru the above code based on the number of records within the excel

[harishicon]

how to use loop in above scenario. Please tell me

[boorgura]

For looping, you can use the same script enclosed in a loop.

ExcelRead:

LOAD Value

frOM <excelfile> ;

LET  vRowCount = NoOfRows('ExcelRead');

FOR i = 0 to $(vRowCount)

LET vValue = peek('Value',$(i), 'ExcelRead');

LET vTable = subfield(vValue, '_', 1)

LET vBU = subfield(vValue, '_', 2)

LET vLedger = subfield(vValue, '_', 3)

LOAD *,

     '$(vBU)' as BU,

     '$(vLedger)' as Ledger;

SQL SELECT *

FROM '$(vTable)'

NEXT

if you were to concatenate all the DB tables into one table - you will have to declare the table name before the loop with null values. And then use a Concatenate(TableName) before the LOAD statement in the loop.

[harishicon]

Thanks Dear.

I also created . please have a look

ExcelRead:

LOAD Value

frOM <excelfile> ;

FOR Each a in FieldValueList('Value')

LET vValue = peek('Value',$(a), 'ExcelRead');

LET vTable = subfield(vValue, '_', 1)

LET vBU = subfield(vValue, '_', 2)

LET vLedger = subfield(vValue, '_', 3)

LOAD *,

     '$(vBU)' as BU,

     '$(vLedger)' as Ledger;

SQL SELECT *

FROM '$(vTable)'

Next a;