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.
- Create a formula for the SQL statement in a blank cell of the spreadsheet’s first row of data.
- 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.
- Select every row that contains the sql and paste into your database query window.
- Run the queries and you are done.
Given the following spreadsheet for some customers, I will show a simple formula to do an insert into the customer table.
|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.