Wednesday, March 11, 2015

Transform AIF output to a CSV file

Start out by reading Shashi Sadasivans article series on import of CSV files with AIF: Much of the stuff you need to do for the outbound transformation is the same.

In this example I'm going to show how a Purchase Order Confirmation document can be transformed to a comma (actually semi colon in my example) separated file.

First thing you should do is to create classes to wrap to the XML schemas, so you don't have to work directly with the XML nodes. The class creation is described in Part 3 of Shashi's article series. You'll need classes for the Envelope, the Shared Types and the document in question.
You don't absolutely need to create these classes, but I prefer to work with these classes rather than working directly with the XML nodes.

With the classes in place you can create an instance of the document class (PurchRequistition.AxdPurchaseRequisition). But first you need to isolate the message part of the XML to be able to de-serialize this part of the XML into the document instance.

Here is a method I made for that:

That was the hard part to figure out. I have a gut feeling that you can do this smarter. Please comment if you know how.

I call this method in the beginning of my Transform method, and I then begin to loop over the journal, purchase headers and purchase lines. I use a StringBuilder for the output records.

I have created a method to add each field to the output string, check allowed length, remove unwanted characters and add the field separator. It's called AppendField and that's what I call in the following:

That is it. Remember to close the outgoing stream writer when done:

Here is the AppendField method:

And the GetSafeString method:

You can get the entire class, but not the XSD wrappers, here. You'll have to create the XSD wrappers yourself.

On the outbound port you could use the standard file adapter with this transformation. It will however create the output file with an XML extension. If you need an CSV extension you can't write a a new file adapter extending the standard file adapter. You'll have to override a single method to control the output file name.

Thank you Jacob Broberg for valuable input from your prototype to this solution.