Can you ignore blank rows from Excel in SSIS?

Who is online?  0 guests and 0 members
Home  »  Forums   »  microsoft business intelligence   »  integration services   » Can you ignore blank rows from Excel in SSIS?

Can you ignore blank rows from Excel in SSIS?

Topic RSS Feed

Posts under the topic: Can you ignore blank rows from Excel in SSIS?

Posted: 8/30/2011

Jedi Knight 1644  points  Jedi Knight
  • Joined on: 3/24/2010
  • Posts: 218

I just did an import from Excel where the last 6,200 rows in the sheet had contained data, but a block was deleted and then the sheet was imported.  What imported was the first 2,000 rows of "real" data plus the 6,200 rows of "empty" data.

I assume this to be an issue within the Excel driver / connection manager - I was just wondering if there was a setting to completely ignore blank rows when importing?

I know I could add the columns together with a derived column and then LTRIM & RTRIM and compare to empty string to eliminate them - I am just hoping for an easier, "ignore blank rows = true" kind of fix. 

 

Any thoughts?

Keith A Hyer

 

 


Posted: 8/30/2011

Jedi Master 3057  points  Jedi Master
  • Joined on: 2/19/2010
  • Posts: 449
Answered  Answered

I know this is not really what you are looking for but in this situation I put a Conditional Split immediately after the Excel data source and then test a field for ISNULL and/or LEN > 0 and then only pass the rows with data to the rest of the data flow.


Posted: 8/31/2011

Jedi Youngling 42  points  Jedi Youngling
  • Joined on: 12/3/2009
  • Posts: 11
Answered  Answered

Can you change anything on the Excel side?

In Excel, there is a difference between "clear contents" and "delete". I'm willing to bet that the contents were cleared, which is the default if using the "del" key rather than highlighting the full row and then left-click delete. If they were actually deleted in this manner, the range would be smaller.

You could also use a "Dynamic Named Range" in Excel.

 


Posted: 8/31/2011

Jedi Master 5646  points  Jedi Master
  • Joined on: 1/21/2010
  • Posts: 251
Answered  Answered

I would stage it, then query the table with a WHERE col1 IS NOT NULL and col2 IS NOT NULL, etc...

OR

I would just query the file itself when opening up the connection. You may be able to filter the null data out at the source.


Posted: 8/31/2011

Jedi Knight 1644  points  Jedi Knight
  • Joined on: 3/24/2010
  • Posts: 218

Thanks for the replies, gentlemen!

I marked them all as answers since each is a different, but correct approach.  I'll have to get with my boss and see which way they want to tackle it going forward.

 

Thanks again!

Keith


Posted: 1/4/2012

Jedi Youngling 11  points  Jedi Youngling
  • Joined on: 12/23/2010
  • Posts: 3

This can be done by using script componet. You can find out those row who having null value and ignore those

value using conditional split. If you still feel chalange ,send me data on my mail id: awadhesh.singh@milliman.com .

Thank you.

 


Page 1 of 1 (6 items)