How to convert SQL table into Excel sheet and Excel sheet into a SQL Table

Introduction:

Sometimes a user wants to analyze their SQL query results in Microsoft Excel or import existing Excel data sheets as tables in SQL in order to do various data transformations.

In both scenarios we can achieve this goal as:

Converting Data from SQL table to Excel sheet:

• Go to tools option on the toolbar on SSMS and select options.

• In the window that opens select Query Results.

• Select Results to Grid from the drop down.

• Check Include column headers when copying results from the checkbox and click on the ok button.

• Click on the intersection of first row and first column of the SQL Table to select the entire table and then copy it by pressing ctrl + C or by right clicking and selecting copy .

• Open a new excel sheet and just paste the contents after selecting the first cell.

• If some columns show values as ##### , just increase the column width.

Converting Data from Excel sheet to SQL Table:

• Right click on the database where you want to import the excel sheet as table and go to tasks and select import data.

• Select Microsoft Excel as source from the dialog box

• Select the version of excel you want to convert into

• If the system gives error that microsoft.ace.oledb drivers are not found download it from Microsoft website based on what  driver version is required.

• Choose destination as SQL server Native client and enter the server name and credentials

• Select the sheet which you want to be converted into SQL as table.

• Click next

• Wait for the process to complete

• The new sheet is created as a table in SQL

Conclusion:

Thus we saw how we can convert SQL table into Excel sheet and vice versa

Thank you very much for reading, hope you enjoyed the article!