Importing an Excel Spreadsheet into an Oracle database with SQLDeveloper

It was late. In a corner of the Treasury however, a light burned brightly.
Phillip Hammond, Chancellor of the Exchequer, was still working.
“Spreadsheet Phil” was trying to formulate a plan to rectify the nation’s finances in the event that the Prime Minister was won over by the ebullient Mr Johnson and decided upon a “No Deal” Brexit.
Boris Johnson was currently based at the F.O. which, Phil considered darkly, was somewhat appropriate.
If only his predecessor had had the foresight to stick a couple of billion on Liecester City to win the league at 5000 to 1, The Country’s finances would be in much better shape.
In the midst of this gloomy reflection, he was struck by a sudden inspiration. The crowded half-hour that followed resulted in – what else – a spreadsheet.
The information therein could save The Economy. “Hmmm”, thought Phil, “this stuff is far too important to leave in Excel, I really need to put it into my Oracle database. I wonder if SQLDeveloper could help”…

The Spreadsheet

Being a cautious soul, Phil has decided to stick with something he knows quite well – the question of who is going to be the next Prime Minister of the United Kingdom of Great Britain and Northern Ireland including (for the moment at least), Scotland :

The workbook consists of two sheets – Party Leaders and Odds. At this stage, Phil just wants to upload the data in the Odds sheet.
The data in the decimal odds column is derived by means of a formula, for example :

As we will see, SQLDeveloper will upload the values in cells rather than any formulas that may be present.

The observant among you will also notice that the above screenshot is taken from Libre Office Calc rather than Excel.

As I’m using Ubuntu at the moment, I don’t have a version of Excel to hand. This wouldn’t be much of a problem except for the fact that the SQLDeveloper version I’m using (4.2), doesn’t offer the option of importing data from an ODS formatted spreadsheet.
Fortunately Calc can save a workbook in numerous formats…including XLSX.
You could pretend that the British Government has come to it’s senses and is saving billions in licensing by using open source software. However, in that scenario, we’d probably be using a different RDBMS so it looks like you’re stuck with the real politik of the situation.

Anyhow, this is our spreadsheet. It is in XLSX (Excel 2003 or later) format. We want to load the data from one of it’s two Worksheets into an Oracle database table…

The target table

The DDL for the target table is :

…and there is already some data in it :

You’ll notice that the table does not contain the same columns as the spreadsheet does. The columns it does have in common with the spreadsheet are in a different order.
Oh, and they have names that are different from the headings in the spreadsheet.
As Phil is about to find out, all of these potential problems can be easily overcome…

The SQLDeveloper Import Data Wizard

Firing up SQLDeveloper, Phil navigates to the NEXT_PM table.
Now, he could just use the right-click menu in the Tree view on the left of the screen, but he prefers to select the table and then invoke the Actions menu…

…and selecting Import Data…, which invokes the Data Import Wizard…

When Phil selects his spreadsheet, SQLDeveloper defaults to the first Worksheet. For some reason, it also insists on defaulting the file format to Excel 95-2003 (xls)…

…that’s easily changed, and Phil selects the appropriate Excel format before hitting the Worksheet drop-down to pick the appropriate worksheet

At first, SQLDeveloper isn’t too sure of about the column headings….

…but Phil just sets Skip Rows to 1 and…

Now Phil is happy with what he sees, he clicks Next

He then has a choice of Import methods. He could just opt to create an Insert script by setting the Input Method to Insert Script. Breaking the habit of a lifetime however, Phil throws caution to the wind and decides to Insert the data directly into the table. By leaving Send create script to SQL Worksheet checked, he’ll get an insert script anyway.

Time to click Next.

By default, all of the columns in the Worksheet are selected. Phil uses the left and right arrow buttons to choose only the columns that he wants to import into the table, and then the up and down arrows to specify the order …

Now, he clicks Next.

What he notices here are the red exclamation marks. SQLDeveloper is smart enough to point out that there are some issues to be sorted out…

Phil has a couple of options here. By clicking on a Source Data Column that has a red exclamation mark he can see information such as a Status explaining why the column has been marked by SQLDeveloper, the target column this data is currently pointing to in the target table, and even a sample of the data contained in the column…

He can then take the appropriate action to fix the problem. In this case, he could for example, specify that the Decimal column in the spreadsheet should be imported into the DECIMAL_ODDS table column.
However, as he’s just gone to all that trouble to get his columns in the correct order in the previous screen, he simply changes the Match By drop-down value from Name to Position :

Phil has one last little glitch to sort out, as can be seen from the Yellow exclamation mark on the DATE_ELECTED_AS_AN_MP source column.
In this case, it’s simply a question of setting an appropriate date format…

…and clicking Next

Finally, Phil has the opportunity to review his settings…

…and then, when he’s happy, hit the Finish button to import the data.

At this point, SQLDeveloper displays this reassuring message :

Now Phil can see that the data has been successfully loaded into the table whilst preserving the pre-existing record :

“Well”, thought Phil to himself, “I never knew that getting data from Excel into Oracle was so simple. Now, back to Brexit, it can’t be that hard…”