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 18+ years now and I use my knowledge, passion, and influence to help other developers achieve their goals. I teach online and currently, have over 30,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.