Integrating Excel Files into SEI and SQL for Global Reporting via ODBC

Introduction

Many clients prefer not to manage all their data in SQL tables and instead choose to store part of their data in Excel files. However, these Excel files often need to be used in global reporting alongside ERP tools like SEI (Sage Enterprise Intelligence) or directly within SQL databases. This raises the question of how to integrate these Excel files and mix them with ERP data.

In this article, we will explore two technical solutions to address this need for data integration. Both solutions rely on ODBC technology to establish a connection between the Excel file and the receiving system (SEI or SQL).

Technical Prerequisites

Before diving into the two solutions, it is essential to prepare some technical elements to ensure the integration process runs smoothly.

1. Access rights to the directory containing the Excel file:

   • Ensure that the user running the receiving software (SEI or SQL) has sufficient access rights to the directory where the Excel file is stored.
   • Using a service account is recommended to associate this user with each of the services running the software.

2. Creating the ODBC connection string:

   • The ODBC connection string is necessary to establish communication between the Excel file and SEI or SQL. This string can be generated using the ODBC menu in Windows by creating an ODBC data source for Excel.

3. Excel file formatting:

   • Excel sheet names must follow specific rules: avoid spaces and special characters.
   • The Excel file should also have well-formatted columns without conditional formatting or coloring, to facilitate data import.

Solution 1: Using SEI to Integrate Excel via ODBC

SEI allows for the direct integration of Excel files as external data sources, utilizing ODBC technology. Here are the steps to implement this solution:

1. Generate the ODBC connection string:

   • The first step is to place the Excel file in the desired directory on the server.
   • Then, log into Windows using the service account employed by the receiving software (SEI in this case) to configure the ODBC string. This configuration is done through the ODBC Data Source Administrator in Windows, where you will select the Microsoft Excel Driver (as shown below).

2. Integrating into SEI:

   • Once the connection string is generated by Windows, go to SEI and add a new data source. Select ODBC_Excel as the type of data source.
   • Paste the generated connection string into the appropriate field.

3. Creating the process in SEI:

   • Create an SEI process that uses this new Excel data source. Each worksheet in your Excel file will be treated as a separate table.
   • You can then create global reports and dashboards based on the Excel data directly within SEI.

Solution 2: Linking SQL with Excel via ODBC

The second solution allows you to use an Excel file directly within a SQL database through ODBC, without having to manipulate the connection string manually.

1. Configure the ODBC Provider:

   • Use the Microsoft Office 12.0 Access Database provider to establish the connection between SQL and Excel.
   • Although the provider name references "Access Database", it is fully compatible with Excel files.

2. Adding the Excel file as a linked server in SQL:

   • Once the provider is configured, the Excel file is treated as a remote server connected to SQL. This allows you to query the Excel data using standard SQL queries, treating it as a remote server.
   • Access the Linked Servers section in SQL Server Management Studio and configure the connection with the Excel file. You can then run SQL queries to retrieve and manipulate the Excel data.

Conclusion

Integrating Excel files into reporting systems like SEI or SQL via ODBC is a flexible and powerful solution. Whether you use SEI or link the files to SQL, ODBC allows for seamless connection of Excel files to your reporting processes without needing to convert them into SQL tables. It is important to follow the technical prerequisites to ensure smooth integration.