Import 20K records from Excel file to axapta

Finance and Operations/AX Developer Forum
1

Hello,
I have a requirement in which I need to import around 20K records from excel file to Axapta i.e. in to smmBusRelTable and probably in one more table .Now I was thinking about the possible methods which are better performance wise as well.

  1. Can I use Standard Axapta Import feature for above requirement .If yes, how is it performance wise?

  2. Shall I write a Job for this and then do it .If yes, then to optimize the database operation How can i use Insert_recordset here. As my fields values will be coming from excel file cells. Can someone able to help me on this if possible with proper syntax.

  3. Any other better possible option?

Please help me!

Regards,
Manoj Parashar

2

Hello Manoj,

Data can be easily imported from Microsoft Office Excel by using the import feature of Microsoft Dynamics AX. To reduce the potential of error, the import feature include a wizard that generates Microsoft Office Excel workbook templates and a dialog box that controls the import of data into Microsoft Dynamics AX after the data has been entered into the workbook.

Note : Microsoft Windows Regional options date format and the date format in Microsoft Office Excel on the computer that is performing the import must be the same. If the formats are not the same, the date field will be empty.

Click Administration > Periodic > Data export/import > Excel spreadsheets > Template Wizard.

The Microsoft Office Excel Template Wizard helps you create one or more templates in Microsoft Office Excel where you can enter data, and then import the data into Microsoft Dynamics AX. The wizard creates a worksheet in the workbook for each table that you select.

Thanks & Regards,

Shankar Dutt Sharma

3

I would suggest the following procedure.

  1. Import the excel data into table in a dummy database of sql server.
  2. Create a odbc connection to the dummy database in the machine
  3. Create a job in Axapta 2009 with odbc connectivity and pull the data by sql query.
  4. After reading the data then insert into respective tables of Axapta.

LoginProperty LP = new LoginProperty();
OdbcConnection myConnection;
Statement myStatement;
Str sqlQuery;

;

LP.setDSN(‘DAXServer’);
try
{
myConnection = new OdbcConnection(LP);
myStatement = myConnection.createStatement();
sqlQuery= “SELECT * from Prices where DataAreaid = ‘dat’’” ;
myResult = myStatement.executeQuery(sqlQuery);

while (myResult.next())
{
ttsbegin;
_priceDiscTable.AccountCode=myResult.getInt(3);
_priceDiscTable.ItemRelation=myResult.getString(4);
_priceDiscTable.AccountRelation=“PG”;
_priceDiscTable.QuantityAmount=myResult.getReal(6);
_priceDiscTable.Amount = any2Real( myResult.getString(9));
_priceDiscTable.Currency=myResult.getString(10);
_priceDiscTable.SearchAgain=myResult.getInt(14);
_priceDiscTable.relation=myResult.getInt(16);
_priceDiscTable.UnitId=myResult.getString(17);
_priceDiscTable.Module=myResult.getInt(20);
_priceDiscTable.insert();
ttscommit;
countItms++;

}

print countItms;

pause;
}
catch
{
info(“Check username/password.”);
return;
}

4

Hello

Use standard import functionality. Don’t use excel, you can use custom type importing option.

Convert your excel data into CSV format and define the table & columns and import the data.

20k records will be import few minutes.

This is the better way to import bulk records.

All the best.