Connect Excel DB to MS Sql Server

Mansi Mehan 1 Reputation point
2020-09-10T15:44:08.97+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
8,502 questions
Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,339 questions
{count} votes

3 answers

Sort by: Most helpful
  1. Jayson Sainsbury 26 Reputation points
    2020-09-10T17:48:17.843+00:00

    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!

    No comments

  2. Emi Zhang-MSFT 10,536 Reputation points Microsoft Employee
    2020-09-11T06:06:23.623+00:00

    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.

    No comments

  3. Yitzhak Khabinsky 19,856 Reputation points
    2020-09-16T12:22:05.447+00:00

    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:

    25266-microsoftaceoledb120.png

    No comments