. PHP_EOL

How to Import an EDIFACT Document into a Database

In this tutorial, you will learn how to read the contents of an EDIFACT file and insert that data into a database. The SQL syntax in this example is based on MariaDB, but GoAnywhere can perform the same process with SQL Server, Oracle, MySQL, and other databases.

For this example, we will be reading from an ORDERS Purchase order message – D18B, specifically the Beginning of Message Segment (BGM) and Date/Time/Period Segment (DTM). The file name for the purchase order message is D18B_Orders.txt and the elements within the segments are delineated by the ‘+’ symbol.

EDIFACT Purchase Order

The database table that we will read into is defined with seven columns to match the input file.

Database Column Names

EDIFACT Database Table

Creating the Project

In GoAnywhere MFT, access the Project Explorer by clicking on the Projects item in the Workflows menu. Create a new Project and provide a name and description.

Create Read EDIFACT Project

Read EDIFACT

From the Project Designer, drag the Read EDIFACT task from the Component Library to the Project Outline. On the Read EDIFACT task, enter the Input File location. From the EDIFACT Transaction Set drop-down, select the ORDERS Purchase order message – D18B. If you do not have this Transaction Set installed, you can download it from the Add-On Marketplace.

Read EDIFACT Task

We are going to use the EDIFACT Data Mapping Wizard to take the values read from the input file and map them to an output RowSet variable. We will then write the data contained in the RowSet variable to a database.

To begin, click Launch Data Mapping Wizard on the main panel of the Read EDIFACT task to open the EDIFACT Data Mapper. When you first launch the EDIFACT Data Mapper, you will see an Input EDIFACT Transaction Set and a RowSet container. The EDIFACT Transaction Set container contains all available data elements, segments, and envelopes for the chosen Transaction Set. Each Transaction Set element can be mapped to a RowSet variable column.

In this example, we are reading seven columns of data from the EDIFACT document.

To create a column in a RowSet, select the Transaction Set element and then drag and drop it into the RowSet variable container. Double-click on an output RowSet container to give the output RowSet variable a name.

Read EDIFACT Data Mapper

When finished mapping Transaction Set elements to the RowSet variable container, click Apply. Notice the Output RowSet variable “RowSet” now appears on the main panel of the Read EDIFACT task.

Read EDIFACT Task

SQL Insert

From the Component Library, drag the SQL task to the Project Outline after the Read EDIFACT task.

On the SQL task, specify the Database Server from the drop-down list of Resources. If your database is not defined yet, you can click on the Create button next to the field to define the connection information to your database.

SQL Task

The next step is to add an SQL Statement, which can be done by clicking on the Add button in the SQL toolbar and selecting the Add a Query option in the drop-down list.

In the SQL Statement attribute, enter “INSERT INTO purchase_order (document_name_code, code_list_identification_code, document_name, document_identifier, version_identifier, date_or_time_or_period_function_code_qualifier, date_or_time_or_period_text ) VALUES (?, ?, ?, ?, ?, ?, ? )”. For each column in your input file, you will need to specify a parameter value ‘?’, separated by a

On the Input RowSet Variable attribute, specify the name you used for your RowSet on the Read EDIFACT task. When specifying input variables, use variable syntax around the variable, for example, ${[varName]}.

SQL Statement

Execute Project

Click on the Read EDIFACT component in the Project Outline, then expand the Control panel.

The Log Level field on the Control panel allows you to specify the level of log messages that are generated. While designing and testing a Project, the debug log level provides useful information. Once the Project is running as intended, you can lower the log level to minimize the amount of logs generated.

Press the Execute button in the toolbar of the main panel. The Project will save, validate, and then execute. Once the Project has finished executing, a View Job Log link is displayed. You can review the log or download it as a text file that can then be emailed to tech support if requested.

View the Job Log and you can see an EDIFACT file was read and then the data was inserted into the table.

Project Job Log

Still have questions? Browse our forum, post a question, or live chat with a technician now!