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 TimeXtender Data Quality.
Other uses for this could be to transfer the data automatically to a SQL Server database by using a Data Transfer package within TimeXtender Orchestration.
Create a Data Provider
First step is to create an Excel Data Provider in TimeXtender Orchestration and Data Quality, 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 configures 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
