Though there are many ways to upload data into MS Dynamics Ax, one of the way is by making job in MS Dynamics Ax, though it is a traditional way.
Let us assume that the data to be uploaded is available in CSV format. Also assume that the data to be uploaded is a list of city under different states, as shown below:
In this case data to be uploaded are:
1. CountryRegionId in Column number 2
2. Description in Column number 4
3. Name in Column number 7
4. StateId in Column number 9
To write a job in MS Dynamics Ax, go to the AOT by pressing CTRL+D. Expand the under Jobs.
Add a new job by right clicking on the Job node.
Then add the following code in the code window.
Structure of the Table is:
Sample Code:
static void zz_SNMJob_ImportLogisticAddressCity(Args _args)
{
LogisticsAddresssCity logisticsAddresssCity;
LogisticsAddresssCity uploadLogisticsAddresssCity;
Name name;
CommaIO csvFile;
container readCon;
counter icount, inserted;
Dialog dialog;
DialogField dfFileName;
FileName fileName;
#File
dialog = new Dialog("Pick the file");
dfFileName = dialog.addField(extendedTypeStr(FileNameOpen));
dialog.filenameLookupFilter(["All files", #AllFiles]);
if(dialog.run())
{
filename = dfFileName.value();
}
csvFile = new CommaIO(filename,'r');
if(csvFile)
{
readCon = csvFile.read();
ttsBegin;
while (csvFile.status() == IO_Status::OK)
{
readCon = csvFile.read();
name = conPeek(readCon, 7);
if(readCon)
{
select LogisticsAddresssCity where logisticsAddresssCity.Name == name;
if(!LogisticsAddresssCity)
{
info(strFmt("%1 %2", conPeek(readCon, 4), conPeek(readCon, 7)));
uploadLogisticsAddresssCity.clear();
uploadLogisticsAddresssCity.CountryRegionId = conPeek(readCon, 2);
uploadLogisticsAddresssCity.Description = conPeek(readCon, 4);
uploadLogisticsAddresssCity.Name = conPeek(readCon, 7);
uploadLogisticsAddresssCity.StateId = conPeek(readCon, 9);
uploadLogisticsAddresssCity.Insert();
inserted++;
icount++;
}
}
}
ttsCommit;
}
info(strFmt("%1 records has been imported out of %2", inserted, icount));
}
Explanation of the Code:
LogisticsAddresssCity logisticsAddresssCity;
LogisticsAddresssCity uploadLogisticsAddresssCity;
LogisticsAddresssCity uploadLogisticsAddresssCity;
Declare two variable / object from the Table Class LogisticsAddressCity, as the data to be uploaded into the Table named LogisticsAddressCity. The first variable could be used to store or search for a name of city already existed into the database or not. And the other (uploadLogisticsAddresssCity) will be used for uploading one record.
Name name;
The variable name will be used for storing the name from the CSV file to search from the table if it exists or not.
CommaIO csvFile;
container readCon;
counter icount, inserted;
Dialog dialog;
DialogField dfFileName;
FileName fileName;
#File dialog = new Dialog("Pick the file");
container readCon;
counter icount, inserted;
Dialog dialog;
DialogField dfFileName;
FileName fileName;
#File dialog = new Dialog("Pick the file");
dfFileName = dialog.addField(extendedTypeStr(FileNameOpen));
dialog.filenameLookupFilter(["All files", #AllFiles]);
dialog.filenameLookupFilter(["All files", #AllFiles]);
For handling CSV file and Dialog Box these commands are used.
if(dialog.run())
To run the Dialog Box – dialog.run() is used, and if it is a success without any error, then…
{
filename = dfFileName.value();
}
csvFile = new CommaIO(filename,'r');
Get the CSV file as an Object, in a read only mode.
if(csvFile)
{
readCon = csvFile.read();
If the CSV File is having no erorr as far as its input and output access is concerned then read the content of the file as one line at a time.
ttsBegin;
while (csvFile.status() == IO_Status::OK)
{
readCon = csvFile.read();
name = conPeek(readCon, 7);
if(readCon)
Begin the transaction processing by issuing a command ttsBegin.
If the status of the file is OK that is not EOF, then read the column 7 where the name of the City is there and put assign the value to the variable ‘name’. Then read the content and if the content is healthy then go further.
select LogisticsAddresssCity where logisticsAddresssCity.Name == name;
if(!LogisticsAddresssCity)
Get data from the table containing names of City (LogisticsAddressCity) where the name is same as it is contained in variable ‘name’. If the name of the city does not exists, then go further.
info(strFmt("%1 %2", conPeek(readCon, 4), conPeek(readCon, 7)));
uploadLogisticsAddresssCity.clear();
uploadLogisticsAddresssCity.CountryRegionId = conPeek(readCon, 2);
uploadLogisticsAddresssCity.Description = conPeek(readCon, 4);
uploadLogisticsAddresssCity.Name = conPeek(readCon, 7);
uploadLogisticsAddresssCity.StateId = conPeek(readCon, 9);
uploadLogisticsAddresssCity.Insert();
inserted++;
icount++;
For information purpose put the string into the info function about the values from the column 4 and 7 of the CSV file.
Then first clear the table variable uploadLogisticsAddresssCity, then put the values to the data field for that table variable. Insert the data into the table by using insert() method of the table class.
For the purpose of counting two counters were used (inserted and icount).
ttsCommit;
Each transaction processing is committed into the database by ttsCommit command.
info(strFmt("%1 records has been imported out of %2", inserted, icount));
At last another info function is used to show how many records were inserted.
By running the job we get:
On Finishing:
The data is uploaded into the table:
