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.
Keith A Hyer
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.
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.
I would stage it, then query the table with a WHERE col1 IS NOT NULL and col2 IS NOT NULL, etc...
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.
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.
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: email@example.com .