Update: we have a newer, updated version of this guide here for 2022.

It’s normal to have data in an Excel workbook that needs to be converted into a SQL database. The easiest way by far is to use an online Excel to SQL converter like SQLizer. But if you’ve got some time to kill there are other methods. This post looks at three different ways of converting Excel to SQL.

First up: convert Excel to SQL using SQLizer.

Step 1: Select Excel as your file type

Step 2: Choose the Excel file you want to convert to SQL

Step 3: Select whether the first row contains data or column names

Step 4: Type the name of the Excel worksheet that holds your data

Step 5: Input the cell range of the data that you want to convert

Step 6: Type in a name for your database table

Step 7: Convert your file!

This process takes as little as 30 seconds - and here’s a video showing just that.

Next up…

Convert Excel to SQL using BULK INSERT statements in SQLServer

Compared to SQLizer, the BULK INSERT method is a laborious, manual task. But it works. Here are the steps you need to follow:

• Split your Excel sheets into single CSV files
• In your SQL database, manually create database tables and data types - for example, VARCHAR255
• Once you’ve done this, write the BULK INSERT statement

HOWEVER. This method will only work if the CSV files are on the same server/instance as your SQL server.

Check out this StackOverflow post for more information.

Technically this method is just a long-winded way of converting CSV files - which SQLizer can do much faster.

Covert Excel to SQL using CONCATENATE

This method requires some manipulation of the Excel file itself alongside using Excel’s CONCATENATE function.

• Manually create the destination database table in your SQL server
• To convert your Excel data into INSERT statements you’ll need to add new columns after each column of actual data. These columns should contain INSERT INTO dbo.ProductList VALUES (' ", " '' "," ' ") and look like this:

• Then you need to concatenate these new columns using Excel’s CONCATENATE function

• Copy the same formula to all rows and you’ll have your INSERT statements

To see more specific details on this method visit SQL And Me.

Automation is a good thing

Generally, things are automated for a good reason – normally because they’re time-consuming and waste precious human resources in the form of limited time. Manually converting Excel to SQL comes under this category.

We built SQLizer because we were fed up of manually converting files or writing scripts each time. Luckily for you, we’ve automated the process so you don’t ever need to feel our pain.

Start living a painless SQL life and convert your Excel file now.

SQLizer is free for less than 5,000 rows of data for personal use - if you have a >5000 row CSV file you need to convert, use code CONVERTME10 for 10% off your Pro Monthly plan for the first 12 months, or a one-off 10% discount on your Pro Annual plan.