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

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / How to Import Data fro...
Finance | Project Operations, Human Resources, ...
Answered

How to Import Data from Excel Using X++ Code in D365FO

(1) ShareShare
ReportReport
Posted on by CU09091357-0 501
I'm creating a button in a form that when clicked, will open a dialogue.
The user will upload an excel, and when clicking ok, the records in the excel will be inserted in the form table.
 
I saw many codes and references, but none of them worked for me, as I don't understand how the mapping will be done when uploading an excel file.
Table1(field1, field2) for example are like field1, field2 columns in excel
 
Can someone help
Categories:
Dynamics 365 Finance
Microsoft Dynamics AX
I have the same question (0)
  • Verified answer
    André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 301,132 Super User 2025 Season 2 on at
    Hi,
     
    In case the primary data source of the form is supported with a public data entity, users would be able to use the Open in Excel experience to prepare data in Excel and publish the data. Using that option, there is no need to use custom X++ coding to read an Excel file. 
    You can also consider adding a button which will then start a Data import job as available in the Data Management features.
     
    If you insist in developing something from scratch, then please elaborate on what examples you have seen, tried and where exactly you got stuck.
  • Verified answer
    Waed Ayyad Profile Picture
    Waed Ayyad 9,039 Super User 2025 Season 2 on at
    Hi,
     
    Can you give us more details about the issue? And can you share the code that you tried?
    In general, check the following link: Excel_D365Import
     
    You should add the header and the data that you want to import and, in the code, you will loop through the rows to get the values of all columns in each row and by getting the values then you can use it to import the data to your table.
     
     

    Thanks,

    Waed Ayyad

    If this helped, please mark it as "Verified" for others facing the same issue

     
     
  • Verified answer
    CU09091357-0 Profile Picture
    CU09091357-0 501 on at
    Waed Ayyad, André Arnaud de Cal... thank you for your replies.
     
    Technically, the open in excel is working fine but the request was a button to upload.
     
    Adding that the iteration loop in the excel file to retrieve data worked fine also after opening a dialog.
     
    This is my code:
     
    Using System.IO;
Using OfficeOpenXml;
Using OfficeOpenXml.ExcelPackage;
Using OfficeOpenXml.ExcelRange;
class Ex_ImportFromExcel
{
    public void run()
    {
        this.updateReadings();
    }
    void updateReadings()
    {
        System.IO.Stream     stream;
        ExcelSpreadsheetName sheeet;
        FileUploadBuild      fileUpload;
        DialogGroup          dlgUploadGroup;
        FileUploadBuild      fileUploadBuild;
        FormBuildControl     formBuildControl;
        Ex_ReadingsTable     readings, insertReadings, updateReadings;
        COMVariantType       type;
        Dialog               dialog =    new Dialog('Import Utility Readings'); 
        dlgUploadGroup   = dialog.addGroup('@SYS54759');
        formBuildControl = dialog.formBuildDesign().control(dlgUploadGroup.name());
        fileUploadBuild  = formBuildControl.addControlEx(classstr(FileUpload), 'Upload');
        fileUploadBuild.style(FileUploadStyle::MinimalWithFilename);
        fileUploadBuild.fileTypesAccepted('.xlsx'); 
        str COMVariant2Str(COMVariant _cv)
        {
            switch (_cv.variantType())
            {
                case COMVariantType::VT_BSTR:
                    return _cv.bStr();
                case COMVariantType::VT_EMPTY:
                    return '';
                default:
                    throw error(strfmt('@SYS26908', _cv.variantType()));
            }
        }

        if (dialog.run() && dialog.closedOk())
        {
            FileUpload fileUploadControl     = dialog.formRun().control(dialog.formRun().controlId('Upload'));
            FileUploadTemporaryStorageResult fileUploadResult = fileUploadControl.getFileUploadResult();

            if (fileUploadResult != null && fileUploadResult.getUploadStatus())
            {
                stream = fileUploadResult.openResult();
                using (ExcelPackage Package = new ExcelPackage(stream))
                {
                    int rowCount, i, columncount, j;
                    Package.Load(stream);
                    ExcelWorksheet worksheet = package.get_Workbook().get_Worksheets().get_Item(1);
                    OfficeOpenXml.ExcelRange range = worksheet.Cells;
                    rowCount = (worksheet.Dimension.End.Row) - (worksheet.Dimension.Start.Row)  + 1;
                    columncount = (worksheet.Dimension.End.Column);
                    for (i = 2; i <= rowCount; i++)
                    {
                        insertReadings.AccountNum        = (range.get_Item(i, 1).value);
                        insertReadings.UnitNum           = any2Str(range.get_Item(i, 2).value);
                        insertReadings.RentalOrder       = any2Str(range.get_Item(i, 3).value);
                        insertReadings.MeterNum          = any2Str(range.get_Item(i, 4).value);
                        insertReadings.company           = any2Str(range.get_Item(i, 5).value);
                        insertReadings.BrandName         = any2Str(range.get_Item(i, 6).value);
                        insertReadings.AdminFee          = any2Int(range.get_Item(i, 7).value);
                        insertReadings.AdminFeeID        = any2Str(range.get_Item(i, 8).value);
                        insertReadings.Consumption       = any2Real(range.get_Item(i, 9).value);
                        insertReadings.ConsumptionTwenty = any2Real(range.get_Item(i, 10).value);
                        insertReadings.CostPerUnit       = any2Real(range.get_Item(i, 11).value);
                        insertReadings.CurrentReading    = any2Real(range.get_Item(i, 12).value);
                        insertReadings.DateSaved         = str2Datetime((range.get_Item(i, 13).value), 213);
                        insertReadings.FromDate          = str2Datetime((range.get_Item(i, 14).value), 213);
                        insertReadings.PreviousReading   = any2Real(range.get_Item(i, 16).value);
                        insertReadings.ToDate            = str2Datetime((range.get_Item(i, 17).value), 213);
                        insertReadings.TotalCost         = any2Real(range.get_Item(i, 19).value);
                        insertReadings.insert();
                    }
                }
            }
            else
            {
                error('Error here');
            }
        }
    }
    public static void main(Args args)
    {
        Ex_ImportFromExcel importReadings;
        importReadings = new Ex_ImportFromExcel();
        importReadings.run();
    }
}
     

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

Responsible AI policies

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

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

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

#1
André Arnaud de Calavon Profile Picture

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

#2
Martin Dráb Profile Picture

Martin Dráb 429 Most Valuable Professional

#3
BillurSamdancioglu Profile Picture

BillurSamdancioglu 239 Most Valuable Professional

Last 30 days Overall leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans