How to Import an EDI X12 Document Into a Database

In this tutorial, you will learn how to read the contents of an EDI X12 document 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 850 Purchase Order, specifically a Purchase Order Segment PO1. The file name for the purchase order is 850_example.edi and contains elements delimited by an asterisk.

850 Purchase Order

The database table that we will read into is defined with six columns, named after the EDI document elements in the PO1 segment.

850 P01 Segment

Purchase Order Database Table

Creating the Project

In GoAnywhere MFT, click on Projects in the Workflows menu and select Project Explorer. Create a new Project and provide a name and description.

Create Project

Read EDI X12

From the Project Designer, expand the Data Translation folder in the Component Library, and then drag the Read EDI X12 task to the Project Outline. On the Read EDI X12 task, enter the Input File location. From the X12 Transaction Set drop-down, select the 850 Purchase Order - 4010. If you do not have this transaction set installed, you can download it from the Add-On Marketplace.

Read EDI X12 Task

We are going to use the EDI X12 Data Mapper to map the values read from the input file 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 EDI X12 task to open the EDI X12 Data Mapper. When you first launch the EDI X12 Data Mapper, you will see inbound/read data containers on the left (the EDI document in this case) and outbound/RowSet containers on the right.

The EDI X12 Transaction Set container includes all available data elements, segments, and envelopes for the chosen Transaction Set. Each Transaction Set element can be read to a RowSet column or variable.

In this example, we are reading six columns of data from the EDI document. To create a column, select the Transaction Set element then drag and drop it into the RowSet variable container. Double-click on a RowSet container name to give the output RowSet variable a name. In this example, the output RowSet variable is named PurchaseOrder.

Read EDI X12 Data Mapper

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

Read EDI X12 Task

SQL Insert

From the Component Library, drag the SQL task to the Project Outline after the Read EDI X12 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 toolbar menu and selecting the Add a Query option.

In the SQL Statement attribute, enter "INSERT INTO purchase_order (transaction_set_purpose_code, purchase_order_type_code, purchase_order_number, date, contract_number) VALUES (?, ?, ?, ?, ?, ?)”. For each column in your input file you will need to specify a parameter value '?' separated by a comma.

On the Input RowSet Variable attribute, specify the name you used on the Read EDI X12 task (for example, ${PurchaseOrder}). When specifying input variables, surround the variable name with the variable syntax ${[varName]}.

SQL Query Statement

Execute Project

Click on the Read EDI X12 component in the Project Outline, and 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 you intended, you can lower the log level to minimize the number of logs generated.

Click the Execute button. 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, which can then be emailed to tech support if requested.

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

Read EDI X12 Job Log

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