Teaching a trick to easily import Excel Data into Oracle Autonomous Database (ADW and ATP)

The other day my boss shared with me a nice trick that I found it very useful and has simplified my life a lot, when creating demos or quick prototypes. For this reason and knowing how many people are quickly switching into using Oracle Autonomous Database (ADW or ATP), I thought it would be a good idea to be shared as a quick reference.

The trick consists in using SQL Developer to easily import Data from Excel spreadsheets, directly into Oracle Autonomous DB (ADW or ATP). This also opens up a nice wizard that helps create and configure new database tables to be created and then used to import the data. How cool is that?

This complements a previous article that explains how to provision and get started with Oracle Autonomous Transaction Processing Database. Also, we have published articles to get started with Oracle Autonomous Data Warehouse.

Remember, the difference is simple:

Ok, let’s get started.

Before we start

In this blog I assume the following:

  • You have an Oracle Cloud account, if not request a free trial. Here: https://cloud.oracle.com/tryit
  • Also, I assume that you have already provisioned a new Oracle Autonomous Transaction Processing Database. If not, have a look at this guide.

Ok then, let’s have fun!

Connect into your new Oracle Database instance using SQL Developer

In order to connect to our new Autonomous DB instance, we are going to use SQL Developer. You can use whatever other software you prefer by adjusting the following instructions respectively.

  • In SQL Developer, select New DB Connection.

  • Setup the configuration as follows:
    • Username: admin
    • Password: Enter the password that you used when provisioning the AUTONOMOUS DBC instance
    • Connection Type: Cloud PDB
    • Role: default
    • Configuration File: Select the Wallet (zip file) that you downloaded previously
    • Keystore Password: Select the wallet’s password that you entered when downloading your wallet.
    • Service: Select a service you want to use.

  • Makes sure you get a successful Test.

  • Save and then click Connect. You will find your connection on the left pane. Feel free to expand it.

  • Right click on “Tables” of new established connection and select “Import Data

  • A new wizard will open up

  • Click on Browse button and locate the Excel spreadsheet that you want to import it. If you want, you can try this sample file to continue with the example.

  • Notice that if your Excel file had multiple tabs (worksheets), they will appear as well. Select the one that you want to import. In this case, let’s continue with “Orders“. When ready, click Next.

  • In step 2, enter the table name that you want to create and then use to import your Excel data. Notice that you can re-arrange the columns if needed. When done, click Next.

  • Step 3 shows you the selected columns to be created. At this point you can still remove those you don’t need. When ready, click Next.

  • Step 4 lets you fine-tune your columns. Select their type, precision, nullable option, etc. Once you validate each field on the left, they will appear as bold. SQL Developer will try its best to deduce the best fit based on the type of data to be imported.
  • Once you have gone through each of your columns and confirmed types, options, etc. click on Next.

  • In step 5, validate all inputs and select Finish.

  • Validate that you get a successful message.

If you get an error that says insufficient privileges, make sure that the user is granted with the right privileges.

  • Now, simply refresh your connection. You should see your new table being created.

  • Expand your new table and confirm that it has all the columns that you specified. Also click on Data to display its content. It should be the content of your Excel worksheet.

How cool is that?

The best part is that the same applies to either ADW or ATP. That is, you have all you need to quickly start uploading data into Oracle Autonomous Database for either Analytics (Autonomous Data Warehouse) or Transaction Processing mixed workloads (Automated Transaction Processing).

I will keep publishing more advanced topics as I keep playing with the new generation of Oracle Autonomous Cloud Services. So, you better stay tuned!

I hope you found this blog useful. If you have any question or comment, feel free to contact me directly at https://www.linkedin.com/in/citurria/

Thanks for your time.

Author: Carlos Rodriguez Iturria

I am extremely passionate about people, technology and the most effective ways to connect the two by sharing my knowledge and experience. Working collaboratively with customers and partners inspires and excites me, especially when the outcome is noticeable valuable to a business and results in true innovation. I enjoy learning and teaching, as I recognise that this is a critical aspect of remaining at the forefront of technology in the modern era. Over the past 10+ years, I have developed and defined solutions that are reliable, secure and scalable, working closely with a diverse range of stakeholders. I enjoy leading engagements and am very active in the technical communities – both internal and external. I have stood out as a noticeable mentor running technology events across major cities in Australia and New Zealand, including various technology areas such as, Enterprise Integrations, API Management, Cloud Integration, IaaS and PaaS adoption, DevOps, Continuous Integration, Continuous Automation among others. In recent years, I have shaped my role and directed my capabilities towards educating and architecting benefits for customers using Oracle and AWS Cloud technologies. I get especially excited when I am able to position both as a way to exceed my customers’ expectations. I hold a bachelor degree in Computer Science and certifications in Oracle and AWS Solutions Architecture.

One thought on “Teaching a trick to easily import Excel Data into Oracle Autonomous Database (ADW and ATP)”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s