The Thomson system for processing UPS files uses pipe delimited flat files and the UPS generated files are comma delimited. Commas were being replaced with pipes manually and we developed a process that does this automatically. The important part of this was changing the matter field format from the user input to a standard.
The matter field was coming in using the following formats: 99999-01, 99999/01, or 99999.01. They needed to be in the format xxxxx.xxxxx. They would come in all numeric, or alpha-numeric. They were separated by either a (-), (/), or a (.) or a white space.
As an example a Matter Field would come in looking like this:
or or the output needed to be all numeric looking like this (five numbers followed by a period and five more numbers and enclosed in pipes.)
THE LOGIC
If anything came in with something besides a period as a separator we replaced it with a period. If the segment on the left came in with anything less than five numbers we padded it with zeros i.e. 01 -> 00001, anything more than five numbers we trimmed it to five. If the second segment (right of the separator) came in with anything other than numbers i.e. 12345.02bigman, we trimmed the letters and padded the number with 0’s ie 12345.00002.
We also had to deal with fields being populated with commas in the text ie. ,”America Town, USA”,. We set up our BizTalk Program to recognize the the quotes and output |”America Town, USA”|, essentially ignoring any problems with commas in text.