A quick article on how to skip rows or even specify a range to extract from the Excel file when using as the DataSource.
Why?
If you are simply using a text file as your data source, then the options in the connection manager will let you skip rows and specify column datatypes. You don't get that with Excel but you can still control the data range.
How?
My example is that I have an excel sheet where the first 10 rows are descriptions to the column, and then I have 38 columns ("A" to "AL"):
- In the data flow, click on the Excel datasource component so that it's selected.
- View the Properties panel for this.
- Under Custom Properties » OpenRowset, you should see the name of the Excel worksheet you specified as the datasource (eg. "Sheet1$").
- Using the example above:
- Sheet1$A11:AL12 Selects all data from column A, row 11 up to Column AL, row 12 (so 2 rows will be returned).
- Sheet1$B11:AL Selects all data from column B, row 11 up to Column AL, row unspecified - used if I don't know how many rows there will be and if I want them all. NOTE: If you don't use column headings, F1 is the first column of the data range and NOT the first column of the data sheet (eg. F1 = B11) !!!
Additional
We add a conditional split task to the data flow which only passes through rows from the Excel sheet which are not blank/null. Browsing the net, I found this is sometimes also used for inserting blank rows to act as dividers.
Microsoft Certified Responses:
- "In the Excel connection you cannot skip Nth rows."
- "It is not possible."