Connect a SQL Server database to your workbook

Last week I shared the ODBC connection method for connecting SQL Server data to Excel.  Now that you’ve mastered the intricacies of that technique, I want to share a simpler method.

You can use the Data Connection Wizard to create a dynamic connection between a SQL Server database and your Excel workbook.

This type of connection is read-only  —  you cannot update any data in the database from your workbook.

Here is a link to the article:

https://support.office.com/en-us/article/Connect-a-SQL-Server-database-to-your-workbook-22C39D8D-5B60-4D7E-9D4B-CE6680D43BAD

 

I will be attending PASS SQL Saturday on March 11 – I HOPE TO SEE YOU THERE ! !

http://www.sqlsaturday.com/592/eventhome.aspx

It will be a mind-blowing event that should not be missed . . .

 

 

 

 

Excel save-as CSV options

Here is little trick to change the CSV save-as option to use something other then a comma. Sometimes you need to make an output file with a delimiter other than a comma, tab or space. Excel does not come with an option to save a file as pipe delimited. You have to make a system change to accomplish this. This option is in the strangest place and you would never think to look there.

This link will show you the easy way to make the change. Be aware that when you make this change, you make it for the computer not just Excel.

http://superuser.com/questions/783060/excel-save-as-csv-options-possible-to-change-comma-to-pipe-or-tab-instead

Connect a SQL Server database to your Excel workbook

How many times a day do you open a table or run a procedure and want to review the results in Excel. You right click on the result set to select all, select copy with header, open Excel, select the entire sheet and format as text then paste. There is a better way. Under the data menu in Excel you will find some menu selections for pulling in external data. If you select “From Other Source” you will see that there is a selection for SQL Server. With the server name and your windows authentication you will be presented with a list of Views and Tables that you can select from in each database. It is fast and easy to open a table for review in Excel. The data connection wizard creates an ODC connection to the data and pulls it into Excel. Once the connection is made you will be able to reuse the ODC connection anytime via Existing Connections. Check it out.

Please keep in mind that this process for selecting data from a table is the same as if you opened a table without any criteria, it returns the entire table. Do not use this for production unless you know how many records you will be retrieving. The impact on production can be substantial.

https://support.office.com/en-us/article/Connect-a-SQL-Server-database-to-your-workbook-22c39d8d-5b60-4d7e-9d4b-ce6680d43bad