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!

Saturday, 27 July 2013

Easy Oracle SQL updates with excel

Its always frustrating when end users make the assumptions about data, often users will export data into a spreadsheet and believe it is easy to reimport this into the database.

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 updates into your Oracle database with significantly less pain and frustration!


Step 1 - Question the update

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

  • How long ago was the data in the spreadsheet updated, are other users aware that the update is taking place?
  • Is the user/other users aware that data entered into the database since the spreadsheet was created will be overwritten with the data contained in the spreadsheet.
  • Is there enough in the spreadsheet to match up to the records correctly (primary key etc)?
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;
  • Ensure the first column includes one of the fields that will identify the record in the destination table (i,e, personcode, learnerid or staffnumber)
  • Check the data to ensure that it is formatted correctly, if the data has been amended manually users may have included spaces etc.
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, table name and field type 
  • Enter the table name to be updated in cell C4.
  • Identify each column in row 8 by its name in the database.
  • Identify each column in row 7 by its type;
    • Primary key will be used to construct the where clause
    • Update field will be the fields being updated
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.