Corey Coogan

Python, .Net, C#, ASP.NET MVC, Architecture and Design

  • Subscribe

  • Archives

  • Blog Stats

    • 109,869 hits
  • Meta

Use Excel to Automate Data Entry

Posted by coreycoogan on January 27, 2011

Over the course of my career, I have frequently been given random excel spreadsheets that contain data that needs to be either inserted or updated to a database. There are many routes one can go with this task, most of which are more complicated then necessary. I find the simplest solution is to use Excel formulas to generate my SQL statements for me.

  1. Create a formula for the SQL statement in a blank cell of the spreadsheet’s first row of data.
  2. Copy and paste the formula to the same blank cell in every other row. This fastest way I know to do the paste is to: select the first cell you wish to paste.  Hold down CTL+SHIFT+END.  This will select everything from the point of selection to the last row and last cell in the spreadsheet.  At this point, continue to hold down shift and arrow back to the left to get to the original column and hit enter.

    With the cursor on the cell with the formula, Excel outlines the cell with a tiny square on the bottom right corner.  Double click on that tiny square and your formula is repeated all the way to where the data ends next to it.
    (Thanks to Todd Boehm for posting this to the comments)

    The formula will now be copied to each row.

  3. Select every row that contains the sql and paste into your database query window.
  4. Run the queries and you are done.

An Example

Given the following spreadsheet for some customers, I will show a simple formula to do an insert into the customer table.

A B C D
1 NAME PHONE SIGNUP_DATE
2 Joe Smith (920) 555-1112 5/21/2010 = “insert into CUSTOMER (name,phone,signupDate) values (‘” & A2 & “‘, ‘” & B2 & “‘, ‘” & TEXT(C2,”M/dd/yyyy”) & “‘)”

Convert Excel Date to Text or String

Notice the use of the TEXT function against the value for SIGNUP_DATE.  This is necessary with dates because Excel will spit that value out in a serial format, which isn’t what we want in our database.  Use the TEXT function to convert the Excel Date to text for the insert.

Advertisements

One Response to “Use Excel to Automate Data Entry”

  1. TBoehm30 said

    An easier way to populate the formula is to create the first one in the column directly to the right of your data (just to the left works as well). With the cursor on the cell with the formula, Excel outlines the cell with a tiny square on the bottom right corner. Double click on that tiny square and your formula is repeated all the way to where the data ends next to it.

    This works also if you have a simple formula to populate. For example, put 1 and 2 in the first 2 cells, highlight them both and double click – Excel will keep counting to the bottom. This works for days of the week, month, etc.

    If your column is not next to the data then I like to go End Up. So I will select my formula cell for copying and then move to my data column. Hit “End” then “Down” to go to the bottom. Then move back over to my formula column and hold the shift key down and hit “End” then “Up” to highlight the column. You can now paste. I feel this is easier than the Ctrl+Shift+End and moving around to find your data.

Sorry, the comment form is closed at this time.

 
%d bloggers like this: