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

Sending
User Review
0 (0 votes)

 

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.

Additionally, in terms of formulas, Excel is rich in possibilities. It allows you to use lookup functions (VLOOKUP, HLOOKUP) or even database functions (DGET, DAVERAGE, DMAX, etc.), but even all that together does not mean Excel is a database.

If Excel Is Not a Database, What Options Should I Consider?

It is not only that Excel is not a database engine; it is that the Excel user is not a database designer and will soon have problems with performance and scalability.

There are a couple of tell-tale signs that you are using Excel as a database and you shouldn’t:

  • Your spreadsheet is slow: it takes a long time to load, and editing it takes even longer
  • You have formulas in your spreadsheet that you are not sure how they work or even why they are there
  • Information is repeated in multiple places. For example, the company name is repeatedly written for each contact in that same company. This means there are multiple places where a typo or update error can creep in. Small mistakes can cause you lots of trouble.

If any of these signs sound familiar, please check if you have a “database in Excel” in front of you. If you do, the best alternative to consider is using database software instead of a spreadsheet. We will see some possible approaches for achieving this change.

In this article, we will show you some possible integration scenarios in a FAQ-like organization. We will cover:

Please note that we will mention SQL Server, the database engine, but we will also mention SQL, the language in which you write your database queries (SQL = Structured Query Language). Besides, for the rest of this article, we will consider relational databases, but you could also consider NoSQL databases if you prefer. We will specifically focus on Microsoft SQL Server, but you can get a similar result with MySQL, MariaDB, or other databases.

 


Can I Import an Excel file into SQL Server?

You can certainly get the data you currently have in one or more Excel spreadsheets into Microsoft SQL Server.

If you want to do it, the first thing you should ask yourself is whether the import is a one-off thing or if you will need to repeat this import procedure from Excel to SQL.

How to Import Data from Excel to SQL Server – Simple Procedure

If you are going to import Excel into SQL Server only once, here are the step by step instructions for it:

  1. Open Microsoft SQL Server Management Studio (SSMS)
  2. Connect to your server
  3. In the Object Explorer, locate the database you want to import into. If you do not have a database yet, create one by right-clicking Databases and then choosing New Database…
  4. Right-click the database name and choose Tasks > Import data… This will open the SQL Server Import and Export Wizard
  5. Choose Excel as the Data source and make sure you have the correct Excel version selected too (if your version is not shown on the list, choose the closest one available)
  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

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.

 

By Ana Neto

Software engineer since 1997, she is now a technical advisor for Connecting Software.

Connecting Software is a producer of integration and synchronization software solutions since 2004. We operate globally and we are also a proud “Top Member” and “Top Blogger” at CRMSoftwareBlog.

 

 

The post Yes, You Can Connect Excel to SQL Server, Dynamics 365, and More appeared first on CRM Software Blog | Dynamics 365.