MS Excel 2010 Oracle ODBC

Asked By 10 points N/A Posted on -
qa-featured

Hello Dear Techy Friends,

How to connect MS Excel 2010 Oracle ODBC, what is the minimum configuration for connecting Oracle this way.

Please do the needful getting a detailed procedure of connecting Excel – Oracle ODBC.

Thanks in Advance,

Ashley Glick

SHARE
Answered By 15 points N/A #159531

MS Excel 2010 Oracle ODBC

qa-featured

Hi Ashley,
For connecting MS excel 2010 to Oracle ODBC:
1) Install ODBC driver for Excel. By default, the ODBC driver is installed with windows, to read data from MS Excel
2) Set up a System Data Source Name using the Microsoft ODBC Administrator
   a) Select start, settings,control panel, Administrative tools, Data Sources.The ODBC Data Source Administrator dialog box is open
   b) Navigate to System DSN tab and click ‘Add’ to open ‘Create New Data Source’ dialog box
   c) Select ‘Microsoft Excel Driver’ for the set up of the data source. Click ‘Finish’ to open ODBC Microsoft Excel Setup dialog box
   d) Specify name for the Data Source
   e) Click ‘Select WorkBook’ for the excel file from which data is extracted
   f) Check for the version fields for the source excel file
3) Create the Heterogeneous Services Initialization File
   a) For the configuration of the agent,set the parameters in the heterogeneous service initialization  file
   b) The file name is ‘initSID.ora’,where SID is the Oracle System identifier
   c) The file is located in the OWD_HOMEhsadmin directory
4) Modify the listener.ora file
   a) To listen the requests coming from the Oracle database,set the listener on the agent
   b) Modify the entries in the listener.ora file located in OWD_HOMEnetworkadmin directory
   c) Restart the listener after making modifications
5) Create an ODBC Source Module
   a) Create an ODBC Module from the Projects Navigator
   b) Click ‘Edit’ to open Non-Oracle location dialog box and enter the details as:
   i)  Check that service name and SID name are equal
   ii) Enter Host and Port number in the respective fields
   iii)Provide the login credentials for connection
   iv) The ‘Schema’ field can be left empty
   v)  Click ‘Test Connection’ to check for the connection details
6) Import Metadata from Excel using the Meta Data Import Wizard
   a) Select the table you want to import and right click to move it to the list of selected objects
   b) Click ‘Finish’ to import metadata
7) Create data to load mapping to the target table
   a) Create mappings in the module that contains  the target table
8) Deploy the Mapping
   a) Use the Control center manager to deploy the mapping created.Deploy the source module and later the mapping.

Related Questions