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 Fri, 11/13/2015 - 13:38

Hi Mourad, I don't use ODBC drivers often enough so I will get back to you after I have tested some things out.

I am doing great, how are you doing these days? 

Thanks

Brian

Brian Element Sun, 11/15/2015 - 14:45

Hi Mourad,

Unfortunately I don't have access to oracle so I can't give you an example for your case.  This is one for Access that it has two tables and only extract the amounts where the IDs are the same.  Hopefully this will help you out.

dbName = "Table1.IMD"
Client.ImportODBCFile "`Table1`", dbName, FALSE, ";DSN=MS Access Database;DBQ=C:\USERS\BRIAN\DOCUMENTS\Database1.accdb;DefaultDir=C:\USERS\BRIAN\DOCUMENTS;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;", "SELECT Field2 FROM `Table1` Where ID = ID"
Client.OpenDatabase (dbName)

 

Mourad59290 Tue, 11/17/2015 - 03:55

Hi Brian,
Thank you very much for this it is very helpfull to understand how importation via ODBC work. I was thinking if it possible to import two different tables and then perform a join. Because in your example, it appears that you select same `Table1`. And I am to do a querie with the two tables in  using ImportClientODBCFile just one time.
Otherwise, did you heard about V10 of IDEA ? I was attended to a conference for the presentation of the new features. Some of the functionnalties seams interesting.
Thank you very much for your help. It is always a pleasure to come to your blog.
Kind Regards,
Mourad.
 

Brian Element Tue, 11/17/2015 - 07:50

Hi Mourad,

I will try and get a better example for you and supply the data files so you can see what is going on.  I think I have some time tonight to do it.

Yes, I have heard of it, I was a beta tester and have a version on my desktop.  I haven't had as much time as I would like to play with the new features but the discover is quite nice and the passport gives you links to resources so it is well worth the upgrade.  I hope when I have more time I can post more on the new features.

Brian

Mourad59290 Wed, 12/16/2015 - 09:41

Hi Brian,
How are you ?
Sorry if i did not reply sooner. Actually, I achieved to launch the request and perform a join between two tables in ORACLE. In fact, it is very simple. In the fifth argurmnent of the function Client.ImportODBCFile, you can put whatever SQL request. The only thing is to respect the syntaxe of the ORACLE SQL language.
Thank you very much again for you help.
Best Regards,
Mourad 

paarth Sun, 02/05/2017 - 10:20

Dear Mr. Brian. Hello, ...... i joined forum today only and the conversations sound meaningfull and valuable between some of the valued members and ur goodself. Sir i have a small issue which i m sure u can give me the remedy. i m a government auditor and presently audit government bodies and statutory instutions. Right now i have an assignment of  performance audit on power purchase agreements and electric revenue realization. i have to importment data from the server using idea ODBC. I M not familier about that . kindly give men the step by step guidelines so that i am able to import their billing data.
with regards
paarth

Brian Element Mon, 02/06/2017 - 08:41

Hello Paarth and welcome to the site.

Here is an example of importing an Access database using ODBC.   I don't usually use ODBC in my work so I don't have any examples in connecting to Oracle or SAP but hopefully this will help you out.

You first select the import assistant and select ODBC.

You click on next and select the ODBC driver that relates to your system.  In this case I select the MS Access database.

I then click on next and select the file that I wish to import.  In this instance it is the Sample-Customers.MDB file.

I then select OK and I have a list of tables that I can import.  I can select multiple tables if I wish.

If I select the Advanced button I am able to enter some SQL to extract a subset of the file.  SQL can also be used to join tables together on the import.

I then click on OK to return to the previous dialog and select next to continue which brings me to the import assistant screen where I give the database a name.

So depending on your data source you will need to have an ODBC driver that is configured to connect with your application.

Hope that helps you out a bit.

Brian

paarth Mon, 02/06/2017 - 22:28

that helps a lot
Thanks Mr. Brian
wl disturb u again in case any difficulty in importing the data from external source

paarth Mon, 02/06/2017 - 22:31

do i need to install odbc driver on my laptop before starting the import from ouside source 
plz guide
 

Brian Element Tue, 02/07/2017 - 07:10

Hello Paarth,

Yes you will have to get the odbc drivers and make sure they are installed before using them on your computer.  Depending on the financial system you should check with the vendor to see if they have some drivers that you can use.

Brian