Hello,
1. Is it possible during import data from Excel to DB table skip first 6 rows for example?
2. Also Excel data divided by sections with headers. Is it possible for example to skip every 12th row?

Thank you,

1.In Excel connection you cannot remove the 1st n rows as you want.
2. Not possible.

You will have to use Script component to read the data but that can be unstable.

Can you not get the data in the excel in proper format?

You can try either of the following for your questions:

1)  Assuming data in worksheet named sheet1 and columns F1, F2, F3: 

SELECT     TOP 6 F1, F2, F3
FROM         [Sheet1$]

2)  Complex queries will not work vs. Excel (can't use row_number() for instance). If you had a column that had an ascending number, you could do:

SELECT   * 
FROM [Sheet1$]
WHERE COL4 MOD 12 <> 0

www.texastoo.com

1.In Excel connection you cannot remove the 1st n rows as you want.
2. Not possible.

1.  YES YOU CAN.  Actually, you can do this very easily if you know the number columns that will be imported from your Excel file.  In your Data Flow task, you will need to set the "OpenRowset" Custom Property of your Excel Connection (right-click your Excel connection > Properties; in the Properties window, look for OpenRowset under Custom Properties).  To ignore the first 5 rows in Sheet1, and import columns A-M, you would enter the following value for OpenRowset:  Sheet1$A6:M  (notice, I did not specify a row number for column M.  You can enter a row number if you like, but in my case the number of rows can vary from one iteration to the next)

2. AGAIN, YES YOU CAN.  You can import the data using a conditional split.  You'd configure the conditional split to look for something in each row that uniquely identifies it as a header row; skip the rows that match this 'header logic'.  Another option would be to import all the rows and then remove the header rows using a SQL script in the database...like a cursor that deletes every 12th row.  Or you could add an identity field with seed/increment of 1/1 and then delete all rows with row numbers that divide perfectly by 12.  Something like that...

 

• Marked as answer by vita3 Thursday, May 06, 2010 4:06 PM

check this http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/20944506-5276-41e7-8b65-7f36d6e4bdaa

you will find 2 types of solution for what you are looking for.

a bit late and not a direct response to the question, anyway:

to selectively import a range (columns, rows) you can also give a range directly into the sql-clause:

SELECT
 * FROM
 [Sheet1$A1:C6]

and further refine your selection (within the result) by an additional WHERE-clause

SELECT

* FROM

[Sheet1$A1:C6]

to skip the first row it must be A2 (not A1)

 [Sheet1$A2:C6]

yes it is possible.

you need to use the openrowset property for this. check the link below.

http://sql.geekepisodes.com/2010/ssis-skipping-rows-and-columns-in-excel/

 

Hello EvilDBA, If i need to skip rows using an SSIS Script task,how should i be using it .

Thanks

S

I'm struggling with this because the automated sheet name I'm trying to import has spaces in it. It works perfectly with Sheet1, but not when the sheet has a space.

Do you know how to resolve that?

I have the same problem - spaces in the sheet name. Would anybody know how to get around this, please?