web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / Split values on export...
Finance | Project Operations, Human Resources, ...
Suggested answer

Split values on export to Excel

(0) ShareShare
ReportReport
Posted on by NDingankar22 515

Hi.

Greetings

I have  a form where the details are as follow.

The item details are retrieved from Movemet JOurnal (posted) in ax 2012

Hence diff movement journals can have one or many multiple items. The details are saved in that table in a field concatenate by + sign

I want to export the item details into one single column. How can i acheive that when i export to excel(by code lets say as standard export to excel will not work.

How do i split the item into different column each of excel .Can anyone assist.

Dynsmics_2D00_Community.png

I have the same question (0)
  • ergun sahin Profile Picture
    ergun sahin 8,824 Moderator on at
    RE: Export to Excel-Ax 21012

    Can you show us what u want as a result.  Or the table you showed us are  the result you are trying to achieve?

  • NDingankar22 Profile Picture
    NDingankar22 515 on at
    RE: Export to Excel-Ax 21012

    Thnk you for reply. The result should be as follows in excel. Like every item in each excel column(separate excel column)

    Jounrlnum  item1                      item2                                      item3                                                  item4

    GJ1             itema(apple)           itemb(badmonton)                        

    GJ2             itemc(choloclate)    itemD(dettol)                          

    GJ2                                                                                      

    G3              itema(apple)           itemb(badminton)                 item (cholocalte)                                itemD(detto))

  • Suggested answer
    ergun sahin Profile Picture
    ergun sahin 8,824 Moderator on at
    RE: Export to Excel-Ax 21012

    You can use str2con or strSplit

    www.schweda.net/blog_ax.php

    then you can export to excel

    www.theaxapta.com/.../exporting-data-to-excel-from-axapta-x.html

  • NDingankar22 Profile Picture
    NDingankar22 515 on at
    RE: Export to Excel-Ax 21012

    ok.but how to generate columns in excel .. I will split it in comtainer but excel column generation is i m stuck at

  • Suggested answer
    ergun sahin Profile Picture
    ergun sahin 8,824 Moderator on at
    RE: Split values on export to Excel

    I haven't tested but something like this will fix your problem. Maybe minor changes may be required.

    static void TheaxaptaCreateExcel(Args _args)
{
    SysExcelApplication  xlsApplication;
    SysExcelWorkBooks    xlsWorkBookCollection;
    SysExcelWorkBook     xlsWorkBook;
    SysExcelWorkSheets   xlsWorkSheetCollection;
    SysExcelWorkSheet    xlsWorkSheet;
    SysExcelRange        xlsRange;
    CustomTable          customTable;
    int                  row = 1;
    str                  fileName;
    container            paramAsCon;
    int i;
    
    //Filename
    fileName = "C:\\Test.xlsx";
    //Initialize Excel instance
    xlsApplication           = SysExcelApplication::construct();
    //Open Excel document
    //xlsApplication.visible(true);
    //Create Excel WorkBook and WorkSheet
    xlsWorkBookCollection    = xlsApplication.workbooks();
    xlsWorkBook              = xlsWorkBookCollection.add();
    xlsWorkSheetCollection   = xlsWorkBook.worksheets();
    xlsWorkSheet             = xlsWorkSheetCollection.itemFromNum(1);
    //Excel columns captions
    xlsWorkSheet.cells().item(row,1).value("Journal Num");
    xlsWorkSheet.cells().item(row,2).value("Item1");
    xlsWorkSheet.cells().item(row,3).value("Item2");
    xlsWorkSheet.cells().item(row,4).value("Item3");
    xlsWorkSheet.cells().item(row,5).value("Item4");
    //just caption not that important
    row  ;
    //Fill Excel with customTable JournalNum and item fields (only 20 records)
    while select customTable
    {
        if(row == 20)
        break;
        xlsWorkSheet.cells().item(row,1).value(customTable.JournalNum);
        

        paramAsCon = str2con(paramAsStr, ",");
    
        for (i=1;i<=conLen(paramAsCon);i  )
        {
            //info(conPeek(paramAsCon, i));    
            xlsWorkSheet.cells().item(row,1 i).value(conPeek(paramAsCon, i));
        }
        row  ;
    }
    //Check whether the document already exists
    if(WinApi::fileExists(fileName))
        WinApi::deleteFile(fileName);
    //Save Excel document
    xlsWorkbook.saveAs(fileName);
    //Open Excel document
    xlsApplication.visible(true);
    //Close Excel
    //xlsApplication.quit();
    //xlsApplication.finalize();
}

  • Suggested answer
    Ludwig Reinhard Profile Picture
    Ludwig Reinhard Microsoft Employee on at
    RE: Split values on export to Excel

    Hi NDingankar22,

    Wouldn't it be easier to export the data as they are and then use an Excel macro to format the columns and rows as you need it?

    Best regards,

    Ludwig

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

News and Announcements

Join experts in person to explore low code and AI agents at the Power Platform Community Conference sponsored by Microsoft

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Abhilash Warrier – Community Spotlight

We are honored to recognize Abhilash Warrier as our Community Spotlight honoree for…

Congratulations to the September Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

#1
CA Neeraj Kumar Profile Picture

CA Neeraj Kumar 2,122

#2
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 918 Super User 2025 Season 2

#3
Sohaib Cheema Profile Picture

Sohaib Cheema 646 User Group Leader

Last 30 days Overall leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans