Problem
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?
Solution
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
- We can also store the SQL Command in a variable and use the variable to access the data from the Excel source.
- Check out these additional resources:
