This is the final part to the serieds of Importing CSV files via AIF
Part 1: Consume Web Service
Part 2: Create Item from File adapter
Part 3: Import CSV file with items Through AIF
In the past posts I consumed a webservice InventItemService and created items in the Released products. This was used as a proof of what should happen when the data is sent to Ax. Part 2 looked at doing the same thing using the File system adapter. here we created an XML file, added the item id’s and then put the file in the folder, the batch did its magic and added the items to the Released Products list
A certain drawback to the that was we had to create the XML files manually, or use an external transformer to do it. AX 2012 has a new feature where we can add transformation Libraries in the form of Either XSLT or .Net libraries.
We will be taking the Inbound port created in Part 2 of this series, and add a transformation library, and instead of dropping an XML file into the inbound directory, we will drop a CSV file
A good read about the process I am about to show is: About the AIF Pipeline [AX 2012]
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\
More information about the Transformation can be found at: Walkthrough: Creating a .NET Assembly Transform [AX 2012]
What we need to do is create a Visual studio project, of a Class Library type
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.
At this stage the class should look like this:
public class LoonItemTransform : Microsoft.Dynamics.IntegrationFramework.Transform.ITransform { public void Transform(System.IO.Stream input, System.IO.Stream output, string configuration) { //Need to populate the oputput stream here throw new NotImplementedException(); } }
Okay So we have a transformation Library, 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 2 fields, the itemId and the name.
The Itemd also doubles up as the Product
The code should look like this, Its just reading a file and splitting the line contents.
public class LoonItemTransform : Microsoft.Dynamics.IntegrationFramework.Transform.ITransform { public void Transform(System.IO.Stream input, System.IO.Stream output, string configuration) { StreamReader sreader = new StreamReader(input); string[] dataArray; string lineIn = sreader.ReadLine(); while (lineIn != null) { dataArray = lineIn.Split(','); //0 is itemid, 1 is name lineIn = sreader.ReadLine(); } sreader.Close(); //Create XML for items //Serialize item data to xml string //Create Envelope and add header information, and add item data //serilalize data to outputstream } }
So at this stage we have the data from our CSV file.We now need to somehow create an XML file similar to the one we created in Part 2. For this we will taking help of the XSD files we had take earlier and I will show you how to create a .Net class out of it. We will add these classes into the Library, and populate it. Serializing these classes will give us the XML file desired.
Creating Classes from XSD
What we did in the previous part was create an XML file that conforms to a certain XSD file. There are 3 XSD files in total that we used for creating the XML file. What we well do is create classes using the XSD files, populate them using the CSV file and serialize them.Upon serialization we will get the same output as the previous post.
In our Visual studio project we will first create 2 folders (to separate the objects generated into 2 different namespaces) ItemXSD and SharedXSD
Open the Visual studio command prompt and navigate to the directory where the XSD files were saved.
For the first instance, we need to create classes for the InventItemService. This XSD (item.xsd) is dependant on the SharedTypes.xsd We will specify both these XSD files and pass it to the XSD.exe command.
In the terminal type the following command:
xsd Item.xsd SharedTypes.xsd /classes /namespace:LooneyTrans.ItemXSD
This creates a file Item_SharedTypes.cs. Place this file in the folder ItemXSD of the project
Note: If you are creating the project in VB .Net, you can use the options in XSD.exe to output a vb file instead
Now create the code for the Envelope
xsd Message.xsd /classes /namespace:LooneyTrans.SharedXSD
Place the output file Message.cs in the folder SharedXSD of the project.
After doing so we should be able to access the object AxdEntity_InventTable, just like we did in Part 1 using web services.
We need to extend this one to adding an envelope which sets the Action for the document. The code should now look like this
public class LoonItemTransform : ITransform { public void Transform(System.IO.Stream input, System.IO.Stream output, string configuration) { StreamReader sreader = new StreamReader(input); string[] dataArray; string lineIn = sreader.ReadLine(); //InventTable collection List<ItemXSD.AxdEntity_InventTable> inventTables = new List<ItemXSD.AxdEntity_InventTable>(); //List<ItemXSD.AxdEntity_InventTable> inventTables = new List<ItemXSD.AxdEntity_InventTable>(); /* There seems to be an issue with the code formatting by wordpress here. Replace 'LT' by the less than sign, and 'GT' by greater than sign. The invent table collection should be List'LT'ItemXSD.AxdEntity_InventTable'GT' inventTables = new List'LT'ItemXSD.AxdEntity_InventTable'GT'(); */ while (lineIn != null) { dataArray = lineIn.Split(','); //0 is itemid, 1 is name //Create inventTable for each line ItemXSD.AxdEntity_InventTable inventTable = new ItemXSD.AxdEntity_InventTable(); inventTable.ItemId = dataArray[0]; inventTable.Product = dataArray[0]; inventTable.NameAlias = dataArray[1]; //Insert inventTable to collection inventTables.Add(inventTable); lineIn = sreader.ReadLine(); } sreader.Close(); ItemXSD.AxdItem item = new ItemXSD.AxdItem(); item.InventTable = inventTables.ToArray(); //Serialize item data to xml string StringWriter stringWriterItems = new StringWriter(); System.Xml.Serialization.XmlSerializer serializer = new System.Xml.Serialization.XmlSerializer(typeof(ItemXSD.AxdItem)); serializer.Serialize(stringWriterItems, item); //Put it inside the envelope SharedXSD.EnvelopeType envelope = new SharedXSD.EnvelopeType(); SharedXSD.HeaderType header = new SharedXSD.HeaderType() { MessageId = Guid.NewGuid().ToString("B"), Action = @"http://schemas.microsoft.com/dynamics/2008/01/services/ItemService/create" }; envelope.Header = header; SharedXSD.BodyType bodyType = new SharedXSD.BodyType(); //Load item data xml string into an XML Document XmlDocument xmlDocumentItems = new XmlDocument(); xmlDocumentItems.LoadXml(stringWriterItems.ToString()); //Set the body to the Item XML Document bodyType.MessageParts = xmlDocumentItems.DocumentElement; envelope.Body = bodyType; //serilalize to outputstream System.Xml.Serialization.XmlSerializer mainSerializer = new System.Xml.Serialization.XmlSerializer(typeof(SharedXSD.EnvelopeType)); mainSerializer.Serialize(output, envelope); } }
We now need to compile the project and load the resulting DLL into AX.
Loading the Transformation Library
Going back to the Previous post where we created the Inbound port using the File System Adapter, we need to tell it to use our transformation library. To do so, first deactivate the port, and then set the Inbound Transforms to true.
From the inbound transform screen, we need to add the Library to the Main Transformation Library. Click the Manage Transformsand add the Library that we created and give it a name and description. Note that you need to set a class on the Manage transforms. This means that we can have more than one transform method in the class library and create a record for each of those.
We just added our transformation to the main repository and need to reference it in the Inbound transforms for this port
Notice that the configuration is a text field which can be loaded from a file. This is the text that is passed onto the Transform method created earlier.
After activating this, we should be ready to drop our file and start out batch job
So the File we are passing in look like this:
SS001,SS1ItemName SS002,SS2ItemName
After dropping the File into the Inbound directory, the batch picks it up and pushes it into the Message Queue.
As you can see the message that has been created by the transformation Library is similar to the one we used in the Previous post.
When running the batch job again, this queue is processed and the items are pushed into the Released Product table
This concludes the series of Importing a CSV file into AX using the File System Adapter. (*tears of joy rolling out now*)
Thanks. Nice article by the way
LikeLike
Great series of articles!
It really saved my day, as the “walkthrough” on MSDN isn’t a full solution.
Thanks a lot.
LikeLike
MSDN documentation is the reason why I did this. Glad it helped 🙂
LikeLike
Hello,
I am new to Ax. cannot find the answers any where can you please guide me is there any way to generate a txt file for an external system.
LikeLike
Hi Zainab, Are you trying to export AIF data into a text / csv file? Is that what you are asking for?
You could have a look at the outbound transforms, and do something similar there.
Or you can contact me at my email address from the “About me” page and we can discuss then
LikeLike
Hi Shashi,
I am trying similar transform for importing customer and i am able to generate xml file from text file the steps you mentioned above however i am facing difficulties in adding dirpartytable node inside custtable entity .
My text tile looks like
custgroup , Name
1,testcust1
1,testcust2
since Name is in dirpartytable I want to append dirpartytable node inside custable node but facing problem while doing that .
Any help will be greatly appreciated .
below XML work when i used it in file system adapter
http://schemas.microsoft.com/dynamics/2008/01/services/CustomerService/create
No
cust_1
John Smith
USA
Yes
john Smith
Business
john Smith
LikeLike
Hi AXDev,
What problem are you facing while addind the DirPartyTable node?
Is there any exceptions that have been registered by AIF that you can get a clue from?
If the import works using a file system adapter, you will need to manage to replicate that in your code to convert it from a CSV file.
Is there any code that you can paste out here where you are trying to add the DIrPartyTable?
If not, email me. (You can find my email in the “About me” section)
LikeLike
Hi
As I am a bit rusty in .NET/C# I will be pleased, if you could help me with the following 2 questions.
1 ————————–
I have tried to redo the procedures, you describe to import items from CSV-files.
It went well until I tried to compile your last code example.
In the code you write:
//InventTable collection
List inventTables = new List();
The compiler asks for a type argument after “List”.
As far as I can see, the syntax should be like:
List list1 = new List()
I cannot figure out what type I shall specify.
2 ————————–
I have problems using the namespaces ItemXSD and SharedXSD.
The compiler cannot find them.
In my project I have places the .cs files in the folders, you described:
C:\…\TestImportItemsCSV\ItemXSD\InventItemService_SharedTypes.cs
C:\…\TestImportItemsCSV\SharedXSD\Message.cs
My code starts as followes:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
namespace TestImportItemsCSV
{
public class LoonItemTransform : Microsoft.Dynamics.IntegrationFramework.Transform.ITransform
{
public void Transform(System.IO.Stream input, System.IO.Stream output, string configuration)
{
LikeLike
There are problems showing code that include “less than” xxx “greater than”.
My example
“List list1 = new List()” should be “List.LT.string.GT. list1 = new List.LT.string.GT.()”
Hope you understand, what I mean 🙂
LikeLike
Forget My question 1.
It was the copy function, that removed the characters 😦
LikeLike
Hi Ebbe
1. Thanks for pointing that out. That piece did not look right at all. Must have been the code formatting which took it away. I have made the change in the blog. That line List list1 = new List(); should instead look like this: List”LT”ItemXSD.AxdEntity_InventTable”GT” inventTables = new List”LT”ItemXSD.AxdEntity_InventTable”GT”();
replace “LT” with the less than sign, and “GT” with greater than sign
2. If you open the ItemXSD, SharedXSD files, the namespace should be defined there. You may have specified a different namespace when generating those classes.
LikeLike
Hi Shashi
Thank you for the quick answer (and changes).
1: I thought that I had copied your code correctly.
2: I must confess that I had not added the 2 files to the project.
One of my colleagues recognized my name and helped me out of the blunder.
Now it compiles without errors.
LikeLike
Hi Ebbe,
It took me a few tries to reply to your post regarding the syntax, WordPress definitely thinks I was inserting illegal HTML code 😉
Feel free to email me if you have any issues.
LikeLike
HI Shashi,
I want to import Exchange Rate and data in Ax 2012 from CSV file. I am facing some technical/logical problem in Importing of CSV file. As per your article i have created XML file and AIF services for importing of Exchange rate CSV file data.
I am facing big problem for importing Exchange rate data thriugh CSV –
I have 1 CSV file containg data(fields) –
FromCur TOCUR FROM DATE exchange rate To Date
“ARS”, “ARS”, “11/10/2011”, “1.0000000000”, “12/21/2011”
“ARS”, “AUD”, “11/10/2011”, “.2323379000”, “12/21/2011”
“ARS”, “GBP”, “11/10/2011”, “.1477650000”, “12/21/2011”
“ARS”, “BRL”, “11/10/2011”, “.4147492000”, “12/21/2011”
“ARS”, “CAD”, “11/10/2011”, “.2397888000”, “12/21/2011”
“ARS”, “CLF”, “11/10/2011”, “.0053205000”, “12/21/2011”
I am facing very big problem on Importing Exchange rate data- as in Ax 2012 data fileds (FromCurrency and TOcurrecny) fields are stored in different table (Table in Ax – ExchangeRateCurrencyPair) and another data fields(ValidTo, Valid From, Exchange rate) are stored in different Table (ExchangeRate)and these 2 tables are relation with field( ExchangeRateCurrencyType) which is RECID of row in Table (ExchangeRateCurrencyPair)
and i cannot understand how do I import Exchange rate data in (csv) will import data in both tables or how do i create Recid and then( recid of table ExchangeRateCurrencyType will add in Table exchangeRate table). As per AX data in two tables and and its related on recid basis.
The Exchange rate Data Structure in Ax 2012 is as below:
Table(ExchangeRateType- it defines Exchange rate type like Average, Budget, for each company)
Table (ExchangeRateCurrencyPair – which is pair of currency (CAD-USD, CAD-EUR and stored data based on row recid of Table ExchangeRateType ).
Table(ExchangeRate- fields ValidFrom, ValidTo, ExchangeRate) are related to Table row recid(ExchangeRateCurrencyPair) for each corresponding Currency pair.
How do I import data in all three table with single CSV file.
Please suggest me solution. its very urgent for me so give me as soon as solution.
Sachin
Ax Developer
LikeLike
Hi Sachin,
This is something which is different to the scope of this blog. Please email me regarding this issue.
LikeLike
HI!
Your walk through was a great help. I am able to successfully import the data.
But the problem I am facing is that my import is conditional i.e. first I have to read some data from AX and then based on it I have to import CSV file.
Can I do this through AIF or using .Net Business character is better.
Thanks
LikeLike
Hi Nirmala,
Have you tried to make that transformation code a part of the AOT, and then use proxies inside the Transformation project. The proxy classes should then connect to Ax
LikeLike
Great blog, but I have one small issue. To get the CSV file to process, I had to add the “EcoResProductService” into the service operations before it would process the file. Once this was added I was able to import, but only the first line is imported.
Any idea where I am going wrong ?
Have one more question, if I want to include other fields in the CSV file, is there a specific order they would need to be added to be recognised and passed to the correct field in the end XML ?
Many thanks
Andy
LikeLike
Ok, just checked it is not updating the InventTable at all !!
LikeLike
No problem, all sorted now. Working great thanks.
LikeLike
Excellent post. Thanks for putting all of this in a very easy to follow manner. Great job.
LikeLike
hi, great blog, trying to follow it through and hit a little issue, the link for the reference is no longer works, can you confirm the URL
Add a reference to the Microsoft.Dynamics.IntegrationFramework.Dll using the link above,
LikeLike
Have you tried the x86 directory? The machine I based this on might have installed the Client folder in the regular Programs Files directory. C:\Program Files (x86)\Microsoft Dynamics AX\60\Client\Bin\
LikeLike
sorry what i mean is adding the reference, i have gone into the class, and clicked add reference, and gone to the UNC etc, seems to have added ok, but the code is erroring as if the reference is not there, its erroring on
Microsoft.Dynamics.IntegrationFramework.Transform.ITransform
LikeLike
Is your Project set to “.Net Framework 4” or “.Net Framework 4 client profile” ? You need to guve it the full blown .Net framework 4
LikeLike
think i have, i have just e mail you screen shots to comfrim though
LikeLike
Well done walk through. While I am not strong in C#, you made the code elements very readable with comments. Thank you.
LikeLike
I Followed The Steps Above But i Can’t Find The File Message.xsd In The Specified Path
I Didn’t Find The Folder “C:\Program Files\Microsoft Dynamics AX\60\Server\bin\Application\Share\Include\” , Any Help , Thank You
LikeLike
If you have more than one instance of ax installed, then inside the Server folder, select the application name folder and then continue with the rest of the path.
LikeLike
Hi,
I have been able to successfully build a sales order transform file and load the assembly into AX 2012. We have sinced upgraded to R2 and am trying a payroll import. When trying to load the dll in AX, i get this message.
Error occurred when iterating through types of assembly ‘TF_Payroll, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null’: Unable to load one or more of the requested types. Retrieve the LoaderExceptions property for more information.
Any thoughts on what could be causing that error?
Thanks,
Kevin
LikeLike
Hi Kevin, I haven’t tried this on R2 Yet. Is the DLL that you attached while creating the library the one from R2 ? The versions may be different.
LikeLike
Hi,
I am also facing same problem, can I get any help on this topic.Are you able to resolve this issue.
LikeLike
Shashi,
This post is great thank you for taking the time to put this together. I think this is going to add great value. I am having trouble loading the CSV file.
The file system adapter is unable to read the file D:\DynamicsAX\SalesOrderDrop1\SalesImportTEST_1.csv. Error:
LikeLike
Sorry I have resolved this by adjusting the permissions of the Inbound and Outbound Folder. Moving on to other errors now…
Thanks
Eric
LikeLike
estimated
need to ask a favor, someone uploaded a txt file in the advanced bank reconciliation with some standard ax 2012 bank? I have configured the services aif but I can not import a file someone can tell me how is the structure even in xml file?
Thank you very much.
LikeLike
If you have the AIF services ready, can you check the XSD of that?
The XSD defines the structure of the XML file
LikeLike
Hi,
Thanks for this excellent post. What I trying to achieve is import bank statement as csv file using bankstatment service.
I have found the relevant xsd following your previous post (BankStmt.xsd, SharedType.xsd and Message.xsd).
Now, using command prompt I have created the classes as well. But when I add it to the project it keeps giving me errors like:
The namespace ‘ImportBankStatement.BankStmtXSD’ already contains a definition for ‘AxdEnum_boolean’
I am not sure how come there are two definitions. I want to comment them out but not sure if that will affect something else.
It will be great if you can also throw some light on how I can achieve this import as our banks only gives us statements in csv or pdf formats.
Thanks again
Daws
LikeLike
Hi Daws, Glad to see there is still interest in this post 🙂
Coming to your issue of 2 definitions of AxdEnum_boolean being created, I could not replicate that with the service you mentioned. Which cs files are you referring to?
Does your project have anything else in it?
Did you create a separate namespace for these files (i.e. its not the same as the project namespace) ?
You can get me on my email and send me the XSD files and the CS files you created. That may hold more clue as to what is going over there.
LikeLike
Hi Shashi,
There is nothing else in my project. I have the same namespace to the cs file as the name space of my project.
xsd Message.xsd /classes /namespace:NG.AX.AIF.ImportBankStatement.SharedXSD
xsd BankStmt.xsd SharedTypes.xsd /classes /namespace:NG.AX.AIF.ImportBankStatement.BankStmtXSD
This is the command I used to generate the cs file. NG.AX.AIF.ImportBankStatement is the namespace under which I am having my project. I will email you out my xsd and cs files
LikeLike
Thanks Shashi,
For solving my problem. For reference to others, Unknowingly I had included 2 copies of the same file located in a different folder.
LikeLike
Hi,
Thank you very much for you post! I’ve been following the steps but I have a problem when trying to assign a related data source and serialize all.
In my document query, I have my first data source, called DataHeader, (as you have InventTable) with another one related, called DataLine. I assign without problems dataHeader using your post, but I don’t know how to include the dataLines record in my AxdQuery in order to serialize them. I have all records in List variable but… is there any sentence I have to use to include them in the serialize document?
Thanks in advance!!
LikeLike