Data Harvesting Techniques

There are many different techniques for extracting the data from the external system and copying it into the data set tables. The best technique to use depends on the external system and your organization’s expertise with various database tools.

Possible harvesting techniques include:

  • Use SQL statements to transfer the data directly from the source database to the data set tables.
  • Use SQL statements to extract the data into CSV files, then use Application Collaboration’s Load from File command to pull it into the data tables. See Loading a Text File for more information.

  • Use SQL statements to extract the data into comma separated values (CSV) files, then use a database utility to copy the data into the tables. You could use BCP (bulk copy) to do this. BCP requires the SQL client tools be installed on your Application Collaboration machine.

    See Using BCP to Copy Data into the Tables (SQL Server).

  • Develop a program that uses SQL statements to extract the data and copy it into the data set tables.

Note: You are not limited to these techniques; you can use any method that can get the data from the other system and copy it into the Application Collaboration tables.

Loading a Text File

Application Collaboration includes a command for automatically loading a CSV file into a database table. This essentially performs an automatic bulk copy operation. This method is a quick way to load a single table. You cannot set up a batch file using this method, so you cannot schedule it to run automatically.

To import from a text file, the file must have the exact number of columns as the data set table, and they must be arranged in the same order.

The data in the text file can be delimited with commas, tabs, or any other custom, single-character delimiter.

You can select to automatically clear the table before loading the new data. In addition, you can automatically transform the table after loading the data.

Load a CSV File into a Data Set Table

Caution: If you are bringing date or number information into InterAction using Load From File, it must be in United States, English format. If you wish to bring date or number information into InterAction using a different format, you must use DTS as your import method. See the LexisNexis InterAction Support Site for more information.

  1. In the Application Collaboration main view, select the data source from the console list. This displays the related data set table names in the detail pane.

  2. Right-click the data table to populate and choose Load from File.

  3. Choose (Browse), select the file you want to load, and choose Open.

  4. Specify the encoding of the file you are importing with the Import File As drop-down list.

    For example, if you know that the encoding of the file is Korean, select Korean (Hongul). In most cases, Application Collaboration detects the encoding and specifies it for you.

    If no value is specified, it is set based upon the operating system. If the system value is not supported, the value is set to Windows (Western Europe).

  5. Select the First row of file is field headers check box if a header row exists in the import file.

  6. Select the appropriate File Format from the drop-down list.

  7. If you want to purge existing data from the table before loading the data, select the Clear Table Contents check box.

  8. If you want to transform the data set after loading the table, select the Execute Transformation check box.

  9. Choose Start to load the file. You can view the progress on the bottom of the dialog box. The total number of rows loaded displays in the Rows box.

    Tip: You can also view the total number of rows harvested into a table in the console list. The number displays next to the data set name.

Using BCP to Copy Data into the Tables (SQL Server)

BCP does not respect the table properties and lets you insert incorrect data. This generates an error during transformation. Be sure your data conforms to the table properties before populating the data set tables!

BCP is a database utility for copying several rows of data into a database table. You can use BCP to copy data into the Application Collaboration tables. BCP requires the SQL client tools be installed on your Application Collaboration machine.

You run several BCP statements automatically by creating a batch (*.BAT) file. This lets you populate all the data tables by simply running the batch file.

When using BCP, you must provide full syntax to the table that should receive a table. This syntax is:

DATABASE.OWNER.TABLE

Database refers to the physical database created on your server, not the InterAction database. Therefore, assuming your physical database is called CHICAGO and you are populating the companies table, the table name syntax is:

CHICAGO.IDCAPP.INT_DTS_COMPANY$1