HOW TO Configure Generic Well Test Import

Introduction

XSPOC can be configured to import well tests from an external data source on a regular basis.  However, in order to do so, you must be able to query the data source using SQL.  Typically, the external database can be queried directly, but there are cases where a customer may choose to export the data to a file first.  The well test import copies data from the external data source to XSPOC’s tblWellTests table.  Upon completion of the import for each well, tblWellDetails is updated with the latest values for LastTestDate, GrossRate, and WaterCut.

Procedure

Start with the article https://thetaportal.com/knowledge-base/how-to-configure-data-integration-start-here/

Enable the well test import by setting the system parameter, “DI_WellTestImport”, to “1”.  To disable the import, set the parameter to “0”.

1. Specify the connection string to the well test source by setting the system parameter, “DI_WTConnectString”.  The API used to connect is OLE DB.  In most cases, the OLE DB Clientapplication can be used to construct the connection string for you.
2. Specify the SQL statement used to retreive the data by setting the system parameter, “DI_WTSQL”.  The query must map fields by aliasing them with column names from tblWellTests. For example, “SELECT PROD_DATE as TestDate”. The statement must also contain a filter on @DI_WTIDField. For example, “WHERE WellID=@DI_WTIDField”. For performance reasons, it is recommended that you limit the data to the three (3) most recent days.  The OLE DB Clientapplication can be used to test your SQL statement.    ex. SELECT docDate As TestDate, HoursOn As Duration, Oil As OilRate, Water As WaterRate, Gas As GasRate, 1 As Approved FROM docWellTests WHERE WellSiteID=@DI_WTIDField AND docDate=(SELECT MAX(docDate) FROM docWellTests WHERE WellSiteID=@DI_WTIDField)
3. If necessary, specify the field in tbNodeMaster that relates nodes to their external records by setting the system parameter, “DI_WTIDField”.  The default value is OtherWellID1.
4. If requested by the customer, configure an alternate data source.  In some cases, the primary data source may be unreliable, and the customer will request that data be retrieved from an alternate source if no data is yielded.  To do so, set the system parameter, “DI_WTConnectString2”, to the alternate connection string.  Then, set the system parameter, “DI_WTSQL2”, to the alternate SQL statement.

Example connection strings&Query:

CSV/flatfile Connection string

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=<path>;Extended Properties="text;HDR=No;FMT=Delimited";

CSV/flatfile Query (No spaces in filename, filename can not be too long)

Select F3 as TestDate, F7 as WaterRate, F6 as GasRate, F5 as OilRate, 24 as Duration, 1 as Approved, from <filename> where F2=@DI_WTIDField

Database Connection string

Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;

Database Query

Select <ColumnName> as TestDate, <ColumnName> as WaterRate, <ColumnName> as GasRate, <ColumnName> as OilRate, 24 as Duration, 1 as Approved, from <TableName> where <Well ID ColumnName>=@DI_WTIDField

System Parameters

 CSV Connection String options
“HDR=Yes;” indicates that the first row contains columnnames, not data. “HDR=No;” indicates the opposite.
“Format” = “TabDelimited” OR “Format” = “Delimited(,)” OR “Format” = “CommaDelimited”