There are times when you need to manipulate data with excel before importing it into a database.
Excel does have functionality which can be used to connect the spreadsheet directly to a data-source such as a SQL database, however this is not always practical.

Using Excel formula’s you can easily create the raw SQL statements which can be executed on the database without the need for third-party plugins or special tools.

Creating Insert Statement’s

In this example we are wanting to insert 5 rows containing a staff members idNo and Name.

1) The first step is to arrange the data in the layout you want it to be inserted.

2) Double click into the next blank column beside you’re first row and insert the following formula.
= “insert into table_name (first_column,second_column) values ( ‘” & A2& “‘, ‘” & B2& “‘);”

3) You will need to start changing the 3 default formula values: table_name, first_column, second_column and cell no’s if they are different from this example. In this example we are using staff as the table name, idNo as the first column and Name as the second column.
If you are inserting more fields you will need to expand the formula by adding the extra column information.
For example: = “insert into staff (idNo,Name, Age) values ( ‘” & A2& “‘, ‘” & B2& “‘, ‘” & D2& “‘);”

4) Click enter when you have finished modifying the required fields, you should see your SQL insert Statement for the first column.

 

5) Click on the right hand corner of the SQL statement and drag it down to the last column.

6)When you let go, excel should automatically generate the remaining SQL statements. These can be copied out and executed on your database.

Creating Update Statements

A similar technique can be used to create ‘Update’ statements. In this example we are updating the ‘rrp’ field of the ‘stock’ table for a particular ‘partNo’. The ‘partName’ column is unused in this example and is typical of what you may find in the real world, you should always update rows using the tables unique ‘Primary Key’.

1) Like the first example, layout your data into columns

2) Double click into the next blank column beside you’re first row and insert the following formula.
= “update table_name set column_value_to_update = ‘” & C2 & “‘ where column_primaryKey = ‘” & A2 & “‘;”

3) Change the 3 default formula values: table_name, column_value_to_update, column_primaryKey and the cell no’s if they are different from this example.

4) When you are happy with the formula, drag down the cell to generate the SQL statement

 

Did you find this guide useful?
Leave us a commend below