How to Import data from Excel file to Ax 2009

Hi friends,

   Today we are going to import data from excel to Ax 2009 by code and by using inbuilt features.

First we import data to a dummy CustTable and how to import data without writing any code.

static void Excelimport(Args _args)

{

SysExcelApplication     application;

SysExcelWorkbooks       workbooks;

SysExcelWorkbook        workbook;

SysExcelWorksheets      worksheets;

SysExcelWorksheet       worksheet;

SysExcelCells           cells;

SysExcelCell            cell;

SysExcelRange           totrange,range;

//ysOperationProgress    simpleProgress;

COMVariant              file;

int                     i,imported,  noofrows;

str                     strExample1[],strExample2[];

CustTable1              custTable;//created my own table

#excel

#avifiles

;

try

    {

        application = SysExcelApplication::construct();

        application.visible(false);

        workbooks   = application.workbooks();

        file    = new COMVariant();

        file.bStr("D:\Book1.xlsx");

        workbook    = workbooks.add(file);

        worksheets   = workbook.worksheets();

        worksheet   = worksheets.itemFromNum(1);

    }

catch(Exception::Error)

    {

        throw error("cannot open excel file");

    }

try

    {

        totrange   =   worksheet.cells().range(#ExcelDataRange);

        range   =   totrange.find("*", null, #xlFormulas, #xlWhole,

                                    #xlByRows, #xlPrevious);

        if(range)

        {

            noofrows    =   range.row();

        }

        else

        {

            application.workbooks().close();

            application.quit();

            throw error("D:\Book1.xlsx");

        }

    }

catch(exception::error)

    {

       application.workbooks().close();

       application.quit();

       throw error("error has occured");

    }

    cells   =   worksheet.cells();

 //   simpleProgress = SysOperationProgress::newGeneral(#aviUpdate, 'file is importing', 100);

    ttsbegin;

    //read rows of excel

    for(i=1; i<=noofrows; i++)

    {

        imported++;

       print(imported);

        strExample1[i]           = cells.item(i,2).value().bStr();

        strExample2[i]           = cells.item(i,1).value().bStr();

        custTable.AccountNum     = strExample1[i];

        custTable.Name           = strExample2[i];

        custTable.insert();

    }

    pause;

    ttscommit;

    //display info

    info(int2str(imported) + " records imported.");

    application.workbooks().close();

    application.quit();

}

/* we can also have other values such as

pONo                    =   COMVariant2Str(cells.item(row, 1).value());

itemid                  =   COMVariant2Str(cells.item(row,2).value());

InventSize         =   COMVariant2Str(cells.item(row, 3).value());

batchNumber             =   COMVariant2Str(cells.item(row, 4).value());

poddate                 =   cells.item(row, 5).value().date();

expdate                 =   cells.item(row, 6).value().date();

srNo                    =   COMVariant2Str(cells.item(row, 7).value());

Certs                =   str2enum(Certs,cells.item(row, 8).value().bStr());

CertiAnalysis        =   str2enum(CertiAnalysis,cells.item(row, 9).value().bStr());

quantity                =   cells.item(row, 10).value().double();

_ConfigId               =   COMVariant2Str(cells.item(row, 12).value());

_InventColorId          =   COMVariant2Str(cells.item(row, 13).value());

_InventSiteId           =   COMVariant2Str(cells.item(row, 14).value());

_WMSLocationId          =   COMVariant2Str(cells.item(row, 15).value());

_InventLocationId       =   COMVariant2Str(cells.item(row, 16).value());

_WMSPalletId            =   COMVariant2Str(cells.item(row, 17).value());

ClosedTransactions      =   str2enum(ClosedTransactions,cells.item(row, 18).value().bStr());

ClosedTransQty          =   str2enum(ClosedTransQty,cells.item(row, 19).value().bStr());*/

now we see another type

The following steps may be used in importing the vendors’ multiple addresses in AX2009 through the excel template. Similar approach can be adopted for the Customers also by slightly changing the view i have recommended in this post and at other relevant areas. Send me a mail if you find any trouble . I will be happy to help.

1. Navigate to CEU -> Administration from the Address bar by clicking the navigation path arrows.

2. Click Area Page node: Administration -> Periodic -> Data export/import -> Excel spreadsheets -> Template Wizard.

Form name: Microsoft Office Excel Template Wizard

3. Click the Next > button.

4. Switch to the Open workbook tab on the Microsoft Office Excel Template Wizard form.

5. Change File name from '' to 'C:\….Path…..\MultiAdd_Vendors.xls'.

6. Click the Next > button.

7. Switch to the Select tables tab on the Microsoft Office Excel Template Wizard form.

8. Click Show all tables & Select the “Address” table.

9. Click ‘>’ button to move the table ‘Address’ to selected objects and then Click the Next > button.

10. Switch to the Generate field list tab on the Microsoft Office Excel Template Wizard form.

11. Click the Next > button.

12. Switch to the Select fields tab on the Microsoft Office Excel Template Wizard form.

13. Click the Next > button.

14. Switch to the Import definition group tab on the Microsoft Office Excel Template Wizard form.

15. Click the Next > button.

16. Switch to the Export data tab on the Microsoft Office Excel Template Wizard form.

17. Click the Next > button.

18. Switch to the Finished tab on the Microsoft Office Excel Template Wizard form.

19. Click the Finish button.

20. Close the Microsoft Office Excel Template Wizard form.

21. Create a View in AX. As shown below. I wanted to attach the .xpo for the view but windows live spaces does not support . send me a message if you need and i will provide it over email.

22. Now come to MS Excel . Open the workbook template Multiadd_Vendor.xls we created. Insert a new worksheet and access the above views data in the worksheet as shown below.

a. Under data tab >Select the “From Other Sources” in Get External Data.

b. Type ‘ . ‘ in the server name if the SQL server is installed locally Else type the name of SQL server.

c. Select the Use Windows Authentication if the current user has access to the database else use the Unser name and password for accessing the database.

d. Select the database and the View we have just now created.

e. Click FInish

f.Select the worksheet location where you wish to get the data. ( $A$1 mostly).

You will get the data as shown below

23. Insert another worksheet . Copy the view’s data from the sheet in step (22f)… and paste it in the new worksheet in workbook MultiAdd_Vendors.xls .

24. Move the column AccountNum  to extreme left (make it the first column). The worksheet data must be sorted on the field AccountNum in ascending order. You may arrange the columns as shown below screenshot.

25. In the worksheet template for address i.e. worksheet name “Address_1” add a column “Vendor” at the end as shown below. Populate the template with address data for the Vendor Code in AX .

26.The template field “Table ID of the main table” must be set to 2303 which is the table ID of the DirPartyTable.

27. In the column “Reference” use the VLookup function as shown below. This will get the recid from the DirParty ( in sheet 9) for the vendor in sheet Address_1

28. Copy & paste the formula in all rows for column “Reference”.

29. Copy the column “Reference” and paste special into itself with only values. The data in this column should be in text format even if  numbers are entered. For this you may have to precede the numbers with an apostrophes so that there is a green mark on the each of the upper left corner of the cells as you can see in column Vendor 

Your data is ready to be pulled in AX.

30. Go to the Definition group created while creating the excel template . Click the Table setup button. Go to Import criteria tab.

31. Select the definition group and click import

32. You will get an infolog that ‘No data imported’. But do not worry . The data is imported. Refer to the following link for more information about the infolog.