Import Bank Statement - CSV Format (AX 2012) Advance Bank Reconciliation Statement

Hello,
Today I will quickly show you how to import a Bank Statement (CSV format) in AX 2012. I am not at all good in writing so, will keep the post short and sweet.

How is it going to work?

We will upload a csv file have three fields date, amount and description to an inbound service. This service will convert the csv to xml file using manage transforms and then store back the data to AX.

4/06/2014,1921.46,test desc1

4/06/2014,-1238.49,test desc2

4/06/2014,-1124.2,test desc3

4/06/2014,-1077.46,test desc4

Environment:
1. Dynamic AX 2012 R2
2. Visual Studio

Check for Bankstatement Service and its Namespace

To start with please check if you have Bankstmt service available inside your AX. To do that goto AOT/Forms/AifService. Open this form and you will see list of services scroll down to BankStmtService and check for namespace. 

If you find your namespace to be blank which was in my case then, you will have to manually add a namespace before we can proceed. 

To add a namespace go back to AOT/Services scroll down to find BankStmtService. You can enter the namespace "http://schemas.microsoft.com/dynamics/2008/01/services" inside the properties of the BankStmt service. You will also have to refresh you service to make sure namespace is exposed.

Go back to AOT/Forms/AifService ->BankStmtService click on refresh at the top. This will take a while in some cases but you will find your namespace there now.

Create Transformation Library

In Order to create a transformation library, we need to implement an interface found in Microsoft.Dynamics.IntegrationFramework.This DLL can be found in the directory: C:\Program Files\Microsoft Dynamics AX\60\Client\Bin\ 



To start with create a Class Library type Visual studio project. Add a reference to the Microsoft.Dynamics.IntegrationFramework.Dll using the link above, and create a new class which implements the interface ITransform found in Microsoft.Dynamics.IntegrationFramework.Transform.ITransform

Then create a method which implements the method Transform. This method takes in 3 parameters
1. input (System.IO.Stream) : This is the input stream which is be the file itself
2. output (System.IO.Stream marked as out): This is the output stream that will be returned back to AX. We need to populate this stream
3. configuration (string): This is a custom configuration string that can be added to the port and can be used by the transformation. in this case we will not be using it.

So now we need to add code to read the data from the CSV file, which will be sent in the input stream as a parameter.
The CSV file we are going to add has 3 fields - Date, Amount, Description

The code should look like this, Its just reading a file and splitting the line contents.

We now need to compile the project and load the resulting DLL into AX.


More information about the Transformation can be found at: Walkthrough: Creating a .NET Assembly Transform [AX 2012]

Creating an Inbound port and loading the Transformation library

Now we will create a inbound port which will consume this transformation library dll. Go to AX client, then system administrator/setup/Services and Application Integration Framework/Inbound ports.

Click on New, give  the port a  name and description. Select Adapter has File System adapter, point it to a folder under URI. Under processing options, tick on transform all request and click on Inbound transforms.

Now click on manage transforms, click new give it a name and description, select type as .net assembly then hit Load to upload your Dll which you have created above. Once this is done, hit close and on the inbound transform form click new and select the newly created manage transform under Transform name. Hit close and on the inbound port under troubleshooting fast tab select logging mode as All document versions and tick Include exceptions in fault. That's it we are done, activate the port.

Configuring Advance Bank Reconciliation Statement

Now, we need to configure Bank accounts to accept external statement. Go to Cash & bank management in your ax client. Under common, open bank accounts. Select the account which you want to configure for import and click edit. Under Reconciliation fast tab, tick Advanced bank Reconciliation. Under statement format, right click and select view details. This will open up Bank statement type form. Click on new and give statement format a name, select the inbound port which you have created above under inbound port then select the checkbox for xml file and mention the file type as csv.

Testing

That's it we are done now, time to test our hard work. Go back to Cash and Bank Management in ax client, select Bank statements and then click on Import Statement. Select Bank Account which you had marked as advance bank reconciliation above, under statement format select csv as statement format. Browse File folder field to the folder location of your file. Under select file, browse through the file that you want to import and click OK. You will see a info box confirming your import.  

Good luck :)

 

Whenever you update your class you will have to redeploy the dll to ax. Please click the link for more information.

Importing Updated .NET Assembly Transforms [AX 2012]

Sources

shashidotnet.wordpress.com