locked
Skipping leading rows in Excel RRS feed

  • Question

  • Hi All,

    While we are loading the text file to SQL Server via SSIS, we have the provision to skip any number of leading rows from the source and load the data to SQL server. Is there any provision to do the same for Excel file.

    The source Excel file for me has some description in the leading 5 rows, I want to skip it and start the data load from the row 6. Please provide your thoughts on this.

     

    Thursday, August 30, 2007 2:51 PM

Answers

  • Easiest would be to give each row a number (a bit like an identity in SQL Server) and then use a conditional split to filter out everything where the number <=5

     

    There's an article on SQLIS.com that explains how to give each row a number but that site is down at the moment. Sad

     

    -Jamie

     

     

    Thursday, August 30, 2007 4:08 PM

All replies

  •  

    you can't do this in the source adapter but you can filter them out once they enter the pipeline.

     

    -Jamie

     

    Thursday, August 30, 2007 3:23 PM
  • You mean the "Pipeline" as after loading the data to SQL server. Please clarrify.

    Thursday, August 30, 2007 3:47 PM
  •  Dhanasu wrote:

    You mean the "Pipeline" as after loading the data to SQL server. Please clarrify.

     

    "The pipeline" is another name for SSIS's dataflow task.

     

    -Jamie

    Thursday, August 30, 2007 3:51 PM
  •  

    Okay, thanks.

     

    How should i do in the Pipeleine? Please advice.

    Thursday, August 30, 2007 3:58 PM
  • Easiest would be to give each row a number (a bit like an identity in SQL Server) and then use a conditional split to filter out everything where the number <=5

     

    There's an article on SQLIS.com that explains how to give each row a number but that site is down at the moment. Sad

     

    -Jamie

     

     

    Thursday, August 30, 2007 4:08 PM
  • If you know the format of the excel file in advance, you can use the OpenRowset() property on the Excel Datasource. Something like Sheet1$A6:Z5000 will skip the first 5 rows.

    Thursday, November 29, 2007 8:28 PM
  • The method above works perfectly except when a worksheet name has a space (" ") in it. Does anyone have a solution as I have tried each of the following:

    * [New Sheet1$]A6:Z5000
    * 'New Sheet1$'A6:Z5000
    * 'New Sheet1$A6:Z5000'
    * [New Sheet1$A6:Z5000]

    All receiving a the following error:

    "Error at Stage Bulksheet Data [New Worksheet [837]]: Opening a rowset for "[New Sheet1$]A5:K65000" failed. Check that the object exists in the database."
    Tuesday, September 9, 2008 10:02 PM
  • Did you try this?

    'New Sheet1'$A6:Z5000

    Tuesday, July 19, 2011 12:56 PM
  • I just came across with a similar scenario. I tried it without using any [] or '' and it worked.

     

    Try using this:

    New Sheet1$A6:Z5000

    • Proposed as answer by HeinrichAngela Monday, August 11, 2014 8:39 AM
    Sunday, September 18, 2011 9:25 AM
  • I just came across with a similar scenario. I tried it without using any [] or '' and it worked.

     

    Try using this:

    New Sheet1$A6:Z5000

    Ashfaqh suggestion works.
    SSIS will self-wrap your query in "" quotes.
    Monday, August 11, 2014 8:43 AM
  • In "Table or view" mode, i use this:

    New Sheet1$A6:Z

    In "SQL command" mode, i use this:

    SELECT F1,F3,F5 FROM [New Sheet1$A6:Z]

    it worked.

    Monday, March 30, 2015 9:32 AM