Demo 4

11 videos
Back to XTranslator page

#1. Setup CSV fields

  • We are working on CSV to EDI translation
  • Our CSV file does not have field names in the first row
  • We add Header segment to the map. It represents our Header row in CSV
  • EndTag property is set to carriage return and line feed using #13#10
  • Add few fields manually and set properties. These fields will be our model fields
  • We use Copy and Paste to add lots of fields with the same properties
  • It is fastest way to setup many fields with the same properties
  • TrimSpacesToNull ensures that extra spaces get trimmed from CSV fields and do not transfer to the output side
  • Lots of other functions available via ExternalFunction property
  • Similar steps apply when CSV is translation input or output

#2. Working with multi-level CSV

  • Our CSV has multiple levels: Header and Detail
  • We add Detail segment and fields for CSV Detail lines

#3. Test CSV layout

  • We setup CSV input side. It is time to test it
  • Make sure that Data tab results match expected values for selected field
  • If they do not match then adjust input fields. Make sure you have them all setup with separator in EndTag

#4. Setup EDI output side using template

  • In this example our EDI file is EDI X12 837 release 5010 but same steps apply to all EDI message types
  • We setup EDI message on the output side using template
  • Template sets default DataPath property for the input file
  • This is good enough for basic execution of the map
  • But "Runtime Properties" are much more flexible way of setting up paths
  • We set output path and check EDI segments
  • One important property for EDI segments is StartTag. It is what EDI producer uses to write segments into the output file

#5. Modify imported EDI template

  • EDI template may not match exact layout of EDI segments you have. EDI implementations have variations
  • Copy and Paste segments or whole groups of segments to match layout you have
  • This tends to be most complex of all mapping tasks
  • Rename segments for convenience
  • Set constant values on specific elements using =Value(_your_constant_) function

#6. Map input to output

  • We map input to output
  • Run map to test results

#7. Setup ISA and GS segments

  • We check and adjust ISA and GS segments
  • ISA and GS segments set to Mandatory=true because they do not have any elements mapped
  • ST segment set to Mandatory=false because it has nested elements mapped
  • ISA segment has number of fields that are fixed length
  • There are also number of fields with dynamic values: current date, time, control numbers
  • All of them have to be set exactly based on requirements. EDI validators are very strict on EDI envelop segments

#8. Setup control numbers

  • Control numbers should be unique for every outgoing EDI file
  • Translator stores control numbers inside file set via IniFile property
  • In this example we name it controlnumbers.ini
  • We also setup unique names for each generated control number attached to specific elements using Sequence property
  • If segment is not mapped it has to be set to Mandatory=true
  • If you want to reset control numbers to 1 simply delete file controlnumbers.ini

#9. Segment counting functions

  • We need to count segments produced in the transactions and output segment count in one of the elements
  • SpecialFunction property has number of functions to count segments
  • For EDI X12 maps use ProductSECount or ProduceMultipleSECount
  • For EDIFACT maps use ProduceUNTCount or ProduceMultipleUNTCount
  • We have to set both output side segment and element to Mandatory=true in order to execute function and get segment count
  • If segment has elements mapped then leave Mandatory=false

#10. Other counting functions

  • We need to set counts on HL segments
  • HL segments need to be counted. They also form hierarchy where child HL element #2 points back to parent HL
  • ExternalFunction is used for producing counts
  • Parent HL only has element #1 set to xcount:Extra.CountUtil:GetIncrementCount. This simply counts HL.
  • All child HL have 3 functions set for counting:
  • Element #1 has xcount:Extra.CountUtil:GetIncrementCount
  • Element #2 has xcount:Extra.CountUtil:GetDecrementCount
  • Element #3 has xcount:Extra.CountUtil:IncrementCount
  • All Parameters properties set to HL_COUNT
  • We have to set elements to Mandatory=true in order to execute function and get segment count
  • If element is mapped then leave Mandatory=false
  • Follow your specific requirements and set counts on HL based on them
  • Use ExternalFunctions to count other segments. For example LX.

#11. Showcase of complete CSV to 837 map

  • We revisit important input and output properties
  • Output separators used for whole EDI file
  • Segment separator is set to ~#13#10. That is tilde, carriage return and line feed
  • If you just want tilde as segment separator then simply remove #13#10 from SegmentSep property
  • We have also added number of extra segments based on our specific requirements

Bonus. Pro tips

  • Design your CSV layout first. When you have all the fields it is faster to map then all at once rather than adding fields gradually
  • Most EDI formats only have 2 or 3 major loops. Something that can be called Header, Detail and Subdetail
  • On simpler EDI formats even single repeating line CSV might work as input
  • 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