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.