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.
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,
- Once you’ve done this, write the
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.
Start living a painless SQL life and convert your Excel file now.