Extract CRM data from the backend into Excel

General way of exporting CRM data into excel is by out-of-box export functionality that CRM provides, it works very well but you will see the performance hit on CRM Application server because everything has to go through CRM web services. Alternate way for this one is to get the data directly from SQL Server through the data tab in MS Office Excel. “From Microsoft Query” option is powerful, it supports SQL queries to tables, views and much more.

Let’s go thru this in simple steps:                     

1. select Data tab –> From Other Sources –> From Microsoft Query.                                                             
2. On Choose Data source window, select <New Data Source> , click OK.                                                  
3. On Create New Data source window give some name : MyQuery1,  select driver as “SQL Server”, click connect.                                                                                                                                                  
4. On SQL server login window give Server name, leave the default options and click OK.                              
5. you would come back to “Create New Data Source” window and here leave option 4 blank and click OK.
6. you would come back to “Choose Data source” window, make sure you the new data source is selected and click OK.                                                                                                                                          
7. “Microsoft Query” window opens up, click the SQL button from toolbar to enter the SQL query , sample query that I have used here is to retrieve phone call activities that were created on 10/06/2011:

select fap.subject , fap.activitytypecodename, fap.statecodename , fap.prioritycodename  , fap.createdon, fap.modifiedon , fap.description   from [Trading_Technologies_MSCRM].[dbo].[FilteredActivityPointer] fap
where (fap.createdon >'20101006') and (fap.createdon < '20101007')
and (fap.activitytypecodename = 'Phone Call')

8. Click OK on SQL window, you would see the result data in Microsoft Query window                                    
9. On this window, File –>  “Return Data to Excel”

10. You would see a “Import Data” window popped up, leave the default selections and click OK, you would see some halt – it is retrieving the data (check the status bar on Excel) and the you will see excel sheet populated with data:

That is it, hope this would be useful for you in CRM and non-CRM scenarios