Continuous Monitoring
      Back to home
      1. Help Center
      2. Data Governance
      3. Continuous Monitoring

      Configure a data quality control to an Excel document

      How to connect to an Excel document to create a data quality control directly

      In this example there is an Excel document on the local filesystem that you want to monitor with a Query in Exmon Data Governance.

      Other uses for this could be to transfer the data automatically to a SQL Server database by using a Data Transfer package within Exmon.

       

      Create a Data Provider

      First step is to create an Excel Data Provider in Exmon Data Governance, select Excel as Datasource Type and in the Connection String add the following, and replace the path to the Excel document:

      Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\temp\ProductList.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES";

      The HDR=YES property tells Exmon that the first row contains column names. If that is not the case, remove that property.

       

      Query the data

      Query the data using a subset of the SQL query language, where the area selected is [SHEETNAME$AREA]

      Examples are:

      • SELECT * FROM [Product$]
      • SELECT * FROM [Product$A1:F1]
      • SELECT Name, SKU, ListPrice FROM [Product$] WHERE ListPrice > 0