Know that Excel is one fantastic tool for performing such data analysis. But, at times, having the data that you need into Excel can be troublesome and such would take much time when you would go through the other systems. Also, you are at the mercy of how a different system would export data, and may require other step between exporting as well as getting the data into such format which you need. When you have access to such database in which the data is stored or housed, you may circumvent such steps and create your respective Excel SQL query. Click here to find out more on how to Populate data table from JSON String in Excel.
To start, select the “Get Data” “From Database” or “From SQL Server Database” as being shown in the screen grab. In such point, it can pop-up a prompt to enter the server name and also the target database which you are wanting to query. You may obtain this information from such SSMS. You may enter such information and then choose OK. This will permit you to browse such available tables from such database to import. You may remove those columns and filter tables prior to importing. When you do not know how you can write SQL queries, then this is an approach which you can certainly take.
But, when you choose the “Advanced” dropdown arrow, then you may create your own Excel SQL query. You may create your query in the SSMS or the Visual Studio and then you can just paste such final query in the window. This is because of the reason that there has no intellisense in such window and this can be hard to spot errors in the query.
There is also making an Excel SQL query with the use of VBA. Using such VBA to make your Excel SQL query isn’t really that straight forward as the past approach, but it can still be quite a useful method depending on the situation. It would be great that the data isn’t returned to the table unless you designate this to be so. Such technique is going to work on the older versions of Microsoft Excel but won’t work on the Mac OSX versions of Excel because this uses and ADO connection. Click here to learn ways in Creating a Dependent Dropdown in Excel.
There is also making a query with the use of the Microsoft query. Such option is likely the most complex one but this comes with an added advantage of being compatible with some of the versions of the Mac OSX.
Find out how Microsoft Excel works at http://www.youtube.com/watch?v=IBT_sBzFcOA.