locked
SSIS Excel import skip first rows RRS feed

  • Question

  • 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,
    V. A.
    Friday, February 5, 2010 1:57 PM

Answers

  • 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 6, 2010 4:06 PM
    Thursday, May 6, 2010 3:32 PM
  • 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?
    Hope this helps !!
    Sudeep   |    My Blog
    • Marked as answer by vita3 Friday, February 5, 2010 2:09 PM
    Friday, February 5, 2010 2: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.


    Sincerely SH -- Please kindly don’t forget to mark the post(s) that answered your question and/or vote for the post(s)
    • Marked as answer by vita3 Thursday, May 6, 2010 8:47 PM
    Thursday, May 6, 2010 8:06 PM

All replies

  • 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?
    Hope this helps !!
    Sudeep   |    My Blog
    • Marked as answer by vita3 Friday, February 5, 2010 2:09 PM
    Friday, February 5, 2010 2:06 PM
  • 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

    Lee
    Monday, February 8, 2010 7:32 PM
  • 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 6, 2010 4:06 PM
    Thursday, May 6, 2010 3:32 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.


    Sincerely SH -- Please kindly don’t forget to mark the post(s) that answered your question and/or vote for the post(s)
    • Marked as answer by vita3 Thursday, May 6, 2010 8:47 PM
    Thursday, May 6, 2010 8:06 PM
  • 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

    Friday, August 13, 2010 1:24 PM
  • SELECT

    * FROM

    [Sheet1$A1:C6]


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

     [Sheet1$A2:C6]


    Sincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).
    Friday, August 13, 2010 3:18 PM
  • 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/

     

    Tuesday, September 14, 2010 11:23 AM
  • Hello EvilDBA, If i need to skip rows using an SSIS Script task,how should i be using it .

    Thanks

    S


    Srikanth

    Wednesday, February 12, 2014 4:47 PM
  • 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?

    Thursday, October 23, 2014 9:47 PM
  • I have the same problem - spaces in the sheet name. Would anybody know how to get around this, please?
    Thursday, March 26, 2015 6:11 AM
  • I tried option one but it does not work, I get an OLE DB Error. "Opening a Rowset for '4075$A8:AA' failed. Check that the object exists in the database" .. Note 4075 is the name of the TAB i.e sheet and I want to start at Row 8 in the A column through the AA column and all data rows afterwards.'

    Using SSIS for Visual Studio 2015


    Lee

    Thursday, February 11, 2016 10:51 PM
  • OpneRowDataset and specifying column numbers like A6:AH never worked for me. The preview is blank when i specify cell range. 
    Friday, April 15, 2016 3:15 PM
  • Another option is to use OpenQuery.

    Create first a linked server for the file and use the query below:

    SELECT * FROM OPENQUERY ([LinkedServer Name], 'SELECT * FROM [Sheet With Space$A2:Z')

    Sunday, July 24, 2016 7:08 PM
  • Try removing the single quotes. Even a sheet name with a space is acceptable. (I'm using SSDT 2015.)
    Wednesday, December 21, 2016 10:55 PM
  • Can I do it without specifying the end column. Something like Sheet1$A6 ?

    Monday, September 23, 2019 3:41 PM
  • Hi Pavel,

    You need to specify or (1) a range or (2) Named Range.

    Check it out directly in Excel file itself. Whatever is working in Excel will work in SSIS.

    Monday, September 23, 2019 4:53 PM