This isn't really a blog, its more of a holding page for my domain (seems a shame not to have a page), if I know you then add me on either LinkedIn or Facebook (links are on the right), however if I don't know you then I won't add you!

Wednesday, 15 January 2014

Easy Oracle SQL inserts/imports with excel

Following on from my Excel based tool for carrying out updates from externally provided data here is a follow up tool for importing new data into a table.

As before its always frustrating when end users make the assumptions about data, users may sometimes request a spreadsheet of data is imported into a table.

Having done this numerous times using cobbled together formulas in Excel I decided to put together a generic template that you can use to carry out inserts into your Oracle database with significantly less pain and frustration!


Step 1 - Question the insert

Firstly before carrying out the insert you should ask yourself and the user providing your data the following questions;

  • Will issues be caused by manually importing into the table.
  • How long ago was the data in the spreadsheet updated, are other users aware that the update is taking place?
  • Does similar data already exist in the table, could the insert introduce duplication with queries/views? 
  • Is there enough data in the spreadsheet to create records correctly (based on table constraints)?
Based on that you can then decide whether to proceed with the update.

Step 2 - Format the data

Take the spreadsheet that you have been provided with and make the following amendments;
  • Check the data to ensure that it is formatted correctly, if the data has been amended manually users may have included rogue spaces, it is suggested that data is "cleaned" in a seperate spreadsheet before introducing the data to the insert template.
Step 3 - Transpose the data into the template

Paste the data from the spreadsheet your user has provided into the import template in cell B9.

Step 4 - Set the field names
  • Enter the table name to be updated in cell C4.
  • Identify each column in row 8 by its name in the database.
Step 5 - Review the generated SQL
  • The SQL generated as part of the template needs to be reviewed prior to execution.
  • Once satisfied that the SQL is correct paste the SQL into your client and execute the code.