Dynamic Input Tool - Input Data Source Template vs Oracle DB

[magdaemem]

Hello - 

I have an issue with Dynamic Input Tool and was wondering if someone can help me. 

I'm querying data from a large Oracle DB with company application details (apps have codes i.e AA123, AA234 etc.)

I have a list in XLSX of app codes for which I need to fetch the data from Oracle DB. XLSX file changes on a daily basis and currently it contains around 700 app codes. 

I would like to use that XLSX file as Input Data Source Template in Dynamic Input tool and then use column with App Codes to replace single App code in my SQL query i.e. 

SQL Update Where clause OR Replace a Specific String (I tried both)

SQL Clause to Update

DBTABLE.COMPONENTCODE = "AA123'

Value type: String 

Text to replace 'AA123'

Replacement field: "App codes' <--this is a column from XLSX file

It does not seem to work with XLSX file. It worked when I copied it to Text Input though.

Can someone please help me to understand how I can make this work with the file instead of Text input? Should I change XLSX format to something else? CSV does not work either. 

Thanks

Magda 

[Ladarthure]

Hi @magdaemem,

I would not be able to replicate the error you have, but could you share what error message you have if you have one.

If it does not end up working, you may want to try with other methods : 

• Using in db tools and maybe more likely dynamic query in db wher you could create your query and replace what you want in this query.
  • with a query looking like something like this : select * from table where app_code in ('aaa', 'bbb'...)
• Using a create table in db set up to create a temporary table and then using a join on the app code. The temporary table would look like a table with only one column containing the app code.
• creating a batch macro to replace the app code in each query with a input data in the macro and connecting it to the excel file you have

Another way to do it

[magdaemem]

I dont have any errors, it just returns empty columns when I use XLSX. When I use Text input it works as expected. 

Thank you for suggestions, I will try the ideas you shared.

[Taran87]

Hi @magdaememdid it work? I am also looking to do something similar and it would be great if you could help. Thanks