Importing Data using ODBC connection

kloren1

Forums

Hi,
I have the Mastering IDEAScript manual (by John Paul Mueller) and I can't seem to find any examples of importing data using an ODBC connection to Oracle within a macro. It is quite simple using the import assistant but my users have no SQL knowledge. Essentially what I would like to achieve is to create a dialog box which allows the user to enter an ID number and a fiscal year. Using those values as parameters I would like to import the data into IDEA.
SQL Example:
Select * from table(eg. transcation_detail) where id = :IDNumberEnteredByUser and fiscal_year = :FiscalYearEnteredByUser;
Is this possible and if so how would I do it? Do you need more information?
I am currently using Client Version 8.0.5.243
Thanks for your help.
Kenny
 
 

Brian Element Mon, 06/03/2013 - 10:50

Hi Kenny,

Can you do an import using the import assistant and then go to the history and copy the IDEAScript code for the import and either attach it or copy it here so I can have a look at it.  I don't have access to Oracle so I can't see exactly what IDEA is writing out so it might be possible, but I am not sure.  If the SQL statement is part of the Import script then it would probably be possible.

Brian

kloren1 Mon, 06/03/2013 - 11:53

Hi Brian,
Thanks for the quick response, below is the generated script:
dbName = "TEST_TRANS_DETAILS.IMD"
 ' Please replace ********** with  your password.
Client.ImportODBCFile "" & chr(34) & "TRANSACTION_DETAIL" & chr(34) & "", dbName, FALSE, ";DSN=DWH;UID=kenny;PWD=***********;", "select * 
  from TRANSACTION_DETAIL
 where EMPLOYEE_ID = '00379'
   and FISC_YEAR_KEY = '13'"
Client.OpenDatabase (dbName)
 
If I were able to pass the ID 00379 and the fiscal year 13 from a dialogue box which the user fills out, that would be amazing.
thanks again.

Brian Element Mon, 06/03/2013 - 12:35

Hi Kenny, try this out.  It is a simple script that asks for the ID and FY, I didn't do any error checking on the proper format, only that something was entered in as I wasn't too certain of the proper format.  Also I wasn't sure if you wanted the script to ask for the db name or password so I left it as is.

Let me know if you have any problems (or if it works), it can be easily expanded if needed.

Good luck.

Files
IDEA 12 python packages.jpg (69.87 KB)

kloren1 Mon, 06/03/2013 - 14:35

Hi Brian,
It works great but there is one thing I would need to add on which I forgot to mention.
In the function Function performImport() the line which connects to the database has ;UID=kenny;PWD=******** would it be possible to prompt the user for the username and password instead of having to hard code it in the coding?
 

Brian Element Mon, 06/03/2013 - 14:38

Sure, not a problem.  I will update it.  Just to let you know that the password will be in clear text as there is no password field to mask in IDEA.

Please find attached the update, let me know how it goes.

Files
30.gif (8.72 KB)

kloren1 Mon, 06/03/2013 - 15:02

Brian,
Thanks alot for your help and your wonderful website. It is quite difficult to find help on IDEAScripting and this is great. I'm new to IDEAScripting, I just starting looking at it about 2 weeks ago. I will definately be visiting the site quite often and hopefuully I will be able to contribute to the site as I get more familiar with the product.
Kenny

marisa406 Wed, 07/03/2013 - 09:49

When importing and appending, I keep running into a 1,000 record limit. How can I increase the limit?

Brian Element Wed, 07/03/2013 - 09:59

Hello Marisa,

It sounds like you are using the demo version of IDEA that has a 1,000 record limit, the educational version has 10,000 and the full version has no limits for all intentional purposes.  It sounds like you will need to upgrade your version of IDEA.  If you are using the full version can you give me some additional info, such as what type of importing / appends you are performing.

Thanks

Mourad59290 Fri, 11/13/2015 - 13:02

Hi Brian,
How are you ?
I am trying to find a way to realise a join of two tables using the ODBC connection such as :
 
SELECT CLI_NOM, TEL_NUMERO
FROM T_CLIENT, T_TELEPHONE
WHERE CLI_ID = CLI_ID
I have a Oracle database as Kenny.
Kind Regards,
Mourad