D365F&O, Developer

Dynamics 365 FO: Import data from Excel using X++

Scenario: 

Sometimes consultants don’t want to use the Data Management in D365 due to a lack of data structure knowledge, a short timeline, or many table relations.  They want to import from Excel to all related tables in one shot using x++.

Solution: 

Prepare a batch class for importing master data.  I normally use this template and just change the part of create() method for each data.  

using System.IO;
using OfficeOpenXml;
using OfficeOpenXml.ExcelPackage;
using OfficeOpenXml.ExcelRange;

class AeyImportTemplate extends RunBaseBatch
{
    Filename        ItemFileName;
    Filename        filename;
    DialogField     dialogFilename;

    System.IO.Stream    stream;
    FileUploadTemporaryStorageResult fileUploadResult;
    OfficeOpenXml.ExcelRange    range;
    CommaTextStreamIo   commaIo;
    RecordInsertList    rsl;
    boolean             newIteration;
    int                 line,
                        lineimported,lineerror, lineskipped;
    Counter             loop;
    FileUploadBuild     dialogFileUpload;
    FileUpload          fileUpload;
    DialogRunbase       dialog;



    #define.CurrentVersion(1)
    #define.Version1(1)
    #localmacro.CurrentList
        fileName
    #endmacro

    void create(int row)
    {
        str                         itemId;
        price                       inventPrice;

        InventTableModule           inventTableModule;

        itemId = range.get_Item(row, 1).Value;
        inventPrice = range.get_Item(row, 2).Value;

        try
        {
            ttsbegin;
            select forUpdate inventTableModule
                where inventTableModule.ItemId  == ItemId
                && inventTableModule.ModuleType == ModuleInventPurchSales::Invent;

            if (!inventTableModule)
            {
                inventTableModule.clear();
            }
            inventTableModule.Price             = inventPrice;
            if (!inventTableModule)
            {
                inventTableModule.insert();
            }
            else
            {
                inventTableModule.update();
            }
            lineimported ++;
            ttscommit;
        }
        catch
        {
            lineerror ++;
            ttsAbort;
        }
    }

    void import()
    {
        Container               con;
        InventTable             inventTable;
        
        ;
        setPrefix("Import master data");
        
        if (this.openFile())
        {
            using (ExcelPackage Package = new ExcelPackage(stream))
            {
                int                         rowCount, i, endRow, startRow;
                Package.Load(stream);
                ExcelWorksheet  worksheet   = package.get_Workbook().get_Worksheets().get_Item(1);
                range       = worksheet.Cells;
                endRow      = worksheet.Dimension.End.Row;
                startRow    = worksheet.Dimension.Start.Row;
                rowCount    = endRow - startRow + 1;

                try
                {
                    for (i = 2; i<= rowCount; i++)
                    {
                        setPrefix(strFmt("Line %1", i));
                        this.create(i);
                    }
                }
                catch
                {
                    throw error("Error - Import template");
                }
            }
        }

        info(strfmt("%1 record(s) imported", lineimported));
        info(strfmt("%1 record(s) fail", lineerror));
    }

    boolean openFile()
    {
        boolean ret = false;

        if (fileUploadResult != null && fileUploadResult.getUploadStatus())
        {
            stream = fileUploadResult.openResult();
            ret = true;
        }
        else
        {
            throw error(strfmt("@SYS62207",filename));
        }

        return ret;
    }

    public Object dialog()
    {
        dialog = super();
        
        DialogGroup dialogGroup = dialog.addGroup('Upload file');

        FormBuildControl formBuildControl = dialog.formBuildDesign().control(dialogGroup.name());
        dialogFileUpload = formBuildControl.addControlEx(classstr(FileUpload), 'FileUpload');
        dialogFileUpload.style(FileUploadStyle::MinimalWithFilename);
        dialogFileUpload.fileNameLabel("@SYS308842");
        dialogFileUpload.fileTypesAccepted(".xlsx");
        
        return dialog;
    }

    /// <summary>
    /// Disables the dialog Ok button until the file upload is complete.
    /// </summary>
    /// <param name="_dialog">The <c>Runbase</c> dialog object.</param>
    public void dialogPostRun(DialogRunbase _dialog)
    {
        fileUpload = _dialog.formRun().control(_dialog.formRun().controlId('FileUpload'));
        fileUpload.notifyUploadCompleted += eventhandler(this.uploadCompleted);
        this.setDialogOkButtonEnabled(_dialog, false);
    }

    /// <summary>
    /// After the file has been uploaded, the Ok button is enabled.
    /// </summary>
    public void uploadCompleted()
    {
        filename = fileUpload.fileName();

        this.setDialogOkButtonEnabled(dialog, true);
        
        fileUploadResult = fileUpload.getFileUploadResult();

        fileUpload.notifyUploadCompleted -= eventhandler(this.UploadCompleted);
    }

    /// <summary>
    /// Enables or disables the dialog Ok button.
    /// </summary>
    /// <param name = "_dialog">The <c>Runbase</c> dialog object.</param>
    /// <param name = "_isEnabled">Indicates to enable or disable the Ok button.</param>
    protected void setDialogOkButtonEnabled(DialogRunbase _dialog, boolean _isEnabled)
    {
        FormControl okButtonControl = _dialog.formRun().control(_dialog.formRun().controlId('OkButton'));

        if (okButtonControl)
        {
            okButtonControl.enabled(_isEnabled);
        }
    }

    public container pack()
    {
        return [#CurrentVersion,#CurrentList];
    }

    public boolean unpack(container packedClass)
    {
        Version version = runbase::getVersion(packedClass);
        ;
        switch (version)
        {
            case #CurrentVersion:
                [version,#CurrentList] = packedClass;
                break;
            default:
                return false;
        }
        return true;
    }

    public void run()
    {
        this.import();
        super();
    }

    static void main(Args _args)
    {
        AeyImportTemplate   import;
        FormRun formRun;
        Args    args;


        ;
        import = new AeyImportTemplate();
        if (import.prompt())
        {
            import.run();
        }
    }

    public ClassDescription caption()
    {
        return 'Import template';
    }

}

Hope this post has helped you…

Please follow and share our blog.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s