Yes, You Can Connect Excel to SQL Server, Dynamics 365, and More

Microsoft Excel has been around for a long time, and users love it so much that most companies have important data in Excel spreadsheets. This ubiquitous nature of Excel means that there are many cases in which you need to connect to Excel.

Have you ever been asked to import from, export to, or somehow integrate with Excel?

To be totally honest, not all users love Excel. If you are not comfortable with Excel, it can be daunting to find your way around huge spreadsheets with never-ending formulas. You will not love it then.

You will also not love it if your company or some of your colleagues use Excel as a database. Some “excel database” examples include the classical database of customers in Excel and an inventory database in Excel. The database of customers is probably the most prevalent. It is very easy to get it started, with a column for the company name and the contact name. Then you add the e-mail and the telephone and from then on it keeps growing.

Using Excel as a Database

Let’s say you have this database of customers in Excel. How far can Excel take you?

You can use Home > Format as Table, which will give you clearer formatting and add a filter to your data.

Once you use Home > Format as Table or, alternatively, Insert > Table, you can name the created table, but that doesn’t mean you now have a database table (let alone a full database)!

The Data menu gives you some database-like possibilities, such as establishing Relationships between tables.

1. Choose SQL Native Client as Destination
2. Indicate the name of the server as Server (localhost or . if it is on the same computer)
3. For the Authentication, use the same method you used on step 2 to connect to the server
4. Choose your SQL database in Database (if no database is listed, hit the Refresh button on the right)
5. You can now choose between Copy data from one or more tables or views or Write a query to specify the data to transfer. Unless you don’t want to transfer all the data you have, the option you should select is the first one. The second one allows you to select what data you want. Using it, you can import data from Excel to SQL Server using a query. Assuming the first option was selected, let’s proceed.
6. On the Select Source Tables and Views, you can choose one or more tables based on the spreadsheet name and the name of the table itself
7. Use the Edit Mappings button below to confirm that each column is being mapped as you expect
8. On the Save and Run Package screen, keep the Run immediately option checked and click on Finish.

If you need to import data from Excel to SQL server automatically, repeating it multiple times, then check the next section.

How do I Connect Microsoft Excel to SQL Server?

In this section, we will assume that you want to connect Excel to SQL server in a more permanent and automatic manner.

Use this type of automatic connection if:

• New data that is entered in the Excel file needs to go into the new database without user intervention
• Data can be changed in the Excel file, and those changes need to be reflected on the database automatically
• Data that is changed in the database needs to automatically go to the Excel spreadsheet, for reporting purposes or others

The best way to achieve this is to have an Excel ODBC connection. Once you set it up, everything is automatic, as described above.

It is important to note that in this section, we use a commercial product called Connect Bridge. Connect Bridge is an integration platform developed by Connecting Software that allows you to connect any software through ODBC drivers, JDBC drivers, or Web Services. We will see that you can use it to connect not only to Excel but also to Dynamics 365 and other software in the following sections. There is a free trial available, so you can try all this out and see if it helps in your specific scenario.

Here is the step-by-step procedure for integrating Excel data:

1. Request a free trial and install Connect Bridge
2. Run Connect Bridge Management Studio
3. Add an account for Excel (Accounts > Create account). Select the CB Excel Connector and then use the Authentication Wizard to complete the account creation.
4. Click on Query in the menu and then go to the Connection Browser. Find the CB Excel Connector and open it until you see the Default Connection. Right-click on it and choose Get Connection string. Then copy the ODBC connection string. You will need it further along.
5. Use the Query option to test out a query that will access what you need in Excel. First, you will need to get the id of the root folder. For that, open the Table section, right-click on the RootFolder table and choose the Generate Select option. Once you have that id, you can go into the FolderItems table and generate a select there, using as items_id the id of the root folder. You should then adjust your query so that you get the information you want.
6. Create a Microsoft SQL Server Linked Server that connects to Excel using the connection string from step 4 and inserting the actual password used for Connect Bridge. The video tutorial at https://youtu.be/rkx8vUITJ_U can help you with the step-by-step procedure.

Please note that although it now looks like we are using the Excel spreadsheet as a database and doing an Excel SQL query, all this is just an abstraction. It looks like a database, you operate it as if it were a database, but Connect Bridge is in fact doing a lot of background work for you and translating back and forth from SQL to API calls.

The CB Excel Connector allows you to insert, update, delete and select data. You can also use some functions and charts provided by Excel.

Supported operations:

• List worksheets that are part of the workbook
• Add a new worksheet
• Get a new worksheet
• Get a worksheet based on the name
• Delete a worksheet
• List charts that are part of the worksheet
• Get chart image
• Add a chart
• Update a chart
• Update chart source data
• Get a list of tables
• Create a table
• Update a table
• Get a list of table rows
• Get a list of table columns
• Add a table row
• Add a table column
• Delete table row

Can I Link Excel to Dynamics 365?

Now that you know how to connect Excel to SQL Server, you will be glad to discover that you can use the same logic to connect to a wide array of business software.

For example, let’s say you want to connect Excel to Dynamics 365. You can connect Excel to SQL Server in the way shown in the previous section and then also connect Dynamics 365 to SQL Server in the same way. The data can then flow bidirectionally from Excel to Dynamics or from Dynamics to Excel.

For this, you need to use a connector for each business software you would like to connect, and all those will work together using as the base the integration platform (Connect Bridge). The best part is that this type of integration is guaranteed to keep working regardless of Excel or Dynamics upgrades. When an upgrade occurs, you either have to do nothing, or you just install the next version of that connector. Free of charge and that simple.

How do I Connect Excel to a SharePoint List?

Connecting Excel to SharePoint used to be quite popular, although you had to choose if you wanted to get data from Excel to SharePoint or from SharePoint to Excel as it only worked one way. Still, this was a native feature back in Excel 2003 and then a possibility with Microsoft’s Excel 2007 SharePoint List Synchronizing Add-In.

These options are now deprecated, but the good news is that if you want to connect Excel to SharePoint, you can again use Connect Bridge. You will need the CB Excel connector and the CB SharePoint connector.

Using this approach, you can be bidirectional as long as you can define the rules for what should happen in each possible conflict case.

How do I Connect Excel to SQL Server Remotely?

Connecting Excel to SQL Server using the Connect Bridge approach described in the previous topics makes it possible for you to do it remotely.

The only limitation is that part of Connect Bridge, namely the Connect Bridge ODBC driver, must be installed on the same machine where the SQL server is. If you can ensure that, doing a remote connection will not be a problem.

The Takeaway

If your company is looking for a way to really connect Excel and SQL to harness their full power, consider this: a simple export is a good way for you to start, but it will probably not be enough in the end.

Consider a long-term alternative like the Connect Bridge integration platform and integrate Excel and SQL in a flexible and automatic way.

Are you curious to see all this in action? We’re happy to arrange a quick web meeting to show you around or simply talk over e-mail or phone.

Links and References

Connect Bridge integration platform

Available Connectors for the integration platform

Customer case study
Getting data into Microsoft Dynamics 365 automatically

Download SQL Server Management Studio