Converting Data From Excel to Oracle (2)

Part
01
of one
Part
01

Converting Data From Excel to Oracle - 32 bit

A person can connect Excel to Oracle from various data sources such as Power Query, Legacy Wizard, Query Wizard, Microsoft Query, and PowerPivot using ODBC drivers. To be successful, both the Excel and ODBC software installed on the system must be compatible in either 64-bits or 32-bits; our focus is on the 32- bits.

Installing A 32-bit Office(Excel) And ODBC Softwares

  • When installing a 32-bit office that is expected to run on a 32-bit ODBC software, the installer would have to select the 32-bit package before beginning to run an installation. One would have to select the 32-bit installation for the ODBC software for an efficient and hitch-free query process that will synchronize with the Excel as well.
  • Once the two installations are done in 32-bit form, the following process can be used to connect Excel to Oracle from various data sources such as Power Query, Legacy Wizard, Query Wizard, Microsoft Query, and PowerPivot using ODBC drivers.
  • The following process can be followed for installing ODBC Driver for Oracle on a Windows machine by following the illustration on the graphic user interface.
  • The OCBD driver can be configured to connect to Oracle by the use of this graphic user interface. All necessary add-in in Excels would also be added for the needed OS.

Excel To Oracle From Various Data Sources

Connecting Excel To Oracle With Get & Transform (Power Query)

  • Click the Data tab in Excel, then expand the Get Data dropdown list. Click From Other Sources, then click From ODBC.
  • From ODBC dialog, choose your data source name (DSN). Configured your ODBC driver if it has not been done, expand the Advanced Options dialog box, and "enter the connection string for your data source (without credentials, which are defined in the credentials dialog box in the next step). Additionally, you can enter an SQL statement that will be executed right after establishing a connection to the data source. Click OK."
  • When connecting using a database username or password, select Database and enter your credentials in the dialog box, then click Connect.
  • If the Database is not password-protected or there is a specified credential in the ODBC data source settings, select Default or Custom and press Connect.
  • "In the window that appears, select the table you want to retrieve data from, and click Load."
  • Data from the table will be displayed in an Excel spreadsheet for further analysis.

Connecting Excel To Oracle With Data Connection Wizard (Legacy Wizard)

  • One can use these steps to connect to OLE DB or "ODBC external data source that has already been defined."
  • "In the opened dialog, select ODBC DSN and click Next to continue."
  • "Now select a data source you want to connect to, and click Next."
  • "To connect to the table containing the required data, select its name and click Next to enter and save information about your new file or click Finish."
  • "In the Import Data dialog, you can select the way your data will be viewed in Excel and the place where to put it in the worksheet, and click OK."

Connecting Excel To Oracle with the Query Wizard

  • "You can use this option to create a simple query for retrieving data from Oracle to Excel via ODBC driver."
  • "Open Excel, in the main menu, click the Data tab."
  • "Click the From Other Sources dropdown menu, and then click From Microsoft Query."
  • "In the appeared dialog, you can choose the data source you want to connect to."
  • "After a successful connection, you can select the data you want to be displayed in Excel and click Next."
  • "The next two steps allow filtering and sorting the data. Click Next to skip these procedures."
  • "If you plan to use the query further, you can save it by clicking the Save button on the right."
  • "Select Return Data To Microsoft Excel and click Finish."
  • "In the Import Data dialog, you can select the way your data will be viewed in Excel and the place where to put it in the worksheet, and click OK."
  • "The required data is successfully imported to Excel."

Connecting Excel To Oracle with Microsoft Query

  • "You can use this option to create a more complex query for retrieving Oracle data to Excel via ODBC driver."
  • "Start Excel, click the Data tab."
  • "In the appeared ribbon, click From Other Sources, and then click From Microsoft Query."
  • "In the next dialog, choose the data source you want to connect to (e.g., using data source name — Devart ODBC Oracle). Uncheck Use the Query Wizard to Create/Edit Queries and click OK."
  • "Now, you can select the tables you want to add to your query. When you finish, click the Add button."
  • "In the graphical editor, you can filter rows or columns of data, sort data, join multiple tables, create a parameter query, etc."

Connecting Excel To Oracle with PowerPivot

  • "You can use PowerPivot — an Excel add-in to perform data analysis and create complex data models. To load the required data, do the following:"
  • "In Excel, click the PowerPivot tab, then click Manage to go to the PowerPivot window."
  • "In the opened window, click From Other Sources."
  • "When the Table Import Wizard opens, select Others (OLEDB/ODBC) and click Next."
  • "In the Specify a Connection String window, click the Build button."
  • "In the Data Link Properties dialog, specify the data source you want to connect (e.g., using data source name — Devart ODBC Oracle), and then click Next."
  • "Now, you should choose how to import the data (either select a table from the list or write a query to specify the data to be imported)."
  • "When the Import operation succeeded, click the Close button. The retrieved data is inserted in the active worksheet."

Research Methodology

The team considered it necessary to use quotation marks on most instruction sets on how to connect Excel to Oracle from various data sources such as Power Query, Legacy Wizard, Query Wizard, Microsoft Query, and PowerPivot using ODBC drivers.
Sources
Sources