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

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.

 

11 thoughts on “Alias names in Microsoft Query no longer work in recent versions of Office

  1. 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.

  2. 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.

  3. 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.

  4. 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.

  5. 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.

Leave a Reply

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