Create SQL Insert statements from a spreadsheet

I know this is probably old news to most but I was helping a friend out yesterday who didn’t know this little trick so I thought I would share it. While some of you may have access to production databases its pretty common that these servers are guarded by a DBA. If I get a huge spread sheet of data I can’t really import the data I need to send the sql statements to the dba and the script is run against the production database.

In this example I just got a spread sheet of 5 users that need to be imported into our users table.

Now for 5 users this is not a big deal but what about 100 or even a 1000 users. There is actually an easy way to create your insert statements using excel. First we will mark our D column as SQL. Next place click on the cell D1. What we are going to do is write a sql statement that will grab data from the columns a,b and c. First we write our normal insert statement but for the values we can evaluate the data in a cell using the following formula. The & is just used for concatenating.

Then we can use that same formula for every row in our sheet. Simply copy and then past that formula all the way down for as many records as you have and you will end up with something like this.

While this is great you should see the issue. The user id field is fine but we have no single quotes around our strings, but we can fix that using the concatenate function.

Now our insert statements look a lot better. Again, not the coolest thing in the world but it really helps out nicely in this spot.

Hi, I’m Dan Vega. I am a Software Engineer living just outside of one of my favorite places in the world, Cleveland Ohio. I am a self-taught programmer who firmly believes that you can accomplish anything in this life if you put your mind to it, roll up your sleeves and are willing to put in the work.

I have been writing software for over 17+ years now and I use my knowledge, passion, and influence to help other developers achieve their goals. I teach online and currently, have over 22,000 students.

When I am not writing software I have a number of things that really keep me busy. I love to read books, listen to podcasts and watch online courses. I have a real thirst for knowledge and it consumes me every single day of the week. I also love to run & lift heavy weights which keep me balanced throughout the week.

Please note: I reserve the right to delete comments that are offensive or off-topic.

  • Don’t have Excel on here, so I can’t try this… what happens if, instead of pasting the formula for each row, you just mouse over the bottom right corner of the first D cell until you get the repeat indicator. Then, just drag down into the other cells in the D column. Will it swap out the data and increment appropriately?

    (In Open Office, I get a + sign that tells me I will be "repeating with changes".)

    Just curious if it will behave. 🙂

  • That works as well, in the end your just copying the formula to the selected rows. Nice tip!

  • Been doing this for awhile when receiving large spreadsheets from clients…. such a handy and useful thing to know!

    Beats manually inserting rows and rows manually into a SQL db.

  • It really is! About 3 years ago I used to use excel to do this but I did it a really long way, this is way easier and cooler!

  • Just the other day I was thinking in my head what would be the quickest and dirtiest way to import a spreadsheet without having to rely on any of the new CF9 features. This is a great tip! Thanks for posting.

  • Lola LB

    Interesting . . . this is going to really come in handy for something I’m working on!

  • Dan

    On my local SQL Server, I just create a linked server pointing to an Excel file, dump that into a table, and export that table’s schema / data to a sql file. I like that because once I have it in the db, I can slice and dice the data.

    http://www.shinylight.com/2009/09/19/linked-servers/

  • Mahesh Padekar

    This is not working for columns with date value in it

  • Rob

    Really cool trick, 4 years and it still stands!

  • Michele

    Great article! Thank you so much for sharing, it helped me tremendously.

  • Peter

    Hi,

    I have created VBA macro for excel where you can easily generate this. It is also recognising the datatype and formatting SQL query accordingly:

    http://stackoverflow.com/questions/1570387/how-to-insert-data-from-an-excel-sheet-into-a-database-table/37409790#37409790

  • Kevin Michael

    Just wanted to convey thanks for a fellow Clevelander! I have a ton of data entry to do, and this is the perfect solution for me!

    • Thank you Kevin! Glad to have more Clevelanders visiting my website and even happier I was able to help you out.

  • Carlos Díaz

    Sorry man but this tutorial is not useful at all. When you try to teach something, do it with all examples. Anyone can do your example with no reading you.

    Tell me!

    With your example… how you will convert this?

    id = 1, first = Queen, last = Anne’s