Export to Excel Sheets Column not on the First Row

  • What is the most direct way of exporting from SQL Server to several Excel Worksheets in a Workbook where the column headings are not on the first row because there are report headings or aggregates before the column names in each of the sheets?

    Any help would be greatly appreciated.

    Thanks.

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • I was hoping that someone would be able to make a suggestion.

    The first 3 columns on most Worksheet Contain a Heading.

    The Column Names are on the fourth Row.

    Is there a was to map to the column names in the Excel Sheet if they do not belong on the first row?

    Currently there is a lot of macro code and it is a mess and hard to read.

    I could easily do this in a VBA Code or a macro after I populate the WorkSheet but they do not want that.

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Why do you need to know the column names? If you deselect the option "Column Names in First Row" for the excel output, then you're just left with "F1", "F2", etc ... and then you can map them just based on their order. The output should go to the first unoccupied row.

  • kramaswamy (11/1/2011)


    Why do you need to know the column names? If you deselect the option "Column Names in First Row" for the excel output, then you're just left with "F1", "F2", etc ... and then you can map them just based on their order. The output should go to the first unoccupied row.

    I initially Selected it and mapped it using a Data Conversion task.

    I loaded the data.

    Then I went into a clean Excel Document added the header.

    When I open the package I get the message" THe external metadata column collection is out of synchronization with the data source.

    I haven't seem to find where to change this setting (uncheck).

    Is it possible without have to re-map?

    Probably not, right?

    I like to map the columns to the headers because there are so many and I'm not that familiar with this data.

    Thanks for you help.

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Nope, no way you can do it without re-mapping. If you tell SSIS that you want to use the first row as column headers, and then you change the column headers, itt'l tell you that the metadata is out of sync.

    As far as I know, the only way of doing what you're trying to do, is to just not use the first row as column headers, and refer to them by their column numbers instead.

  • I thought that after I posted the question.

    Makes sense.

    Thanks for your help!

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply