Integrating Excel files with SQL Server is one of the most common requirements for businesses that rely on Excel for reporting and analysis. Whether you’re consolidating financial reports, retail sales files, or manufacturing logs, the ability to query Excel files directly in SQL Server removes manual steps and creates a single source of truth.
SQL Server provides two efficient methods for this: OPENROWSET and OPENDATASOURCE. These functions allow you to query Excel files directly inside SQL without creating a permanent linked server.
At Addend Analytics, a certified Microsoft Solutions Partner, we use these techniques to help clients integrate Excel into Power BI dashboards, SSRS reports, and Microsoft Fabric pipelines—accelerating decision-making and reducing data silos.
SQL Server provides various methods to achieve this, and in this blog post, we’ll explore two of them: OPENROWSET and OPENDATASOURCE. We’ll provide examples, code snippets, and snapshots to guide you through the process.
Prerequisites:
Before we begin, make sure you have the following:
1. Microsoft SQL Server installed and running.
2. The Excel file you want to access.
Method 1: Using OPENROWSET
OPENROWSET is an SQL Server function that allows you to access remote data sources, including Excel files. You can use it to read data from an Excel file directly into SQL Server.
The OPENROWSET function can be referenced in the FROM clause of a query as if it were a table name. The OPENROWSET function can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement, subject to the capabilities of the OLE DB provider. Although the query might return multiple result sets, OPENROWSET returns only the first one.
Step 1: Enable Ad Hoc Distributed Queries
sp_configure ‘show advanced options’, 1;
RECONFIGURE;
sp_configure ‘Ad Hoc Distributed Queries’, 1;
RECONFIGURE;
Step 2: Query Excel Data Using OPENROWSET
SELECT *
FROM OPENROWSET(
‘Microsoft.ACE.OLEDB.12.0’,
‘Excel 12.0; Database=C:\Data\EmployeeData.xlsx; HDR=YES’,
‘SELECT * FROM [Sheet1$]’
);
You can adjust the SELECT statement to retrieve specific data from your Excel sheet.
Method 2: Using OPENDATASOURCE
OPENDATASOURCE is another method for accessing external data sources in SQL Server. It’s very similar to OPENROWSET but provides a more structured way of specifying the data source.
The OPENDATASOURCE function can be used in the same Transact-SQL syntax locations as a linked-server name. Therefore, OPENDATASOURCE can be used as the first part of a four-part name that refers to a table or view name in a SELECT, INSERT, UPDATE, or DELETE statement, or to a remote stored procedure in an EXECUTE statement. When executing remote stored procedures, OPENDATASOURCE should refer to another instance of SQL Server. OPENDATASOURCE does not accept variables for its arguments.
Step 1: Query Excel Data Using OPENDATASOURCE
SELECT *
FROM OPENDATASOURCE(
‘Microsoft.ACE.OLEDB.12.0’,
‘Data Source=C:\Data\EmployeeData.xlsx; Extended Properties=Excel 12.0’
)…[Sheet1$];
Replace the file path with the actual location of your Excel file.
Both OPENROWSET and OPENDATASOURCE are powerful tools for accessing Excel files in SQL Server. They allow you to read data from Excel sheets directly into your database, making it easier to integrate external data sources with your SQL Server environment.
When using these methods, remember to specify the correct Excel file path and provide the necessary connection properties, such as the OLE DB provider and version. Additionally, ensure that you have the required permissions to access the Excel file.
With these techniques, you can efficiently work with Excel data within your SQL Server database, making data integration and analysis tasks more manageable and efficient.
Expanded Insights & Business Value
· For Power BI users: Once Excel data is in SQL, it can be connected to Power BI dashboards for automated, real-time reporting.
· For manufacturing: Machine log Excel files can be uploaded into SQL and visualized with Power BI manufacturing dashboards.
· For retail analytics: Sales data in Excel can be consolidated into SQL and used for inventory optimization.
· For finance teams: Monthly Excel reconciliations can be centralized in SQL for compliance reporting.
Talk to a SQL + Power BI Expert Today
FAQs
Q1. What’s the difference between OPENROWSET and OPENDATASOURCE?
OPENROWSET is better for ad hoc queries, while OPENDATASOURCE is more structured and suited for repeatable processes.
Q2. Do I need to set up a linked server for this?
No. Both functions allow you to query Excel without creating a permanent linked server.
Q3. Can large Excel files be queried?
Yes, but performance may degrade. For large datasets, consider staging data or using SSIS, Azure Data Factory, or Microsoft Fabric pipelines.
Q4. How does this benefit Power BI projects?
Importing Excel into SQL creates a clean data model that can be used for DAX calculations, visualizations, and row-level security in Power BI.
Q5. Is this secure for enterprise data?
Yes, but ensure proper SQL permissions and file access security are in place.
Importing Excel into SQL Server with OPENROWSET and OPENDATASOURCE helps businesses eliminate manual work, centralize data, and power advanced analytics.
At Addend Analytics, we deliver end-to-end Power BI consulting services, helping companies integrate Excel, ERP, CRM, and IoT data into SQL Server to unlock actionable insights.
· 70% faster reporting cycles
· 25% higher operational efficiency
· Secure, scalable architecture
Get a Free ROI Consultation with Addend Analytics
