How to read data from an Excel file starting from the nth row with SQL Server Integration Services

By:   |   Comments (4)   |   Related: More > Integration Services Excel


I have Excel files I need to read using SQL Server Integration Services (SSIS), but the first few rows in the file are just information about the data and these rows should be ignored. How can I read data from an Excel file starting from the nth row in SQL Server Integration Services?


The Excel source is one of the most used data sources in SQL Server Integration Services (SSIS). In this tip I will demonstrate how can we read Excel data starting from any row. Consider a scenario like the Excel file below screen where the data starts on row number 7.


There are multiple ways of reading Excel data from nth row and I will explain two methods in this tip.

Method 1: Using the OpenRowset Function

I have already created the SSIS package. It contains only an Excel Source in the Data Flow Task which will read data from the above mentioned Excel source file. The Data Flow task looks like the below image.


Let's preview the data. As you can see from the below image by default it reads all the rows, but my requirement is to start reading from the 7th row.


To achieve our task, right click on the Excel Source and select Properties, please refer to the below image.


In the Excel Source properties list you see OpenRowset as shown below, this contains the sheet name from which you are reading the data. Currently the OpenRowset value is Sheet1$, which means the Excel Source will read all of the data from Sheet1$. We have to make modifications here if we want to start reading the data from a specific row of this sheet.


As you know we need to read from the 7th row, so we have to update the OpenRowset value. For our example we will change this to Sheet1$A7:B100 which will allow us to read the data from Column A and Column B starting from the 7th row to the 100th row. As you can see from the below image, I have made the changes as per my requirement, but you can change as needed. Make sure you enter the correct sheet range in the OpenRowset properties, otherwise the Excel Source may not read the complete set of data.


Let's once again preview the data from the Excel Source. As you can see from the below image it now reads all the rows starting from the 7th row as per the requirement.


Method 2: Query Excel Sheet

We can write a SQL command to read data from the Excel file. In this method we will write a SQL Command to read data from the 7th row. Right click on the Excel Source and select Edit as shown below.


On the Editor screen, we need to change the Data Access Mode to SQL command on the Connection Manager page and write the SQL command to read the data as shown below.


Let's preview the data and as you can see from the below image it reads the correct data.


Now that you are reading the correct data using either of these methods you can continue on with your SSIS package development.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Ghanesh Prasad Ghanesh Prasad leads a team in Microsoft Business Intelligence and has over 5 years of experience working with SQL Server.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Wednesday, April 25, 2018 - 12:35:14 PM - Tarun Back To Top (75781)

 I have same issue with CSV file, I need to skip the first two rows and column header starts from 3 row.

Please help


Tuesday, March 27, 2018 - 11:08:39 AM - Scott Back To Top (75527)

Method 1 worked for me in testing some basic data.  But, with these methods, what if you do not know the end row of the data?  Data starts at cell A2, btu the length (rows) of the data varies.  How can you accoutn for that?


Tuesday, June 21, 2016 - 8:45:53 AM - Sriram L Back To Top (41730)

 Hi Ghanesh,

With regards to current article, if I have to read data from excel file starting nth row with SSIS and i don't know from which line the data starts, is there a way?


Sriram L


Friday, January 15, 2016 - 11:10:13 AM - Brian Henning Back To Top (40429)

I have to consume a lot of Excel-based data from workbooks maintained by other users. I've been successfull in getting them to use an Excel NAMED RANGE. This is especially handy since they like to re-arrange their worksheets and move the data around. The NAMED RANGE isolates me from these kinds of actions. It's very much like Method 2 but is a little more DBA-friendly.


get free sql tips
agree to terms