Connect Excel DB to MS Sql Server

Question

I just installed MS SQL Server Management Studio and I need help connecting to an Excel database.

Answer 1

I think you are confused about the technology or are asking the wrong question. An Excel file is not a database and Management Studio is merely the UI tool used to connect to a MS SQL Server instance. If you have a SQL server instance in your environment, you could import the excel sheet into a database and use SSMS to query and analyze it but you cannot connect directly to a Excel spreadsheet.

I hope this clears things up!

Answer 2

Hi @Mansi Mehan ,
Are you looking for the way import Excel to SQL Server?
If yes, please refer to this support article:
import-data-from-excel-to-sql
If my understanding is incorrect, please provide more information about this problem and I'm glad to help you.

---

If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Hi @Mansi Mehan ,

As @Jayson Sainsbury correctly pointed out, MS Excel file is not a database.

Microsoft ACE OLEDB Provider allows to query MS Excel files like a virtual DB table on a file system.

First, you need to check if the ACE provider is installed. There are 3 versions of it: 12, 15, and 16.

Here is how to check in SSMS if the ACE provider is installed:

EXEC master.sys.sp_MSset_oledb_prop;  

And, finally here is how to query MS Excel file via T-SQL:

SELECT *  
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',  
   'Excel 12.0 Xml; HDR=NO; IMEX=1;  
    Database=c:\Users\Yitzhak\Documents\dada.xlsx',  
    [Sheet1$]);  

Here is how it looks like: