Excel Connection manager

Question

Excel Connection manager

ns.sharath

SSC Eights!

Points: 876
More actions
March 2, 2010 at 9:59 am

#139108

I have an excel file with 3 blank rows. The data starts from the 4th row only. Is there a way I can specify the excel connection manager to start accessing the data from 4th row only?

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 1

Not there.. You could potentially add a Conditional split after the Excel source on the Data-Flow and discard those rows.

CEWII

ns.sharath SSC Eights! Points: 876 More actions · Answer 2

ns.sharath

SSC Eights!

Points: 876

March 3, 2010 at 2:09 am

#1127588

Thanks Elliot

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 3

Elliott Whitlow

SSC Guru

Points: 102402

March 3, 2010 at 9:55 am

#1127904

You are welcome.

CEWII

P Jones SSChampion Points: 12361 More actions · Answer 4

Yes you can - I spent ages with this problem in both 2000 dts and 2005 ssis.

In your Excel data source set the data access mode as SQL Command and then the SQL Command text as

select * from [SheetName$A4:IV65535]

where sheetname is the name of the worksheet and the range is your data

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 5

Interesting. I think I might have learned something today..

CEWII

Adtjob Mr or Mrs. 500 Points: 526 More actions · Answer 6

Or alternatively what you could do is have a execute sql task after the insert into your table with a "DELETE FROM TABLENAME WHERE ROWSNOTWANTED=''

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 7

avinash jobanputra (3/4/2010)
Or alternatively what you could do is have a execute sql task after the insert into your table with a "DELETE FROM TABLENAME WHERE ROWSNOTWANTED=''

I have to say that in almost no case would that be ok, in many (most?) cases this method would cause issues in the data-flow pipeline. Therefore they would never have been inserted in the first place..

CEWII

divyanth SSCarpal Tunnel Points: 4303 More actions · Answer 8

P Jones (3/4/2010)
Yes you can - I spent ages with this problem in both 2000 dts and 2005 ssis.
In your Excel data source set the data access mode as SQL Command and then the SQL Command text as
select * from [SheetName$A4:IV65535]
where sheetname is the name of the worksheet and the range is your data

I had this problem previously and used the conditional split approach.. This is something new.. never saw it anywhere before..

what does that actually refer to ..i knw that 65535 is the max limit what does IV refer to... is it cell number in roman format??

If you want from 6th cell would it be [SheetName$A6:VI65535] ??? and i guess it works only with excel 2000 bcz the later versions can store way no of rows.. Does it wrk with later versions... if we just change the no of rows value from 65535 to the number that later versions can store..

Adtjob Mr or Mrs. 500 Points: 526 More actions · Answer 9

I meant at the end of the package flow So

step 1 Data flow task

a) it retrieves the excel file

b) it writes to the destination (sql table)

step 2 Execute SQL task

a) sql statement like

delete from Sheet1$

where id is null

I have tested this and it works.

Irrespective, this can be done a better way but its one way which potentially could work for you ns.sharath

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 10

I'm not saying it wouldn't work. But most of my data-flows have a lot between the source and the destination. Having NULLs or blanks will OFTEN cause them to fail, I'd have to add extra logic to handle that condition where it makes more sense to filter them off BEFORE I have to deal with them. What I'm saying is that while it might work, its not very functional.

CEWII

Adtjob Mr or Mrs. 500 Points: 526 More actions · Answer 11

Adtjob

Mr or Mrs. 500

Points: 526

March 4, 2010 at 12:48 pm

#1128722

Ok fair point