Converting tab delimited files into EDI

A few days ago I received a email saying "I am a DataDirect XQuery user, and I'm successfully using your product to create XML-based reports from incoming EDI messages. Now I'm in need to do something somewhat different: it may happen that my company receives tab-delimited messages, and those messages need to be translated to EDI X12 831, rather than converted to XML. Can your product help me handling tab-delimited to EDI conversions?".

Not being a marketing person, I usually like showing how things are done, rather than just claiming that they can be easily done using our products... :)

So, I asked the user for an example of his data, a tab-delimited file. If you have read some of the other posts in this Blog, you will know that DataDirect XQuery can access tab-delimited files thanks to the XML Converters technology. So, consider this tab-delimited file:

[cc lang="text" theme="xquery"]id description quantity unitprice 001 Telephone 1 23.00 002 Desk 1 129.00 003 Keyboard 2 21.00[/cc]

The following XQuery will return, for example, all the description fields in the tab-delimited input message:

[cc lang="xquery"] { for $item in doc(“converter:TAB:first=yes?file:///c:/sample.tab”)//row return $item/description } [/cc]

The result is:

[cc lang="xquery"] Telephone Desk Keyboard [/cc]

That shows that we can handle a tab-delimited file in DataDirect XQuery and generate XML; but it doesn't help much our user: he needs to output EDI.

But we know how to do that; Marc has shown us in this post how you can create EDI messages from XQuery. We just need to apply the same idea to our example; the following XQuery uses the incoming tab-delimited file to apparently generate an XML fragment consistent with a specific vocabulary; that vocabulary is what the underlying XML Converters technology knows how to translate in the corresponding EDI message (an X12 832 message in this case, which is a price/sales catalog). The serialization option at the beginning of the XQuery is instructing DataDirect XQuery to output a raw EDI message rather than XML; that way we are moving data from a tab-delimited file format into an EDI message manipulating it as if it was all XML, but without ever materializing it as XML! Many of the EDI fields typically specified are omitted here; but this example will run if you want to see how it works.

[cc lang="xquery"]declare option ddtek:serialize “method=EDI,long=yes”;

1515151515 5151515151 ^ 000032123 0 P *

CT 9988776655 1122334455 128 X 004030

832 12345

CP GOV56789 00

{ for $row in doc(‘converter:TAB:first=yes?file:///c:/sample.tab’)/table/row return

MF {$row/id/text()} {$row/description/text()} {$row/quantity/text()}

{$row/unitprice/text()}

}

[/cc]

Curious about the result? Here you go:

[cc lang="text" theme="xquery"]ISA+00+ +00+ +ZZ+1515151515 +ZZ+5151515151 +080129+1710+U+00000+000032123+0+P+*’ GS+CT+9988776655+1122334455+20080129+1710+128+X+004030′ ST+832+12345′ BCT+CP+GOV56789++++++++00′ LIN++MF+001++++Telephone++1′ CTP+++23′ LIN++MF+002++++Desk++1′ CTP+++129′ LIN++MF+003++++Keyboard++2′ CTP+++21′ SE+9+12345′ GE+1+128′ IEA+1+000032123′[/cc]

The beauty of this approach is that you can use it in conjunction with all the other data aggregation capabilities of DataDirect XQuery, like possibly adding data to the outgoing EDI message which is not available in the incoming tab-delimited file but available inside a relational database (like a encoded field that may need to be looked up in a table); and this will all work taking advantage of the performance and scalability features of DataDirect XQuery and XML Converters: the example above will seamlessly stream from tab-delimited to EDI without ever allocating in memory for than a few items per time.

The same technique applies of course to all the data sources supported by DataDirect XQuery and XML Converters; you can find more details at the following links:

http://www.xmlconverters.com/standards/

http://www.xquery.com/architecture/relational.html

http://www.xquery.com/architecture/SOAP.html