Introduction
Welcome to Excel Jet Consult blog post. In this tutorial, we are going to learn how to import excel data to Azure SQL Database via SQL Server Management Studio. We will also look at how to connect with the same data (export to Excel). Let’s get started
Sample File
In the caption below, we have the sample Excel data to import to Azure SQL Server. Note, the data is formatted in Excel Table and descriptive Table name is provided
Azure Portal
- Login to portal.azure.com
- Click on SQL Servers
- Click on Add
- In the Create SQL Server Database under the Basics, provide the following:
- Resource Group, Server Name, Location, Server Admin Login, Password, Confirm Password
- Click on Review + Create
In the next page, we have all the information entered in the previous page as seen below
- Click on Create
At the top right corner, we will see Submitting deployment. When we click on it, we will see: Your deployment is underway. After the deployment is successful, we have the screen below
Click on Go to resource. We have fully create the SQL Server
Create Database
Next is to create the database.
- Click on Create Database
- In the Create SQL Database, provide the Database name
- Click on Review + Create
- Click on Create
- After the deployment is completed, click on Go to resource
SQL Server Management Studio
- In the SQL Server Management Studio, click on Connect and select Database Engine
- In the Server name, copy the server name from the Azure Portal and paste. See the server name in the caption below
- Select Authentication drop-down and choose SQL Server Authentication
- Provide Login and Password
- Click on Connect
Your client IP address does not have access to the server is displayed. To fix this, return to the Azure Portal
- Click on the Database name and click on Set Server Firewall
- Click on Add client IP and click on Save
- Return to SQL Server Management Studio and click on Connect
Access is granted and the Azure SQL Server Database in available as seen below
Import Excel Data
To import the Excel Data:
- Right-click on the Database which is Azure Global Bootcamp
- Select Tasks
- Select Import Data
- Select Microsoft Excel from the Data Source drop-down
- Click on Browse to select the Excel file
- Select Microsoft Office 97-2003 from the Excel version drop-down
- Click Next
- Select SQL Server Native Client 11.0 from the Destination drop-down
- Click Next
- Check Use SQL Server Authentication box and provide the Login and Password
- Click Next and Next
- Select Data$ and click Next and Finish
The data is successfully imported into the Azure SQL Database. To see the data, go back to Azure Portal on the web
- Click on the Database and click on Query editor (preview)
- Provide Password and click on OK
From the caption below, we can see the data under Tables
This complete the steps to importing data from Excel to Azure SQL Database
Export Data To Excel
To export the data to Excel:
Open a new workbook or use existing workbook
In the Get & Transform group of the Data tab, click on Get Data
Select From Azure
Select From Azure SQL Database
In the SQL Server Database, paste the Server name
Click OK
In the next dialogue box, select Database and provide the username and password
Click on Connect
Select the Data from the Azure Global Bootcamp database. We can see the data in the preview window to the right
To load as a Table to Excel, click on Load
The data is loaded to Excel. We can just remove the first two rows. This complete the circle of importing and exporting data from Excel to Azure SQL Database and back to Excel.
You can watch the video below and subscribe to my YouTube channel