How to pick data from 4 th row of Excel with the help of SSIS package.

Question

Hi,

I have excel sheet, which having data from 4th row, please let me know, hot to pick data from 4tg row of excel with the help of SSIS package.

---

Amit
Please mark as answer if helpful
http://fascinatingsql.wordpress.com/

Answer 1

Hi,

Play around with the OpenRowset property of the Excel source.
(Check this blog post.)

Regards,
Zoli

Answer 2

You can also read all the lines and discard the first 3 with a conditional split.

---

MCTS, MCITP - Please mark posts as answered where appropriate.
Answer #1: Have you tried turning it off and on again?
Answer #2: It depends...

Answer 3

Hi Amit

If you are using Excel Source follow the following steps:

1. In Excel Source Editor Select SQL Command from data access mode

2. In SQL command text give the range of columns and rows e.g. if you want to select 1000 rows starting from row 4 from column A to Z the query will be

SELECT [SHEET1$A4:Z1004].*

FROM [SHEET1$A4:Z1004]

This statement will fetch the 1000 rows between column A and Z

In case you know the starting row but not the ending row change this query like

SELECT [SHEET1$A4:Z].*

FROM [SHEET1$A4:Z]

The above query selects all rows starting from row 4 between columns A and Z.

In case you are using OLE DB source use the same statement in OpenRowSet property 

I hope it will help you.

Cheers

Harris

Answer 4

You can also define a range in the Excel sheet, and that range will show up as one of the choices in the dropdown list in SSIS.  I prefer this method because with a named range, your users can "unintentionally" modify the spreadsheet a little, even though you told them not to, and the package won't break.

---

Talk to me now on

Answer 5

check answer ONE in this link

---

Sincerely SH -- MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

Answer 6

You can also define a range in the Excel sheet, and that range will show up as one of the choices in the dropdown list in SSIS.  I prefer this method because with a named range, your users can "unintentionally" modify the spreadsheet a little, even though you told them not to, and the package won't break.

Correct me if i am wrong, you can not  "define a range in the Excel sheet" through SSIS ( without installing Excel component)? right?

the source file is always made by the customers how can you get or set a define range sheet from the customer? you have no control, my question is that how do you handle your excel file to be range defined ?

thnaks Todd

---

Sincerely SH -- MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

Answer 7

Correct me if i am wrong, you can not  "define a range in the Excel sheet" through SSIS ( without installing Excel component)? right?

If you have the Office Interop libraries installed, you can create a named range using a script task:

http://weblogs.asp.net/jdanforth/archive/2008/08/18/excel-automation-example-with-named-ranges.aspx

---

MCTS, MCITP - Please mark posts as answered where appropriate.
Answer #1: Have you tried turning it off and on again?
Answer #2: It depends...

Answer 8

If you have the Office Interop libraries installed, .....

so I'm right, you have to install something to do that.

thanks Koen

---

Sincerely SH -- MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

Answer 9

Correct, Nik.

I'm talking about techniques to intelligently set up a "template" sheet that your business customers can fill in.  If (and that's sometimes a big if) you can supply such a template, I always try to use named ranges...

---

Talk to me now on

Answer 10

Correct, Nik.

I'm talking about techniques to intelligently set up a "template" sheet that your business customers can fill in.  If (and that's sometimes a big if) you can supply such a template, I always try to use named ranges...

Thanks Todd thats very very good,

Questions:

1- So if i provide a excel sheet for the customer and set the RANGE within excel, what ever range they use will be captures in the excel RANGE?

2- can they shift/move the columns arround? ( i assume yes)

3- can they shift/move the columns arround? ( this one i dont know, havent tested it)

4- can you eamil me a Excel sample ([e-mail]) thanks

---

Sincerely SH -- MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

Answer 11

Don't know that I have a good sample to send - but I'll probably have to make one for a conference coming up (DevTeach Vancouver).

Your "customer" can shift the range around as a block - but if they insert columns in the middle, or rearrange columns, you're toast... unless you have headers turned on, then you're OK.  (Extra columns will be ignored and rearranged columns will be "found" via the column header.)

Even given that resiliency, I still prefer to lock the spreadsheet down as much as possible.  The range is usually only for my benefit when I choose to edit the template - not to allow the "user" to be able to muck with it.  So I usually protect the sheet (except for the range contents) so that the "user" can't move or alter the sheet structure.

To answer your first question - an Excel Range is simply a pointer to a block of cells.  You can define a range named "Data" as B5:D10000, with headers on row 5, and whatever they want anywhere else.  When you read the spreadsheet in SSIS - if you read the range "Data" - that gets "translated" to B5:D10000.  If you (or they) edit the spreadsheet to insert a column between A and B, then the range definition (kept in the Excel spreadsheet) changes to C5:E10000.  Your SSIS package still refers to "Data", and when it opens the Excel sheet, the Excel sheet will translate that to C5:E10000.  It's like a view or synonym in SQL (but not exactly!).

---

Talk to me now on

Answer 12

Thanks Todd, it was very good.

---

Sincerely SH -- MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).