Additional menu

Tachytelic.net

Alias names in Microsoft Query no longer work in recent versions of Office

by 17 Comments

I’m using Office 2010 and Office 2013 to aggregate data from a SQL data source.

In the past I have had no problems aliasing column names in MS Query but found today that it no longer seemed to work.

It’s not a big problem for regular columns, but for aggregate columns you really need the column name intact if you are going to use the data in Excel for the purposes of working wiht a Pivot Table.

I tried various ways to get this working again and found that query would modify the SQL source and just remove the aliases.

How to use alias column names in MS-Query

I found two solutions to this:

1) Wrap your new column name around square braces, for example “Select odrtyp as [OrderType]”

2) select ‘OrderType’=ordtyp

Just a quick thing, but hope it helps someone.  There are a couple of MS articles that detail a similar problem, which didn’t seem to have any impact on my system.

 

Reader Interactions

Comments

  2. Joe says

    I had the same problem with MS Query. I frequently paste very complex SQL into MSQuery to pull data from Oracle, Netezza, etc. and do the analysis in a Pivot. One day aliases worked fine and the next they didn’t. I couldn’t find a workable solution (I refuse to write VBA for something that should just work) on the web so I tried one last thing and it works!

    Just write a really simple query like select * from [table] from a small table from the same database that your are pulling data. Return the results to excel. Then click on the Refresh drop down in the ribbon and select Connection Properties. Go to the Definition tab and delete the simple SQL that you wrote from the Command Text box. Paste your SQL with aliases into the Command Text box. Click OK and wallaa! It works!

    Somehow the MS Query wizard is attempting to “help” us by erasing the aliased names when we try to paste in the SQL at that step in the process but this “help” doesn’t get executed when replacing SQL in the Command Text in Refresh -> Connection Properties -> Definition after the query has already been created.

  3. Joe says

    I forgot to mention that the [] solution didn’t work for me in case you were wondering 🙂

  4. Bernard Faucher says

    To what Joe mentinned, you do not even need to create a new query, just replace the existing SQL text put in by MS-Query (without aliases) with your SQL with aliases, and everything will do just fine.

    Thank you all for your help with this puzzling matter.

  6. Dan Troxell says

    I tried both F0301.A5cmc1 as [Area] and it did not work, when I looked at the SQL the as [Area] was gone. Using ‘Area’=F0301.A5cmc1 gave SQL validation error. Trying to connect to DB2 400 on an IBM iSeries.

    Though it looked like a good fix. I’ll keep searching.

  7. Dan Troxell says

    I found this solution on another site that worked for me. Clumsy, but it did the trick for me.

    Curiously, while MS Query will remove all aliases used in a SQL statement, modifying the command text in the Connection Properties, Definition tab allowed aliases. At least when all columns had aliases were the aliases reflected in the worksheet. I had tried a single column without success, but when the alias wasn’t erased I tried naming all of them.

  8. Doug says

    Nice tip. Worked for me …eventually.

    I had an Excel doc updating by SQL. I wanted the column headers to have different text than the actual field names. Only one issue: I had to start fresh on a new sheet at least twice. It seems that I couldn’t get it to recognize my column name changes in the sql itself; the columns just wouldn’t show up. I guess it would confuse the Excel parsing logic. So my addition tip: get your sql worked out with the aliases first..before you put it in whatever container your working with.

  13. Christoph says

    Thanks a ton! I select with an complex SQL against an ODBC Sybase connection, and now it seems to work even if you only cast the first column name in brackets OR simply use a line comment using two dashes:

    select a as [i], b as ii from…
    works as well as
    select a as i — line comment
    , b as ii
    from….

  14. Jordi VL says

    Thanks, great, another ‘HELP’ of ms-query is that automatically changes the position of the columns in a random way, when I modify the query or simple add a new field to extract, if somebody sorted out this please let me know.

Leave a Reply

Your email address will not be published. Required fields are marked *