Convert Excel to SQL insert statements

As an IT business analyst, you may have encountered situations where you need to convert Excel data into SQL insert or update statements. This can be necessary when setting up test data sets or mass uploading reference data for a new application.

In this article, I will guide you through the steps to successfully convert Excel to SQL insert statements. Whether you’re a beginner or an experienced analyst, these tips and tricks will help make the process smooth and efficient. Let’s get started!

Steps to convert Excel to SQL insert Statements

In this chapter, I will walk you through the steps to convert your Excel data into SQL insert statements. Hence, you can easily insert your Excel data into your SQL database.

These steps are not limited to insert statements only. You can easily apply them to other types of SQL statements, such as update, replace, or delete statements as well.

account table example, to convert excel to SQL script

Suppose you have an “account” table in your database having the following fields: ‘id’, ‘name’, ‘status’, ‘address’, and ‘website’.
And your Excel data contains these columns ‘id’, ‘name’, ‘status’, ‘address’, and ‘website’.
Add a new column to your Excel sheet to the left of the ‘website’ column. You can name it anything you like, but for this example, we’ll use “query”.
In the “query” column, use the “concatenate” Excel function to create your SQL insert statement. The first parameter of the concatenate function should be the static part of the SQL insert statement. Then, I alternate between the value of each related field and a comma separator. Be sure to add quotes around text values. You can refer to the formula bellow for guidance:

=CONCATENATE("insert into account (id,name,status,address,website) values(",A2,",'",B2,"','",C2,"','",D2,"','",E2,"');")

After writing the Excel formula for the first data row, copy and paste it to the remaining rows to generate all the SQL insert statements.
Copy the values of the “query” column and paste them into your database management system( in my case I use HeidiSQL)
Execute the SQL script.
Finally, view the data of the “account” table by executing a simple SQL select statement.

Select * from account;

I hope these tips and tricks will help you in the process of Excel to SQL conversion. You can also check the related video, where I show you step by step the above process.

Med B.

I hold a master’s degree in IT and management and my experience covers project management, IT business analysis, Salesforce administration and business process management, database analysis and design and digital transformation. I have worked for almost 20 years in different international and local companies in Morocco and have successfully delivered multiple projects.

You can learn more about me by visiting my LinkedIn page.