Version 22.0.8473

• Overview
  • EDI to Excel
  • Excel to EDI
• Create an EDI to Excel Flow
  • Step 1: EDI Connector
  • Step 2: Excel Connector
  • Step 3: Testing the EDI to Excel Flow
• Create an Excel to EDI Flow
  • Step 1: Excel Connector
  • Step 2: EDI Connector
  • Step 3: Testing the Excel to EDI Flow
    • Adjusting the Sample Excel Sheet

One of the fastest ways to get started with EDI integration is to visualize EDI documents in Excel spreadsheets. This EDI-to-Excel Flow may be the precursor for a more complex EDI integration, or it might be a long-term solution for handling EDI documents in a familiar environment.

The CData Arc Excel Connector comes with built-in templates for rendering EDI documents as stylized Excel sheets, and for converting these Excel sheets into an EDI document. This guide explains how to quickly get started with EDI Flows that utilize these templates and do not require any custom mappings or custom logic.

Arc also includes powerful mapping tools to convert EDI data into many different formats, and two other How-To guides can be used as reference for more complicated EDI mapping projects:

• Map EDI to CSV
• Map Database Output to EDI

Overview

This guide will discuss both:

• Converting incoming EDI files into a styled Excel spreadsheet
• Generating new EDI documents by inserting data into Excel cells.

This section contains a brief overview of the concepts involved, and the following sections will walk through the process of creating an Arc Flow that accomplishes an EDI-Excel translation in both directions.

EDI to Excel

The Excel Connector comes pre-configured with a template Excel file that generates stylized and formatted Excel sheets out of EDI XML. This template file is what allows EDI documents to be rendered in Excel without any custom configuration or mapping.

The Excel Connector also supports generating test EDI XML files to immediately convert into a stylized Excel sheet. The fastest way to get started visualizing EDI in Excel is to create a test Excel sheet using these two features:

• Set the connector’s Template File to EDI_XML_to_XLSX_styled_template.xlsx
• Use the Create Test Files option in the Input tab of the Excel Connector (via the More dropdown button)

The Excel Connector’s Test Files feature is helpful for getting started quickly, however it is often useful to generate a styled Excel sheet from actual production EDI data rather than the Test Files included with the Connector. To accomplish this, the Template File should remain set to the same EDI_XML_to_XLSX_styled_template.xlsx file, but further steps are required to generate more relevant input files, as described below.

First, sample EDI data needs to be converted to XML, since Arc uses XML as the intermediary format for manipulating and transforming data. Converting a sample EDI document into XML requires an EDI connector that matches the type of EDI document (e.g. the X12 Connector for X12 documents, the EDIFACT Connector for EDIFACT documents, etc). In addition to converting EDI to and from XML, these connectors also validate the structure of the EDI document and optionally validate the EDI interchange headers.

Once the EDI document has been converted to XML, it can be uploaded in the Input tab of the Excel Connector. The Excel Connector’s Template File should still be set to EDI_XML_to_XLSX_styled_template.xlsx, which will convert the sample EDI document into a stylized Excel sheet.

An Arc Flow for generating Excel sheets out of custom EDI data is described in the Create an EDI to Excel Flow section.

Excel to EDI

As mentioned in the previous section, the Excel Connector includes a template file that converts EDI XML into a stylized Excel spreadsheet. The connector also includes a template file that converts Excel spreadsheets in this style back into XML. Thus, custom EDI data can be entered into the stylized spreadsheet, converted into XML, and then converted into an EDI document.

This can serve as an easy way to convert data into EDI; the relevant data is inserted into the appropriate Excel sheet and Arc uses this to produce the desired EDI document.

Since the data should be entered into a stylized Excel sheet that matches the Excel Connector’s template, the first step is to generate an Excel sheet that has the appropriate format. The full process for generating EDI documents from an Excel sheet is as follows:

1. Begin with a sample EDI document
2. Convert the sample EDI document to XML (via the X12 Connector, EDIFACT Connector, etc)
3. Convert the resulting XML to an Excel sheet using the template file included with the Excel Connector (EDI_XML_to_XLSX_styled_template.xlsx)
4. Save this generated Excel sheet with sample data (e.g. ‘sample_excel.xlsx’)
5. Replace the data in ‘sample_excel.xlsx’ with actual production data
6. Use the Excel Connector’s template file to convert the production spreadsheet back into XML (EDI_XLSX_to_XML_template.xml)
7. Convert the resulting XML into an EDI document (via the X12 Connector, EDIFACT Connector, etc)
8. For future EDI documents, re-load the ‘sample_excel.xlsx’ file and repeat steps 5-7

The following sections will detail the process of creating Arc Flows to convert EDI into Excel and vice versa.

Create an EDI to Excel Flow

As mentioned in the EDI to Excel section, the first step in translating EDI into Excel is to convert the EDI into XML.

Step 1: EDI Connector

Converting an EDI document into XML requires a connector for the specific EDI document type (X12, EDIFACT, EANCOM, etc). For the purposes of this guide, the EDI document will be an X12 810 (Invoice), so the first connector to add to the Flow is an X12 Connector:

The X12 Connector is configured such that Usage Indicator is ‘T-Test Data’, this disables interchange validation and makes the process of generating a sample document easier (the corresponding field in EDIFACT-based connectors is Test Indicator, which should be enabled to replicate this behavior).

In the Advanced Tab of the X12 Connector, the Generate Description As setting can be used to determine whether descriptions of each EDI element should be generated in the resulting Excel spreadsheet. The default value for this setting is ‘XML Comment’, and XML comments are not read by the Excel template (in other words, when this setting is set to ‘XML Comment’, no descriptions will appear in the stylized Excel sheet). Changing this setting to ‘Element Attribute’ will cause the Excel template to parse out the description values and include them in the resulting Excel sheet. So, if the Excel sheet should include EDI element descriptions, set Generate Description As to ‘Element Attribute’.

Step 2: Excel Connector

Next, the X12 Connector should be connected to an Excel Connector so that the XML files are passed along to the Excel Connector:

Note: After connecting the X12 Connector to the Excel Connector in the Flow, ensure that the Flow changes are saved using the blue save icon in the bottom right of the Flows page (separate from the ‘Save Changes’ button for any particular connector).

The XMLToExcel Connector should be configured such that the Template File is EDI_XML_to_XLSX_styled_template.xlsx. This template file should already be available in the Template File dropdown within the Excel Connector’s Settings tab.

Step 3: Testing the EDI to Excel Flow

With this Flow and connector configuration, a sample EDI file can be sent through the Flow. The X12 Connector includes the ability to generate a sample 810 via the More dropdown button in the Input tab; for documents other than 810, 850, 855, and 856, a sample EDI document must be procured from elsewhere.

After sending a sample EDI document through the Flow, the output of the Excel Connector should look similar to this:

Any standard X12 document can now be converted into an Excel sheet stylized like the above by inputting the document into the start of this Flow.

Create an Excel to EDI Flow

As described in the Excel to EDI section, the first step in creating an Excel to EDI Flow is to generate a sample Excel sheet. To generate this sheet, follow the steps in the previous section. The stylized Excel sheet resulting from this Flow will function as the sample Excel sheet.

Note: When multiple types of EDI documents are required (e.g. an 810 invoice as well as an 850 purchase order), then a sample Excel sheet should be created for each different document type.

After generating a sample Excel sheet, the Excel-to-EDI Flow can be configured. This Flow is separate but similar to the one described in the previous section.

Step 1: Excel Connector

First, an Excel Connector should be added to the Flow:

This Excel Connector should be configured such that the Template File is EDI_XLSX_to_XML_template.xml. This template file should already be available in the Template File dropdown within the Excel Connector’s Settings tab.

Step 2: EDI Connector

Next, the Excel Connector should be connected to an X12 Connector (other EDI Flows may use an EDIFACT Connector, EANCOM Connector, etc):

Note: After connecting the Excel Connector to the X12 Connector in the Flow, ensure that the Flow changes are saved using the blue save icon in the bottom right of the Flows page (separate from the ‘Save Changes’ button for any particular connector).

The X12 Connector should be configured such that the Interchange Settings match the trading partner that will receive the resulting X12 documents. Please see the dedicated X12 documentation for more information on X12 Connector configuration.

Step 3: Testing the Excel to EDI Flow

Once this Flow is configured, Excel documents that match the format of the sample Excel sheet can be passed through the Flow to generate an EDI document. You can run a quick test by passing the sample Excel sheet itself through the Flow (by uploading it to the ‘ExcelToXML’ connector).

The Excel Connector will generate XML representing the EDI document, like this:

<TransactionSet>
<TX-00401-810 type="TransactionSet">
<Meta>
<ST01>810</ST01>
<ST02>0001</ST02></Meta>
<BIG type="Segment">
<BIG01>20150708</BIG01>
<BIG02>3003014445</BIG02>
<BIG03></BIG03>

The X12 Connector (or other EDI Connector) will convert this XML into EDI format and apply Interchange headers according to the Interchange Settings section in the connector configuration.

After confirming that the Flow converts the sample Excel sheet to the desired EDI format, the specific values within this sample Excel sheet can be replaced with actual production data, and then that production data can be converted into EDI via the same Flow.

Adjusting the Sample Excel Sheet

Some EDI documents will require adding or removing rows from the sample Excel sheet. For example, an EDI Purchase Order may have varying numbers of line items (PO1 Loops) that do not always match the number of line items (PO1 Loops) in the sample Excel sheet.

In these cases, make sure to insert new rows into the Excel sheet (if more Loops are required) or delete rows from the Excel sheet (if fewer Loops are required) to ensure that the structure of the spreadsheet matches the structure of the desired EDI document.

Given that some EDI Loops may need to be predictably added or removed, it is recommended to start with a sample EDI document that contains more (rather than fewer) of the possible segments and Loop repetitions, since removing extra rows from the sample Excel sheet is typically easier than adding new rows.