How to fetch data from Excel using SSIS from a particular row ?

13
1515

Aim :  How can we fetch data from excel using SSIS from a particular row or middle of the excel sheet. Description :  During my recent project development, I faced this interesting scenario. My client sent me the data in excel file and told me to export the data from that given Excel to local SQL Server machine. Final output was to create a report according to the data present in database. The problem was we had data at different places in the same excel sheet. I mean to say the data was not present continuous in excel file. To put my point more clearly I will explain it in simple way. We need to fetch the data from the 10th row onwards and need to ignore first 9 rows. I was very curious at that time and started thinking about how to ignore the first 9 rows and how to fetch the data from 10th row on words. My brain was not helping me in finding the solution as I forgot to eat almonds that day. I then opted the best option i.e. I started googling for the solution. After searching too many links I found the right solution to my question i.e. fetch data from excel using SSIS from a particular row. So, I’m sharing it with you also. The following are the steps which we need to follow while creating an ssis package to fetch data from excel using SSIS from a particular row. Let’s see this interesting solution.

Steps to Fetch Data from Exel using SSIS

Step 1 – Excel Source. Below is the excel sheet which contains the data at various different rows. But, we need to fetch it from 10th row onwards. fetch data from excel source in ssis Step 2 – SSIS Package Configuration. excel source in ssis

  1. Create one SSIS package and name it as say, Sample.dtsx.
  2. Now, Drag and drop one Dataflow task.
  3. Now, Drag and drop one excel source.
  4. Double click on Excel source to configure excel properties.
  5. Create excel connection manager and give the path of that excel file.
  6. Once you are done with the configuration of excel source. It looks like below picture.
  7. Click on “OK” to finish the excel source configurations.

Note :- Here I’m not going to show the configuration part of excel source. I am sure you all must be expert in configuring excel source. configure excel source editor in ssis Step 3 – Excel Source property window.

  • Now, we are going to play a small trick here to fetch data from excel using SSIS starting from the 10throw (specific row).
  • Select Excel source and press “F4” to get properties window. Once you done the above step it will bring up the properties window. Below is the screen shot for same. Now go to Custom properties option. Here we can see that the value for OpenRowset option is the name of the excel sheet i.e. Sheet1$.

excel source OpenRowset properties in ssis

  • We will change this OpenRowset property value = A10:D20.
  • In this, A10 will indicate the starting position of the data and D20 indicates the ending position of the data.
  • We can also observe the same in the source of excel sheet I.e. where our data starts and where it ends. It started at A10 and Ended at D20. This is the only reason I’ve taken the same in OpenRowset property. You can configure it as per your data and need.
  • One important question is if we have huge data in our excel sheet. For example, if we have lacks of rows. In this case, it is difficult to find the end point and it may take a long. So, simple solution is – Your data starts at A10 and ends up with same column D but we don’t know the exact number of rows when the data ends up in column D. So, for this situation you can set OpenRowset property to   “ OpenRowset=A10:D ”.

Step 4 – Excel Source OpenRowset property configuration.

  1. In our case we can clearly see that the data starts at A10 and Ends at D20. So, we will consider the same range.
  2. Now we will configure our OpenRowset property =A10:D20. The windows will look like image below.
  3. Simply click on “OK” and you are done with the solution to this interesting problem. That’s it.

Configure excel source properties in ssis Step 5 – OLEDB destination.

  1. Drag and drop OLEDB destination to get the output.
  2. Create a connection between Excel source and OLEDB destination.
  3. Configure your OLEDB destination.
  4. Launch time now. Run the sample.dtsx package.
  5. To verify the output, Go to SQL server and open table EXCEL_FILE.

OLEDB destination in ssis Conclusion :  In the above result you can clearly observe that the employee with the name Seshu was skipped. The reason behind this is – Employee named “Seshu” record was not in the range of A10:D20. That’s why it is not considered in the output. With this we come to an end of this article, I hope you liked this article on how to fetch data from excel using SSIS from a particular row. you can give your ratings and comments below. We will highly appreciate your feedback.

VN:F [1.9.22_1171]
Your Feedback hels to Improve
Rating: 9.6/10 (23 votes cast)
VN:F [1.9.22_1171]
Rating: +19 (from 19 votes)
How to fetch data from Excel using SSIS from a particular row ?, 9.6 out of 10 based on 23 ratings

13 COMMENTS

  1. Your articles are very good and easy to understand.Thanks a lot for sharing your knowledge.
    We appreciate your hard work .

  2. Hi Avinash,

    Nice example. Can we do it different way instead of change the openrowset value from Sjheet1 to A10:D20. We can directly write on sql commad in data access mode in excel source to get the data from any rows and column : Select * from [Sheet1$A10:D20].

    Please correct me if I am wrong.

    Thanks
    Ashish Kumar !!!

  3. Hi Avinash,

    in the same scenario i need a requirement like only need empid and sal columns data.

    Thanks
    Nagarjuna.

Comments are closed.