Forum Discussion

copco_online's avatar
copco_online
Copper Contributor
Feb 10, 2022

Mapping Stored Procedure Query Results to Excel Worksheet Columns

I have an Excel Workbook where for each sheet I load data from a stored procedure.  The worksheet refreshes the data in the order of the procedure. However, if I insert a column into the procedure's resultset, that column gets added to the end of the range.

 

Moving the column then affects the formatting, etc..

 

Is it possible to define the mapping so that new columns can be inserted into the existing sheet and those new columns in the resultset mapping properly?

 

So, if I return fields FieldA, FieldB, FieldC...

The excel sheet will show fields FieldA, FieldB, FieldC in that order.

If I add a field such that the result set looks like FieldA, FieldNEW, FieldB, FieldC...

The excel sheet currently shows fields FieldA, FieldB, FieldC, FieldNEW.

I want FieldNEW to show as the second column.

 

Thank you,

 

- Nick

developer
excel

3 Replies

  • Martin_Weiss's avatar
    Martin_Weiss
    Bronze Contributor
    Feb 11, 2022

    Hi copco_online 

     

    actually it is the default behaviour that the output columns are in the same order as the input columns.

    I suspect that you have somewhere in the query a step "Reordered Columns"

     

    If that's the case, than this is the cause for the behaviour you describe. Just delete that step, if appropriate.

     

    • copco_online's avatar
      copco_online
      Copper Contributor
      Feb 11, 2022
      Hello DTE,

      Thank you for the response. I'm not sure if there is a "step" called "Reordered Columns". The source is a stored procedure, and the query cannot be shown graphically within Excel. Is it possible that I'm just not seeing where this is? It seems to me that some sort of mapping is established at the onset when the query definition is constructed. When a field is added in the output of the stored procedure where we would like it, Excel not knowing or recognizing the mapping simply puts it at the end. Where should I look for this Reordered Columns step? Thanks.
      • Martin_Weiss's avatar
        Martin_Weiss
        Bronze Contributor
        Feb 14, 2022

        Hi copco_online ,

         

        if it's an external stored procedure, then it might be difficult. But maybe you could try the following:

         

        Open the Queries & Connections pane and check, if there is a connection listed there. If so, you could do a right-click on it and theck the properties.

        If you are lucky, there is a connection string where you can see a SQL statement. 

        This could be the place where the sort order of the fields are defined.

        But there is also a good chance that you do not find anything here. Unfortunately inn this case, I do not have any further advise for you.

         

         

Resources