Home > Database, mysql, Oracle > Generate SQL queries from Excel document

Generate SQL queries from Excel document

October 28th, 2011

Hi All,
Long time no see, eh?!
That’s what happens when you start working for utilization targets..
When the business needs some Excel to be processed through a database in a certain manner I tend to save lots of time by generating the SQL queries automatically inside the Excel document.
Example:
You have an Excel document that depicts table structure inside your database. the Excel document contains the latest updated by the business version of some variables that should be updated inside your database. (Here many of you will ask the logical question, who does stuff in such a rigid and obsolete way? And I have nothing to say in my defense)
What I do is to pickup the important columns that form my query and generate in in a free columns on the right hand side.
="update table_name set colum_name_1="&F2&" where column_name_2='"&A2&"' and column_name_3='"&C2&"';"

For F2=5, A2=STRING1, C2=STRING2 the result will be the following:

update table_name set column_name_1=5 where column_name_2='STRING1' and column_name_3='STRING2';

You should be careful with the escape characters and quotations to make the different data types work.

HTH,

Spas

Categories: Database, mysql, Oracle Tags:
  1. ismail
    October 29th, 2012 at 06:27 | #1

    How do I get the update string to work, if the column has date type.

    I tried to format the date columns in the specific format that sql understands ‘yyyy-mm-dd’
    but when I build the string and reference the date it gives me rubbish formatted data. is there a way to overcome this ?

    thanks

Comments are closed.