Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Import Excel file thro...
Microsoft Dynamics AX (Archived)

Import Excel file through batch job in AX 2012 R3

(0) ShareShare
ReportReport
Posted on by Nadeem 815

Hi,

Is there a way to import excel file in to AX using batch job.

Export to Excel using batch job works fine using below but is there a way to import excel data in Batch as well?

Microsoft.Dynamics.AX.Fim.Spreadsheets.Spreadsheet spreadsheet;

Microsoft.Dynamics.AX.Fim.Spreadsheets.CellProperties cellProperties;

Microsoft.Dynamics.AX.Fim.Spreadsheets.ColumnProperties columnProperties;

Thanks,

Nadeem

*This post is locked for comments

  • Suggested answer
    Nadeem Profile Picture
    Nadeem 815 on at
    RE: Import Excel file through batch job in AX 2012 R3

    Hi Kavitha,

    I have copied over three methods from the class which I have used to export data to excel in Batch.

    Run on property must be set to server for this class.

    You can add methods to class for running in batch and modify the execute method accordingly to make it work for you.

    public static void main(Args args)

    {

       AssetTrans assetTrans;

       SysExcelApplication application;

       SysExcelWorkBooks workbooks;

       SysExcelWorkBook   workbook;

       SysExcelWorksheets worksheets;

       sysExcelWorksheet   worksheet;

       SysExcelCells            cells;

       SysExcelCell              cell;

       int                             row;

       DimensionAttributeValueSetItemView dimAttrSet;

       DimensionAttribute dimAttr;

       str                            dimAttrStr;

       Map                         dims;

       int                            dimNum;

       ExcelExport Export = ExcelExport::newImportServer();

       ;

       Export.init();

       if (Export.prompt())

       {

           Export.run();

       }

    }

    public void run()

    {

           setPrefix(ExcelExport::description());

           if (this.validate())

           {

               this.execute();

               info ("@SYS70406");

           }

    }

    private void execute()

    {

       boolean                             fileFound;

       boolean                             fileOk;

       AssetTrans                         assetTrans;

       int                                      row;

       DimensionAttributeValueSetItemView  dimAttrSet,dimAttrSetMapVar;

       DimensionAttribute         dimAttr,dimAttrMapVar;

       str                                     dimAttrStr;

       Map                                  dims;

       int                                     dimNum;

       str                                     pathstr;

       anytype                            amountstr;

       str                                     ak;

       str                                    MarketSalesRegion, costCenter, Product, SalesRep,Project;

       Map                                 map;

       MapEnumerator              mapEnumerator;

       NameAlias                       nameAlias;

       int                                    counter = 1;  // start map with 1

       // Below custom table contians dimension names

       AssetTransExcelExport   AssetTransExcelExport,AssetTransExcelExport_del, AssetTransExcelExport_DimName, AssetTransExcelExport_DisplayVal;

       #define.ReadWritePermission('RW')

       #define.ExcelColumnWidth(08) // was 15

       #define.ExcelCellFontSize("Microsoft.Dynamics.AX.Fim.Spreadsheets.CellFontSize")

       #define.Size9("Size9")

       Microsoft.Dynamics.AX.Fim.Spreadsheets.Spreadsheet spreadsheet;

       Microsoft.Dynamics.AX.Fim.Spreadsheets.CellProperties cellProperties;

       Microsoft.Dynamics.AX.Fim.Spreadsheets.ColumnProperties columnProperties;

       void addColumn(str _name)

       {

           columnProperties = new Microsoft.Dynamics.AX.Fim.Spreadsheets.ColumnProperties();

           columnProperties.set_Width(#ExcelColumnWidth);

           spreadSheet.InstantiateColumn(columnProperties);

           cellProperties = new Microsoft.Dynamics.AX.Fim.Spreadsheets.CellProperties();

           cellProperties.set_FontSize(CLRInterop::parseClrEnum(#ExcelCellFontSize, #Size9));

           cellProperties.set_Bold(true);

           spreadSheet.AddStringCellToWorkbook(_name, cellProperties);

       }

       map = new Map(Types::String, Types::Integer);

       select firstOnly * from assetParameters;

       filepath = this.Modifyfilename(assetParameters.AssetTransExcelExportPath);

       new FileIOPermission(filepath, #ReadWritePermission).assert();

       spreadSheet = new Microsoft.Dynamics.AX.Fim.Spreadsheets.Spreadsheet();

       if (!spreadSheet.CreateSpreadsheet(filepath))

       {

           spreadSheet.Dispose();

           throw error(strFmt("@SYS72245", filepath));

       }

       addColumn("Voucher");

       addColumn("Transaction date");

       addColumn("Fixed asset number");

       addColumn("Transaction type");

       addColumn("Amount");

       addColumn("Fixed asset group");

       ttsBegin;

       delete_from AssetTransExcelExport_del;

       ttsCommit;

       ttsBegin;

       select forUpdate AssetTransExcelExport_DimName;

       while  select  dimAttrSetMapVar

             join Name from dimAttrMapVar

                   group by name

                   where dimAttrMapVar.RecId == dimAttrSetMapVar.DimensionAttribute

       {

             nameAlias = dimAttrMapVar.Name;

             if (!map.exists(nameAlias))

             {

                map.insert(nameAlias, counter);

                counter++;

             }

             AssetTransExcelExport_DimName.Name = dimAttrMapVar.Name;

             AssetTransExcelExport_DimName.doInsert();

        }

       ttsCommit;

       mapEnumerator = map.getEnumerator();

       while (mapEnumerator.moveNext())

       {

          addcolumn(mapEnumerator.currentKey());

       }

      while select * from assetTrans

      {

         spreadSheet.MoveToNextRowInWorkbook();

         cellProperties = new Microsoft.Dynamics.AX.Fim.Spreadsheets.CellProperties();

         cellProperties.set_FontSize(CLRInterop::parseClrEnum(#ExcelCellFontSize, #Size9));

         spreadSheet.AddStringCellToWorkbook(assetTrans.Voucher, cellProperties);

         spreadSheet.AddStringCellToWorkbook(date2str(assetTrans.TransDate,123,2,DateSeparator::Slash,2,DateSeparator::Slash,4), cellProperties);

         spreadSheet.AddStringCellToWorkbook(assetTrans.AssetId,cellProperties);

         spreadsheet.AddStringCellToWorkbook(enum2str(assetTrans.TransType), cellProperties);

         spreadSheet.AddStringCellToWorkbook(num2str(assetTrans.AmountCur,0,2,1,2),cellProperties);

         spreadsheet.AddStringCellToWorkbook(assetTrans.AssetGroup, cellProperties);

         while select dimAttrSet

               where dimAttrSet.DimensionAttributeValueSet == assetTrans.DefaultDimension

                   join Name from dimAttr

                       order by Name

                       where dimattr.RecId == dimAttrSet.DimensionAttribute

         {

                    select forUpdate firstOnly AssetTransExcelExport_DisplayVal

                       where AssetTransExcelExport_DisplayVal.Name ==  dimAttr.Name;

                   {

                        ttsBegin;

                        AssetTransExcelExport_DisplayVal.DisplayValue= dimAttrSet.DisplayValue;

                        AssetTransExcelExport_DisplayVal.doUpdate();

                       ttsCommit;

                   }

          }

           while select * from AssetTransExcelExport

           {

                spreadsheet.AddStringCellToWorkbook(AssetTransExcelExport.DisplayValue, cellProperties);

           }

           // clear table for next record

           ttsBegin;

          while select forUpdate * from AssetTransExcelExport_del

           {

               AssetTransExcelExport_del.DisplayValue = "";

               AssetTransExcelExport_del.doUpdate();

           }

           ttsCommit;

      }

       spreadSheet.WriteFile();

       spreadSheet.Dispose();

       CodeAccessPermission::revertAssert();

    }

  • Pravasti AK Profile Picture
    Pravasti AK 2,985 on at
    RE: Import Excel file through batch job in AX 2012 R3

    Hi Nadeem,I used the same code in the given link sashanazarov.blogspot.co.uk/.../exporting-to-excel-with.html.

    only the file location I have changed.In this post you have clearly mentioned export excel is working fine.Can you be more specific on this.

  • Nadeem Profile Picture
    Nadeem 815 on at
    RE: Import Excel file through batch job in AX 2012 R3

    Hi Kavitha, I can't see you full code to identify the cause of error.  It would be better to export the file as CSV and open the exported file in excel.

  • Pravasti AK Profile Picture
    Pravasti AK 2,985 on at
    RE: Import Excel file through batch job in AX 2012 R3

    Hi Nadeem,

     I  followed this blog to create my excel export  ,I can able to create a excel file .When i tried to do in batch ,i am getting error in this line.:spreadSheet.InstantiateColumn(columnProperties);

  • Nadeem Profile Picture
    Nadeem 815 on at
    RE: Import Excel file through batch job in AX 2012 R3

    Check below post.

    sashanazarov.blogspot.co.uk/.../exporting-to-excel-with.html

  • Pravasti AK Profile Picture
    Pravasti AK 2,985 on at
    RE: Import Excel file through batch job in AX 2012 R3

    Hi nadeem,

    As u said u r comfort with export to excel through batch process.When I try to do i am getting error when i tried to run in batch.

    . System. NullReferenceException: Object reference not set to an instance of an object. at Dynamics. Ax.Application.

    error line "spreadSheet.InstantiateColumn(columnProperties);".help me out to go ahead with my process.

  • Nadeem Profile Picture
    Nadeem 815 on at
    RE: Import Excel file through batch job in AX 2012 R3

    Hi Thuyen Dang,

    You can try DIXF and run the job as batch which will read the file from  selected location at selected date/time and update AX.

    Other option is to use CSV which will work accordingly as well.

    Another options is to use Excel Add-In to publish data in to AX.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Import Excel file through batch job in AX 2012 R3

    Hi Nadeem,

    SysExcelApplication  cannot work with batch job !

    Thanks,

    Thuyen Dang

  • Nadeem Profile Picture
    Nadeem 815 on at
    RE: Import Excel file through batch job in AX 2012 R3

    Try below link.

    axpedia.blogspot.co.uk/.../import-from-excel-file-using-x-in-ax.html

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Import Excel file through batch job in AX 2012 R3

    Hi Nadeem,

    I have used Speadsheet for export excel thru batchjob success, but I can't import excel with spreadsheet.

    Can you show your code for import ?

    Thank and regard,

    Thuyen Dang

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

photo of Muhammad Affan

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

thumbnail image of a pie chart section

Top 10 leaders for November!

Congratulations to our November super stars!

thumbnail image of the word Tip

Tips for Writing Effective Verified Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,391 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,445 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans