How to Import the Excel Data into the SQL Server

In today’s world, it is essential to be able to seamlessly transfer the data between different systems. When it comes to managing the data in a relational database system like SQL Server, one common task is importing the data from external sources like Excel spreadsheets. Excel is a widely used tool to manage and analyse the data, and it can be useful to import the data from Excel into the SQL Server for various purposes such as data warehousing, reporting, and analysis. There are various methods available to import the Excel data into the SQL Server, each with its own benefits and limitations.

In this article, we will discuss the most commonly used methods to import the Excel data into the SQL Server and provide examples of T-SQL queries to perform the import operation.

Methods to Import the Excel Data into the SQL Server

Using the SQL Server Import Wizard

The SQL Server Import and Export Wizard is a powerful tool that can be used to import the Excel data into the SQL Server. The wizard provides a user-friendly interface that guides you through the process of importing the data. Here are the following steps to use the “Import and Export Wizard”:

1. Right-click the database and select Tasks -> Import Data.

2. Select “Microsoft Excel” as the data source in the Import Wizard dialog box. Now, browse to the Excel file that you want to import the data from and select the Excel sheet that contains the data that you need to import. Then, click “Next”.

3. Select “Microsoft OLE DB Provider for SQL Server” as the destination in the Destination dialog box. Now, enter the server’s name and authentication details for the SQL Server database that you want to import the data into. Then, select the database and table that you want to import the data into.

4. Map the columns from the Excel source to the corresponding columns in the destination table.

5. Click “Next” to preview the data and configure any additional settings such as error handling and identity columns.

6. Click “Finish” to save the configuration and import the data into the SQL Server.

Using the T-SQL Commands

You can use the T-SQL commands to import the Excel data into the SQL Server using the OPENROWSET function. The OPENROWSET function allows you to read the data from an external source such as an Excel file, and insert it into a table in the SQL Server. To import the Excel data into the SQL Server using the T-SQL commands, follow these steps:

1. Create a new table in the SQL Server that matches the structure of the Excel sheet that you want to import the data from.

For example: If the Excel sheet has columns for Name, Phone Number, Stream, Company Place, and Job Location, create a table with columns for Name, Phone Number, Stream, Company Place, and Job Location.

CREATE TABLE dbo.sheet2$ (

Name VARCHAR(50),

PhoneNumber VARCHAR(20),

Stream VARCHAR(50),

CompanyPlaced VARCHAR(50),

JobLocation VARCHAR(50)

)

2. Write a T-SQL command in a new query window that uses the OPENROWSET function to read the data from the Excel file. Insert it into the table that you created. Here is an example command that you can use:

INSERT INTO dbo.sheet2$ (Name, PhoneNumber, Stream, CompanyPlaced, JobLocation )

SELECT Name, PhoneNumber, Stream, CompanyPlaced, JobLocation

FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',

'Excel 12.0;Database=[C:\Users\Somdeb\Desktop\Students.xls];HDR=YES',

'SELECT * FROM [sheet2$]')

Output:

Name Phone Number Stream Company Placed Job location

1 Arnab Das 9876543210 Engineering Infosys Kolkata

2 Riya Patel 8765432109 Medicine Apollo Hospitals Mumbai

3 Advait Pal 7654321098 Law TATA Group Delhi

4 Anjali Singh 6543210987 Arts Wipro Limited Chennai

3. Execute the T-SQL command to import the data from the Excel sheet into the SQL Server table.

Note: Before executing the T-SQL command, you may need to enable the Ad Hoc Distributed Queries option on your SQL Server.

sp_configure 'show advanced options', 1;

RECONFIGURE;

GO

sp_configure 'Ad Hoc Distributed Queries', 1;

RECONFIGURE;

GO

Using the Import Flat File

Another simple method to import the data into the SQL Server is using the “Import Flat File” wizard in the SQL Server Management Studio. This method is useful when you have a large file with a fixed or delimited format such as a CSV file or a tab-delimited file. Here are the steps to import the data using the “Import Flat File” wizard:

1. Right-click on the database where you want to import the data and select “Tasks” -> “Import Flat File” from the context menu.

2. Browse to the location of the CSV or excel file that you want to import. You can also select the format of the flat file and preview the data. Specify the name of table in the “New table name” box. Once you specify the options, click “Next” to proceed.

3. You can preview the data that will be imported in the “Preview Data” screen and modify the mappings between the source and destination columns if necessary. Once you specify the options, click “Next” to proceed.

4. Review the summary of the import process in the “Summary” screen and click “Finish” to complete the import.

5. Once the import is complete, you can review the imported data in the destination table in the database.

Simply run a SELECT statement against the table where you imported the data to confirm the import of the Excel or CSV file.

SELECT * FROM dbo.familyCSV;

Output:

FamilyMembers Age Profession

1 Ajay 42 Engineer

2 Sayani 38 Homemaker

3 Rohit 24 Freelancer

4 Ritu 11 Student

Conclusion

Importing the data from Microsoft Excel into the SQL Server is a common task that can be accomplished using various methods including SSIS, the Import and Export Wizard, and T-SQL commands. Each method has its own advantages and disadvantages. The best method for your situation depends on different factors such as the size and complexity of the data, the frequency of the import, and your technical skills and resources. By following the steps that we outlined in this article, you can successfully import the Excel data into the SQL Server.