Objective

First, the objective should be clearly defined. As a functional goal, there is the requirement to access the data generated by a storage system in real time and to store it in another storage location. This should be used for backup and statistical purposes: For example to extend the system with a visualization component at a later date.

The goal in this case is the automated conversion of CSV files into a DB2 database with error notification. The source CSV file represents a message, regularly created by a storage system, which represents the inflow and outflow of pallets. This message is a CSV file.

Requirements

By defining the goal, a concrete task has now arisen: Conversion of the source file into the target format. In order to realize this task, a suitable technical solution for converting the data is required. Often the first thought is: "programming". Basically this thought is not wrong either. However, there has been standard software for converting data between standard formats for decades - namely EDI or EAI tools. Standard software of this type manages these tasks "out-of-the-box" with all the features that may be required, and this even without programming knowledge, because these are often required in companies for more complex programming work. For further consideration of this example, we now assume that a suitable standard software such as i‑effect^® is available.

Source format

The first step is now the formal description of the source structure (for a CSV file this description is relatively simple compared to other formats). The standard software needs this description to read or create files of this type. To create this description you have to look at the intersection of all existing files (with CSV this is probably not necessary - in this case one file can be enough, since it already contains all elements). However, in this application case, when looking at several files, you will notice that there are also files where only one line per file is filled. So there may be 1-N lines in the file.

id;warehouse;pallet number;x-coordinate;y-coordinate;z-coordinate;action;date;time 1;L1;4711;1;2;4;outsourcing;31.10.2019;14:14 2;L2;4800;3;4;1;storage;31.10.2019;14:21 3;L3;4801;6;1;1;storage;31.10.2019;14:25

After analyzing the file, the description for this CSV file type can now be created. The format of such a description varies from software to software. However, the essential information is the same (see also CSV article in the glossary).

• Fields and field types (and the sequence)
• Separator
• Line break

    CRLF
    ;
    "
    
        
        
        
        
        
        
        
        
        
    

In the EDI mapping, the description created then looks as follows:

Target format

After looking at the source format, it is now necessary to analyze the target format. In this example, this is very simple, since the structure is to be transferred almost 1-to-1 to the database.

The DB2 file is defined as follows:

CREATE TABLE X006.WHOUSEMOV (
MID BIGINT,
WHOUSE VARCHAR(30),
PALLETID BIGINT,
XCOOR SMALLINT,
YCOOR SMALLINT,
ZCOOR SMALLINT,
DIR VARCHAR(1),
MDATE DATE,
MTIME TIME,
ARCFILE VARCHAR(160),
CONVDTM TIMESTAMP
);

Conversion (Mapping)

In data mapping, the data are now assigned to each other, the mapping is the "route planner" for the data. The mapping for this task would look like this using a graphical mapping editor like i‑effect^® *MAPGUI:

The result of the following mapping therefore also looks as expected in the database:

The result of the conversion can be traced in the EDI software.