Office Data Connection files

Excel has its own method of importing external data from a variety of sources, including data in the SQL Server. This method can be embedded directly in the workbook or stored in a separate file, the Office Data Connection (ODC) file. When this .odc file is created, it can be reused over and over for a quick connection between your GP and Excel that is refreshable.

Creating an .odc file

Let's create an .odc file to bring in our vendors using these steps:

1. In Excel 2016, go to Data | From Other Sources | From SQL Server:
   
2. The Data Connection window will open. Enter the name of your server and your GP log in credentials. Click on Next.
   

   Tip

   If you do not know the server or database names, consult your IT department or your GP Partner. Usually, the server name is the name of the machine on which the SQL Server is installed.

3. Select the database you want to report on and then select the view called Vendors. Click on Finish:
   

   Tip

   The database name can be found in the upper-right corner on the Company Setup window in GP.

   In the Excel Data Connection Wizard window, first, SQL Views are displayed in alphabetical order; then, SQL Tables are displayed in alphabetical order.

4. The Import Data window will open, providing you with the option of either importing the data into an Excel Table, a PivotTable Report/PivotChart, or just creating the connection between SQL and Excel. Let's leave the option marked as Table and click on OK. The data will then import into the spreadsheet:
   

The location of the .odc file

We'll find the location of the .odc file we created earlier, with the spreadsheet still open. Follow these steps:

1. Open the Connection Properties window by going to Data | Connections | Properties…
2. Click on the Definition tab, and the Connection File path will be displayed. For me, our file and path is C:\Users\Belinda Allen\Documents\My Data Sources\cherry TWO Vendors.odc. As you can see, the actual .odc name is a combination of your server, your database, and the table/view you used for the connection:
   

Reusing an .odc fie

One of the great features of using an .odc file is that once it is created, it can be used over and over again. Let's reuse the connection file we just created and follow these steps:

1. Open a blank Excel workbook. Go to Click Data | Existing Connections and scroll to find the connection you just created. For us, it is cherry TWO Vendors. Click on Open:
   
2. The Import Data window will open, asking how you want to view your data: Table, PivotTable Report/PivotChart, or if you just want to create a connection.

   Tip

   Note that you can also simply double-click on the file in Windows Explorer, which will open Excel and take you directly to the Import Data window.

3. You'll then be prompted for your SQL login, which is the same as your GP login.