This tutorial is to help those who have designed and developed a database with Oracle SQL, now are curious to know how their tables look like. One way to be able to view this tables and your data is to upload those tables to excel spreadsheets.
First and foremost, you have set the link between the database and excel. This is achieved in the following ways:
Go to your windows start menu, select control panel, and select administrative tools. Once you have selected the administrative tools, you should see this environment like so,
Double click on the Data Source Administrator (ODBC) option, you'll find the following window:
Select Excel files and click add to display the create new data source window. Here you'll be able to add your database that you want to link with Excel. Then click finish to make the connection with your database service.
Once the finish button has been clicked you're ready to set your connection through your database ODBC driver configuration. Enter a data source name of your choosing, just so that you remember what you did in the future, enter a description. The next option is mandatory for a successful connection between you oracle database and excel. Choose your database service name for your particular oracle database, and also enter the user ID like so:
Now you can click on the test connection to see if you succeeded, if you succeeded you'll find your new link created in the Data Source Administrator window like so:
If you have made it thus far you're ready to upload your tables to excel spreadsheets, just click OK button to finish.
This part is for those who have no idea how to know what their database service name is. To find out go to the windows start menu and type services.msc on the search bar and press enter. This will take you straight to all your windows services but you're only interested in your database service name, so navigate to oracle and click on the option that says ORACLESERVICE... The name following ORACLESERVICE is your service name.
If this service is not started like mine get it started by clicking the start option, and also start the listener too.
Now we are ready to upload our tables to excel; start by opening excel spreadsheet. On the spreadsheet select the data option at the top of the menu bar,
after that select From Other Sources, from the drop down list choose From Microsoft Query, you'll find the data source window with the new connection (dExcel) we just created.
Select the link dExcel or whatever you must have named yours and click OK, then you'll be prompted for your oracle database password, enter your password and click OK. Now you'll find all the available tables and columns in your database, choose the tables you'd like to display on you spreadsheet. In my case I just selected the contact table from my video store tables.
Click next for sorting options and where you'd want to start the display on the Excel spreadsheet.
This shows you where your table will be displayed from on the spreadsheet. In case I choose this default option this how my table looks once I press OK
Now you have your data displayed all for you to see.