Demo 1

13 videos
Back to XTranslator page

#1. Importing template EDI segments and elements

  • In this example our EDI file is EDI X12 837 release 5010 but same steps apply to all EDI message types
  • We import template and check important template properties
  • Setup input file and run translation
  • Since output side is not setup we only examine extracted data in Data tab

#2. Modify imported EDI template

  • EDI template may not match exact layout of EDI segments you have. Real world EDI implementations vary a lot
  • Copy and Paste segments or whole groups of segments to match layout you have
  • This tends to be most complex of all mapping tasks
  • It is also task where EDI skills help. Usually takes many repeated adjustments to get layout right
  • Since output side is not setup we only examine extracted data in Data tab
  • In this example HL segments are nested and Condition/ConditionType is used to distinguish them
  • If warnings show up in the Log tab that indicates mismatch between your input file and the map
  • Adjust map by moving or adding segments. Set Condition/ConditionType on segments
  • Please watch another video on this page about Condition/ConditionType property and warnings in Log tab

#3. Showcase how input EDI matches map

  • If EDI file contains carriage return and line end characters it is easy to open in any text editor
  • We open EDI file and show how it matches our map input side
  • Segments match directly
  • Segments with qualifiers match via segment and Condition/ConditionType
  • Other videos on this page explain how to setup segments with Condition/ConditionType

#4. Importing CSV field names from sample file

  • Our CSV file has field names in the first row
  • We import it with "By-Example" Wizard and add more fields using Copy and Paste
  • Same steps apply when CSV is translation input or output

#5. Manual setup of CSV field names and working with multi-level CSV

  • Our CSV has multiple levels: Header, Detail, Subdetail, etc.
  • We import basic CSV with "By-Example" Wizard just to get started and add more fields using Copy and Paste
  • We add both Header and Subdetail CSV lines
  • Header has to always have field names as first line. We set it via Format property =Value() function
  • We also set Mandatory=true on all fields that have to be produced in the output and will not be mapped
  • Same steps apply when CSV is translation input or output

#6. Basic mapping steps

  • We scroll down to important item on the left input side
  • Select items on the left and right side
  • Map them using right click pop-up menu
  • Run map to see actual live data populated in table format for selected item
  • Select both input and output items to see live data after map runs

#7. Steps for setting up paths and separators

  • One way to set paths is via DataPath properties but in this example we use Runtime Parameters screen to setup input and output paths.
  • Wildcards and macros used to grab all input text files and produce output files with unique names
  • Disable separator auto-detection for this map's EDI input files
  • Our trading partner is sending files with invalid ISA segments and refuses to fix the problem on they end
  • Usually auto-detect works fine. But this EDI input comes with corrupt ISA segment as a result auto-detect does not work.
  • We set separators manually to fixed values. That way translation still works even if incoming EDI file is slightly invalid.

#8. Steps for filtering EDI segments based on element qualifiers

  • We have situation where two NM1 segments can have element #1 qualifiers 41 and 40
  • Each NM1 based on qualifier should be mapped to different output fields.
  • We will use Condition/ConditionType properties to distinguish one NM1 segment from the other based on value in element #1
  • Choose existing NM1. Set its Condition to 41 and ConditionType to FirstNestedItemEqualsTo
  • Copy segment and paste another NM1 just below
  • Set its Condition to 40 and ConditionType to FirstNestedItemEqualsTo
  • Map new segment elements to output. Note: each segment maps to different output fields
  • Important: Condition can have multiple values separated by comma then it will match any one of them. 41,40,42 would match either 41 or 40 or 42
  • Condition can be empty and in that case it will match "any value"

#9. Another example of filtering EDI segments based on element qualifiers

  • We have situation where number of REF segments can have different qualifiers in element #1
  • Each REF holds different data in its elements based on actual qualifier in its element #1
  • We will use Condition/ConditionType properties to distinguish one REF segment from the other based on value in element #1
  • That way we can map them to different output fields
  • In this example we already setup number of REF segments with Conditions. All we need now is to add one more and test it
  • Map new segment elements to output
  • We run map to see actual live data

#10. Steps for adding CSV output field

  • Our CSV file has '|' pipe separated fields.
  • Commas and other separators can be used here as well. But commas might be present in address data. Pipes tend to be safer alternative.
  • Right click on the output side and add element
  • Enter '|' pipe separator into EndTag property
  • Set Mandatory to 'true'. This will ensure that field is always produced in the output even if there is no data for it in the input
  • Map input to new output field
  • Check produced output by running the map
  • Make sure our CSV service line ends with carriage return and line feed entered as codes #13#10 in EndTag property
  • Codes prefixed with # can be used in StartTag, EndTag and LoopEndTag properties. Codes match ASCII table character codes. Example: #9 is tab character

#11. Steps for mapping subelements

  • We map subelements. They are initially mapped same way as elements
  • But once mapping is established it is modified to target specific subelement
  • While this video shows EDI subelement on input side same steps apply for mappings when EDI subelement is on the output side
  • In all cases Mappings screen is used to alter the mapping from element mapping to specific subelement mapping

#12. Steps dealing with warnings

  • We run map and see warnings in the Log tab
  • Translator shows warnings if input file contains segments that are not setup in the map
  • Segment might be setup in the map but have Condition/ConditionType set on it preventing the match with incoming data
  • If you have large input file single error in segment setup may generate multiple warnings. It is clearly the case in this video.
  • Easiest way to find what is failing is to examine Log tab
  • Log usually contains first input segment that does not have a match (segment that is not setup correctly in the map)
  • If map contains few setup errors then log may be harder to dissect
  • Number of segments in the log match number of segments that did not get processed by the map
  • Never run map in production if you have warnings in the Log tab

#13. Showcase of complete 837 to CSV map

  • We revisit important input and output properties
  • First input separators are setup for whole EDI file
  • Auto-detect separators should be 'true' for most maps. Only maps that work with corrupted files need manual separators
  • Then each segment has StartTag property setup
  • Then each segment may also have Condition and ConditionType properties setup
  • We check that HI segment has subelements mapped using Mappings screen
  • After input examination we move on to the output side
  • Each CSV line ends with carriage return and line feed entered as #13#10 in EndTag properties
  • Each CSV field ends with '|'
  • All output fields have Mandatory property set to 'true' to always produce at least empty field and separator in the output.
  • First field called 'Reserved' has constant value setup via Format property. Everytime map runs it produces text 'Header' in that field.
  • We run the map and check translation data

Bonus. Pro tips

  • If you have sample input data: start mapping from input side (left)
  • If you have few input files use smaller one. That way you can run map faster and iterate on changes quicker
  • You can always have map in working (runnable) state when mapping input side first
  • Even if translation data is only displayed in the Data tab. It means that translator is able to read input side and first half of the map is working
  • Avoid using just a comma as your CSV field delimiter. Single commas might be present in address and company name fields
  • Use commas only if you know that data has been sanitized and will not contain commas
  • If commas are present in data | vertical pipe or "," comma+double quotes characters might be better choice as CSV field delimiters