Hello,
I want to know is there any ways to write coding to import data in the excel csv format to Ax. The purpose is to update certain field in the existing data without deleting the existing data or having any duplicate data. It is possible to do so using excel templates. But how about using csv excel format? It will be faster compare to excel template when having above 1000 records.
Can anyone give any idea?
Thank you
Hi
have look under Administation/Periodic/Data Export import/
Here you have multiple options to import using Excel, csv or text files. ther are also option to use validation when importing.
To my knowlege ther is no performane difference using CSV or Excel. Ther is also something called solution center but this is not part of standard AX, it is a add-on sold by a company called ToIncrease.
The standard import/export works fine, I have uesd many times. Wheter you will update or insert data is up to the data you are importing, if a primary key is present in the data, you will get a update, if not it will be inserted, Be sure to set the import script to validate.
Hello,
I use this script as a starting point when importing or updating data from a csv file. I hope this answers your question.
static void VSP_UpdateCustNAV(Args _args)
{
AsciiIo asciiIo;
container con;
FileIoPermission perm;
CustTable custTable;
CustAccountNAV custNav;
VATNum vatNumAX;
VATNum vatNumFile;
Name Name;
// #define.ExampleFile(@“c:\test.txt”)
// #define.ExampleOpenMode(“r”)
;
// The AsciiIO.new method runs under code access permission.
perm = new FileIoPermission(@“E:\file.csv”,“R”);
if (perm == null)
{
return;
}
// Code access permission scope starts here.
perm.assert();
// BP deviation documented.
asciiIo = new AsciiIo(@“E:\file.csv”,“R”);
asciiIo.inFieldDelimiter(";");
if (asciiIo != null)
{
con = asciiIo.read();
while (asciiIo.status() == IO_Status::Ok)
{
name = conPeek(con,2);
custNAV = conPeek(con,1);
ttsbegin;
custTable.clear();
select firstonly forupdate custTable
where custTable.Name == name;
if (custTable.RecId)
{
custTable.CustAccountNav = custNAV;
custTable.doUpdate();
info(strfmt(“NAME: FROM %1 TO %2”,custTable.AccountNum,custTable.CustAccountNav));
}
ttscommit;
con = asciiIo.read();
}
}
}
What I could suggest is to look at an ETL tool. There are open source tools available for free download.
Talend Open Studio is an open source ETL tool for data integration and migration experts. It’s easy to learn for a non-technical user. What distinguishes Talend, when it comes to business users, is the tMap component. It allows the user to get a graphical and functional view of integration processes. For more information: http://www.talend.com/
Hello Grutald,
When it comes to importing data into DAX, I would stick with -
This way Ax can take care of validations, number sequence generation etc.
Regards,
Hi, i’m not sure if anyone could help me with this but would appreciate some inputs. I’m not a developer nor a programmer but more to Systems and Network.
Scenario:-
We use a School’s management system that uses SQL as a backend (excellent data exporting available).
We use Dynamic AX for our Accounting purposes.
Everytime we have new students > our Admissions officer would enter the details for the student (i.e. Parents name, Address, Student name, Student ID etc etc under the School’s Management system > the Admissions dept would then send an email to our Accounts dept with the same set of details > Account dept would then re-enter the details onto AX manually.
My objective:-
I would like to automate these processes either by importing the details in bulk using csv to our Accounting system (Dynamic AX) or using similar methods. I believe there are some fields that have sequential numbers. Would appreciate a complete idiot guide and i dont mind to take this offline if someone is willing to help. Thanks in advance
You can do it in AX as well, better to export student information from your school management software in to Excel and you can update this student information as customer in dynamics AX import through import / export utility.
hi,
Try this…
static void csv_loadCSVFile(Args _args)
{
Dialog dialog;
DialogField dialogFileName;
SysOperationProgress simpleProgress;
Filename filename;
FileIOPermission permission;
TextIO textIO;
ExcelFileImport ExcelFileImport;
str s11;
int i;
Container c1,c2;
CompanyInfo companyInfoLoc = CompanyInfo::find();
Container filterCriteria;
#File
#avifiles
;
dialog = new Dialog(“Importing Text File”);
dialogFileName = dialog.addField(typeid(Filenameopen), “File Name”);
filterCriteria = [’*.txt’];
filterCriteria = dialog.filenameLookupFilter(filterCriteria);
dialog.run();
if (dialog.run())
filename = dialogFileName.value();
if(!filename)
{
info(“Filename must be filled”);
throw("");
}
permission = new fileIOpermission(filename,#io_read);
permission.assert();
textIO = new TextIO(filename,#io_read);
textIO.inFieldDelimiter(’,’);///Change the Delimeter if it is , or ; etc
simpleProgress = SysOperationProgress::newGeneral(#aviUpdate, ‘Importing item data’,100);
if(textIO)
{
while(textIO.status() == IO_Status::Ok)
{
c1 = textIO.read();
s11 = conpeek(c1,1);
if(strlen(s11) > 1)
{
ExcelFileImport.itemid = Conpeek(c1,1);
ExcelFileImport.name = Conpeek(c1,2);
ExcelFileImport.insert();
i++;
simpleProgress.incCount();
simpleprogress.setText(strfmt(“Lines imported: %1”, i));
info(strfmt(“Item : %1 has been created”,ExcelFileImport.itemid));
sleep(10);
}
}
}
}
I’m a new programmer. Since I’m almost doing the same project except that I set a limit on the data imports.
The file I’m trying to imprt is too big, it has 400thou something entries. In column “A” I want to set a code that will only import data with the entry name “Accept”. Would someone help me on this. Thanks