Version 24.2.9039

• Overview
  • Video Resources
  • 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 might 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 styled Excel sheets, and for converting these Excel sheets into an EDI document. This guide explains how to quickly get started with EDI flows that use 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. Use the following two How-To guides as references for more complex EDI mapping projects:

• Map EDI to CSV
• Map Database Output to EDI

Overview

This guide covers:

• 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 walk through the process of creating an Arc flow that accomplishes an EDI-Excel translation in both directions.

Video Resources

Watch this short video for an introduction to how you can visualize EDI data in Excel using Arc.

EDI to Excel

The Excel connector comes pre-configured with a template Excel file that generates styled 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 styled 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 on the Input tab of the Excel connector (under the More dropdown)

The 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, keep the Template File as EDI_XML_to_XLSX_styled_template.xlsx file, but follow some additional steps to generate more relevant input files. These steps are 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 (for example, the X12 connector for X12 documents or the EDIFACT connector for EDIFACT documents). In addition to converting EDI to and from XML, these connectors also validate the structure of the EDI document and can optionally validate the EDI interchange headers.

Once the EDI document has been converted to XML, you can upload it on the Input tab of the Excel connector. The Excel connector’s Template File should remain as EDI_XML_to_XLSX_styled_template.xlsx, which converts the sample EDI document into a styled Excel sheet.

See Create an EDI to Excel Flow for details on how to create an Arc flow to generate Excel sheets out of custom EDI data.

Excel to EDI

As mentioned above, the Excel connector includes a template file that converts EDI XML into a styled 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 styled spreadsheet, converted to XML, and then converted to an EDI document.

This can be 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 EDI document.

Since the data should be entered into a styled Excel sheet that matches the Excel connector’s template, the first step is to generate an Excel sheet in 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 (using something like an X12 or EDIFACT connector)
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 the generated Excel sheet with sample data (for example, sample_excel.xlsx)
5. Replace the data in sample_excel.xlsx with 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 (again using something like an X12 or EDIFACT connector)
8. For future EDI documents, re-load the sample_excel.xlsx file and repeat steps 5-7

The following sections detail how to create Arc flows to convert EDI into Excel and vice versa.

Create an EDI to Excel Flow

As mentioned in EDI to Excel, 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, and so on). This guide uses X12 810 (invoice) as the EDI document. Before the X12 connector is a trigger connector (AS2 in the following example), which receives the X12 810 document and passes it directly to the X12 connector. However, your use case might require a different trigger connector, such as SFTP. See Connector Categories for more information on the types of connectors that might fit your use case.

In the Interchange Settings section of the Settings tab of the X12 connector, set Usage Indicator to 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).

On the Advanced tab of the X12 connector, you can use the Generate Description As setting to determine whether descriptions of each EDI element should be generated in the resulting spreadsheet. The default value for this setting is XML Comment, and XML comments are not read by the Excel template. This means that when when this is set to XML Comment, descriptions do not appear in the styled Excel sheet. Change this setting to Element Attribute to have the Excel template parse out the description values and include them in the resulting Excel sheet.

Step 2: Excel Connector

Next, connect the X12 connector to an Excel connector so that the XML files are passed along to the Excel connector:

Set the Excel Template File to EDI_XML_to_XLSX_styled_template.xlsx. This template file is available in the Template File dropdown on the Excel connector’s Settings tab.

Add an instance of a terminal connector (not shown in the previous image) to serve as the final connector in the flow. A common terminal connector for this use case is a File connector which allows you to drop off the finished files to a path on disk. See Connector Categories for more information on the types of connectors that might fit your use case.

Step 3: Testing the EDI to Excel Flow

Now you can send a sample EDI file through the flow. You can generate a sample 810 document by clicking the More dropdown on the X12 connector Input tab.

Tip: You can generate sample 810, 850, 855, and 856 documents in the X12 connector. If you need other document types, you must get them from somewhere else.

After you send the sample document through the flow, the output of the Excel connector should look similar to this:

You can now convert any standard X12 document to an Excel sheet styled like the image above by inputting the document into the start of this flow.

Create an Excel to EDI Flow

As described in Excel to EDI, 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 Create an EDI to Excel Flow. The styled Excel sheet resulting from this flow functions as the sample Excel sheet.

Note: If you need multiple types of EDI documents (for example, an 810 invoice as well as an 850 purchase order), you need to create sample Excel sheet for each document type.

After you generate a sample Excel sheet, you can configure the Excel-to-EDI flow. This flow is separate but similar to the one described above.

Step 1: Excel Connector

First, add an Excel connector to the flow. Before the Excel connector is a trigger connector (File in the following example). Your use case might require a different trigger connector, such as SFTP. See Connector Categories for more information on the types of connectors that might fit your use case.

Set the Excel Template File to EDI_XLSX_to_XML_template.xml. This template file is available in the Template File dropdown on the Excel connector’s Settings tab.

Step 2: EDI Connector

Next, connect the Excel connector to an X12 connector (other EDI flows might use EDIFACT or EANCOM connectors):

Configure the X12 connector Interchange Settings to match the trading partner that will receive the resulting X12 documents. See the X12 documentation for more information. The X12 connector in this example is designed to be connected directly to a terminal connector. AS2 is commonly used, but other MFT connectors can be used as well.

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 EDI documents. You can run a quick test by uploading the sample Excel sheet to the Excel connector.

The Excel connector generates 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) converts this XML into EDI format and applies interchange headers according to the Interchange Settings in the X12 connector.

After you confirm that the flow converts the sample Excel sheet to the desired EDI format, you can replace the specific values in the sample Excel sheet with actual production data, and use the same flow to convert the production data to EDI.

Adjusting the Sample Excel Sheet

Some EDI documents require adding or removing rows from the sample Excel sheet. For example, an EDI purchase order might 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 this situation, insert rows into the Excel sheet if you need more loops, or delete rows from the Excel sheet if you need fewer loops to ensure that the spreadsheet structure matches the EDI document structure.

Since some EDI loops might need to be predictably added or removed, best practice is to start with a sample EDI document that contains more of the possible segments and loop repetitions, because removing rows from the sample Excel sheet is typically easier than adding new rows.