Exporting Date-Time Stamps Using Kourier Integrator

A common requirement when exporting data to a MS SQL Server database is to stamp the exported row (record) with a date-time stamp. DBAs will often use this information when they analyze the data. Another similar requirement is to export an existing date and time fields from a UniData/UniVerse record to a single datetime column in a SQL Server database. This tech tip will show you how to setup your Export Specifications to use date-time stamps and what you need to do to export existing date and time stamps fields that exist in your UniData/UniVerse Database. These techniques will work using either Kourier Integrator or Kourier Integrator Classic.

Adding a Date-Time Stamp to Exported Data

We'll start with the simplest example, adding a date-time stamp to an exported row. Note, this tip was first published in our Summer 2006 newsletter in an "Ask Kore" article. However, it's a very common question and for completeness it's being included here.

In order to automatically stamp each exported record with a date and time to indicate when it was loaded into your data warehouse, all you need to do is create a new line in the Export Specification for Field ID "0" and then enter this in the Conversion field, including quotes:

"{@DATETIME}"

Note: This must be uppercase, and don't forget to create a SQL datetime type column in your SQL database before running the export.

See the example Kourier Integrator for U2 Export Specifications screen below for how you could enter this. Best practice is to add the date-time stamps as the last exported field, but it can be anywhere actually.

That's all there is to it.

Exporting Existing U2 date and Time Stamps

If your data record has an existing date stamp and a time stamp which indicates the moment on which the U2 record was updated or created, you may want to store this in your SQL database as a single datetime value, rather than two separate datetime values (one for the date, one for the time). While Kourier Integrator does not currently have a way to create this kind of data field, it can easily be done using an I-descriptor, or "virtual" attribute". There are a number of ways to do this, depending on how the data is stored in your U2 application. The methods described here can be used in either Kourier Integrator or Kourier Integrator Classic (for DataFlo.)

Example 2

OK, so that's fine for a simple case where the date and time are in separate attributes. How about another common scenario, where there is a single stamp attribute which has the User ID, date and time separated by asterisks or some other character? For example, let's say that Attribute 73 of your data file has this structure, and a sample record has the data "JSEGAL*14641*52437". How would we get this as a datetime stamp? Well, here is the formula we would use:

OCONV(OCONV(EXTRACT(@RECORD,73,0,0),'G1*1'),'D4/'):" ":OCONV(OCONV(EXTRACT(@RECORD,73,0,0),'G2*1'),'MTS')

It's pretty much the same, except that we are using the "G" Group Extract conversion to separate out the 2nd and 3rd parts of the "*" delimited field. Using this dictionary on the record with the sample data above would give us the output "01/31/2008 14:33:57".

Example 3

Now for the really complex example. What about when you have MultiValued line items being exported to a child table in SQL, and you have date and time stamps as part of the line item information? Well, the OCONV function (and many other functions) will not work properly on MultiValued data. Similarly, the ":" operator for concatenation will not work on MultiValued data. Instead, we have to use the SUBR (Subroutine) functions provided in both UniData and Universe for this situation. It does mean that our formula will get longer and less readable. But we can still make it work!

Let's say that we have associated MultiValues in attributes 41 through 79 of your U2 data file, and associated Attributes 52 and 53 have the date and time, respectively. We can still use the EXTRACT function to get all of the MultiValues in each attribute, but we have to use the SUBR("-OCONVS",data,conversion) function and the SUBR("-CATS",value1,value2) function to properly format the data. Also, we need to remember to use the REUSE() function to repeat any non-MultiValued data. So, our formula would look like the following:

SUBR("-CATS",SUBR("-CATS",SUBR("-OCONVS",EXTRACT(@RECORD,52,0,0),"D4-"),REUSE(" ")),SUBR("-OCONVS",EXTRACT(@RECORD,53,0,0),"MTS"))

(Note that this command would be entered all on one line)

Example 4

With Kourier Integrator for U2 successfully implemented on so many different application platforms, we've seen many different kinds of data structures. On one system, there was a date/time stamp in a single attribute, with the date in Value 1 and the time in Value 2. In this case, our formula would look very much like the first example (let's say that the data is in attribute 20):

OCONV(EXTRACT(@RECORD,20,1,0),'D4/'):" ":OCONV(EXTRACT(@RECORD,20,2,0),'MTS')

Of course, with any new I-Descriptor dictionary, you must compile the DICT before using it. In both UniData and UniVerse, you would do this with the following command:

CD filename dictionary_name

This will also tell you if you have made a mistake, like mis-counting the opening and closing parentheses (easy to do!).

With all of these examples, you should be able to tackle almost any date/time structure found in your application. As always, of course, we are more than happy to assist you in building these I-descriptor dictionaries!

We hope you find this tech tip as useful. As always, if you have any question on how to do any of this, please call us.
DataFlo and Preview are products, copyrights and trademarks of Epicor Corporation

Our technical tips are provided to help you find new or easier ways to solve some of your technical challenges or to show you a new technique. We hope you find them useful. Please contact us if you would like to share one of your Tech Tips or need help solving a technical challenge. We would be happy to help you.

View Tech Tip Archive