Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


By:   |   Read Comments (2)   |   Related Tips: More > Integration Services Excel


Latest on-demand video "Optimize SQL Server Performance" (watch now for free)


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.

Excel_Source

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.

Importing_Excel_Data_To_Destination

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.

Excel_Source_Preview_Data.JPG

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

Excel_Source_Properties

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.

Excel_Source_Properties_List

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.

Excel_Source_OpenRowSet_Update

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.

Excel_Source_Preview_Data_After_OpenRowSet_Update.JPG

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.

Excel_Source_Edit

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.

Excel_Source_SQL_Command

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

Excel_Source_Preview_Data_After_OpenRowSet_Update.JPG

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

Next Steps


Last Update:


signup button

next tip button



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.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



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

 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?

 

Regards,
Sriram L

 


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

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.

 


Learn more about SQL Server tools