Skip to main content

Notifications

Announcements

No record found.

Finance | Project Operations, Human Resources, ...
Suggested answer

Data Import from Excel to dynamics through X++ code

(0) ShareShare
ReportReport
Posted on by 53

Hi 

I am trying to import data from excel to dynamics FO through X code. I have been able to import various fields but facing issue in importing date field from excel to dynamics. I have put some infolog to see if the date fields is imported through code but it is not visible while other fields are fetched successfully. 

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

class RP_TimesheetImportClass extends RunBase
{
        public static void main(Args _args)
    {
        System.IO.Stream        stream;
        ExcelSpreadsheetName    sheeet;
        FileUploadBuild         fileUpload;
        DialogGroup             dlgUploadGroup;
        FileUploadBuild         fileUploadBuild;
        FormBuildControl        formBuildControl;

        TSTIMESHEETTABLE        timesheetTable;
        TSTIMESHEETLINE         timesheetLine;
        TSTIMESHEETLINEWEEK     timesheetLineWeek;

        ResourceView            resourceView;
        ProjPeriodId            periodId;


        //TrvExpTrans             expTrans, expTransLoc;
        //TrvExpTable             trvExpTable, trvExpTableLoc;
        //LedgerParameters        ledgerParameters;
        str                     timesheetNumber, resourceId, resourceName, resourceLegalEntity,projectLegalEntity, projectId, projectName, categoryId, approvalStatus;
        TransDate               startDate, endDate;
        str                     totalHrs, day1, day2, day3, day4, day5, day6, day7;
        
        
        
        int                     k, identifierValue, counterIdentifierValue;
       
        boolean                 isInterCompanyProject;
        RecId                   tstimesheettable;
        str                     purpose, legalEntityValue, projIDValue, employeeId;
        Str1260                 tripFromAndToDateValue, expenseCategory;
        TransDate               expSubmissionDate, invoiceReceiptDate;



        //str                     custAccount;
        //str                     employee;
        //str                     employeeWorkLocation;
        //str                     horizontal;
        //str                     incedoOfficeLocation;
        //str                     project, expnum;
        //str                     subHorizontal;
        //str                     usSalaryComponent;
        //str                     vendor;
        
        //Amount                  expAmount, exchangeRate;
        //str                     currencyCode, billableStatus;
        //

        HcmWorker               hcmWorker;
        HcmEmployment           hcmEmployment;
        ProjTable               projTable;
        RecId                   currentLegalEntity;
        DataAreaId              compDataAreaId;

        //str startDateStr, endDateStr;

       // TransDate startDate, endDate;
        

        Dialog                  dialog = new Dialog('Import the data from Excel');

        dlgUploadGroup          = dialog.addGroup('@SYS54759');
        formBuildControl        = dialog.formBuildDesign().control(dlgUploadGroup.name());
        fileUploadBuild         = formBuildControl.addControlEx(classstr(FileUpload), 'Upload');

        fileUploadBuild.style(FileUploadStyle::MinimalWithFilename);

        fileUploadBuild.fileTypesAccepted('.xlsx');

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

            if (fileUploadResult != null && fileUploadResult.getUploadStatus())
            {
                stream = fileUploadResult.openResult();
                using (ExcelPackage Package = new ExcelPackage(stream))
                {
                    int                         rowCount, i;

                    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;


                    for (i = 2; i<= rowCount; i  )
                    {
                        try
                        {
                            k  ;
                            timesheetNumber         = any2Str(range.get_Item(i, 1).value);

                            resourceId              = any2Str(range.get_Item(i, 2).value);
                           
                            resourceLegalEntity     = any2Str(range.get_Item(i, 3).value);
                            projectLegalEntity     = any2Str(range.get_Item(i, 4).value);
                            projectId               = any2Str(range.get_Item(i, 5).value);
                           
                            categoryId              = any2Str(range.get_Item(i, 6).value);
                            periodId                = any2Str(range.get_Item(i, 7).value);
                            
                            startDate            = str2Date(range.get_Item(i,8).value,213);
                            endDate            = str2Date(range.get_Item(i,9).value,213);
                            //startDate               = str2Date(startDateStr, 213);
                            //endDate                 = str2Date(endDateStr, 213);
                            
                            
                             



                            info(strFmt("Period ID : %1",periodId));
                           // info(strFmt("Start Date str : %1, End Date str : %2",startDateStr, endDateStr));
                            info(strFmt("Start Date : %1, End Date : %2",startDate, endDate));
                        
                            day1                    = any2Str(range.get_Item(i, 10).value);
                            day2                    = any2Str(range.get_Item(i, 11).value);
                            day3                    = any2Str(range.get_Item(i, 12).value);
                            day4                    = any2Str(range.get_Item(i, 13).value);
                            day5                    = any2Str(range.get_Item(i, 14).value);
                            day6                    = any2Str(range.get_Item(i, 15).value);
                            day7                    = any2Str(range.get_Item(i, 16).value);
                            approvalStatus          = any2Str(range.get_Item(i, 17).value);
                            Info(strFmt("Approval Status %1", approvalStatus));
                            

                            compDataAreaId          = CompanyInfo::find().DataArea;
                            //currentLegalEntity      = CompanyInfo::find().RecId;

                            //ledgerParameters = LedgerParameters::find();

                            select  RecId from resourceView
                                where resourceView.ResourceId == resourceId;

                            


                            //if(timesheetTable)
                            //{
                               // ttsbegin;
                                timesheetTable.clear();
                                timesheetLine.clear();
                                timesheetLineWeek.clear();
                            if(approvalStatus == "Approved")  timesheetTable.ApprovalStatus = TSAppStatus::Approved;
                            else if(approvalStatus == "Draft")  timesheetTable.ApprovalStatus = TSAppStatus::Create;
                            else if(approvalStatus == "In Review")  timesheetTable.ApprovalStatus = TSAppStatus::Pending;

                            //else if(approvalStatus == "Draft")  timesheetTable.ApprovalStatus = TSAppStatus::;

                                
                                //timesheetTable.ApprovalStatus   =str2Enum(ApprovalStatus,approvalStatus);
                                timesheetTable.TimesheetNbr     =timesheetNumber;
                                timesheetTable.Resource = resourceView.RecId; 
                                //timesheetTable.Resource         =name;
                                timesheetTable.ProjPeriodId = periodId;
                                timesheetTable.PeriodFrom       =startDate;
                                timesheetTable.PeriodTo         =endDate;
                                info(strFmt("Period ID : %1",timesheetTable.ProjPeriodId));
                                Info(strFmt("Start Date: %1, End Date : %2",timesheetTable.PeriodFrom, timesheetTable.PeriodTo));

                                //timesheetTable.D =resourceLegalEntity;

                                timesheetTable.insert();
                                Info("Inserted successfully to timesheettable");

                                //ttscommit;
                          // }
                            //if(timesheetLine)
                           // {
                                //ttsbegin;
                                timesheetLine.TimesheetNbr      =timesheetNumber;
                                timesheetLine.ProjId            =projectId;
                                timesheetLine.ProjectDataAreaId =projectLegalEntity;
                                timesheetLine.CategoryId        =categoryId;
                                timesheetLine.Resource          =resourceView.RecId; 
                                //timesheetLine.ApprovalStatus    =str2Enum(ApprovalStatus,approvalStatus);
                            if(approvalStatus == "Approved")  timesheetTable.ApprovalStatus = TSAppStatus::Approved;
                            else if(approvalStatus == "Draft")  timesheetTable.ApprovalStatus = TSAppStatus::Create;
                            else if(approvalStatus == "In Review")  timesheetTable.ApprovalStatus = TSAppStatus::Pending;
                                timesheetLine.insert();
                                Info("Inserted successfully to timesheetLine");
                               // ttscommit;
                               select RecId from timesheetLine where timesheetLine.TimesheetNbr==timesheetNumber;
                                
                            //}
                            //if(timesheetLineWeek)
                           // {
                                //ttsbegin;
                                timesheetLineWeek.TimesheetNbr  =timesheetNumber;
                                timesheetLineWeek.DayFrom       =startDate;
                                timesheetLineWeek.DayTo         =endDate;
                                timesheetLineWeek.Hours[1]         =any2Real(day1);
                                timesheetLineWeek.Hours[2]         =any2Real(day2);
                                timesheetLineWeek.Hours[3]         =any2Real(day3);
                                timesheetLineWeek.Hours[4]         =any2Real(day4);
                                timesheetLineWeek.Hours[5]         =any2Real(day5);
                                timesheetLineWeek.Hours[6]         =any2Real(day6);
                                timesheetLineWeek.Hours[7]         =any2Real(day7);
                                timesheetLineWeek.TSTimesheetLine = timesheetLine.RecId;
                            
                                timesheetLineWeek.insert();
                                Info("Inserted successfully to timesheetLineWeek");
                               // ttscommit;


                            //}

                        }
                            catch(Exception::Error)
                            {
                                info(strFmt("The line no. %1 has been skipped due to an error", i));
                            //System.Exception ex = Exception::Error;
                                //error(ex.Message);
                                //Info(strFmt("Error %1",
                                continue;
                            }
                        }


                    }
                }
            }
        }

}

8625.Capture.PNG

These are the columns of Excel file which I want to import.

  • GirishS Profile Picture
    GirishS 27,821 Super User 2024 Season 1 on at
    RE: Data Import from Excel to dynamics through X++ code

    Please mark the helpful answers as verified, so that others can make use of this thread in the future.

    Its done by clicking "Did this answer your question" on the particular reply.

    Thanks,

    Girish S.

  • rsingh62 Profile Picture
    rsingh62 53 on at
    RE: Data Import from Excel to dynamics through X++ code

    It works. Thank you very much Girish and Komi.

  • GirishS Profile Picture
    GirishS 27,821 Super User 2024 Season 1 on at
    RE: Data Import from Excel to dynamics through X++ code

    Komi asking you to set the column format type on the excel to "General" for date field.

    Refer to the below image.

    11404.Capture.PNG

    Thanks,

    Girish S.

  • rsingh62 Profile Picture
    rsingh62 53 on at
    RE: Data Import from Excel to dynamics through X++ code

    Testing-_2800_1_2900_.xlsx

    This is the excel file. Could you please elaborate the steps u have mentioned. 

  • Suggested answer
    Komi Siabi Profile Picture
    Komi Siabi 12,759 Most Valuable Professional on at
    RE: Data Import from Excel to dynamics through X++ code

    For the date to import successfully, you need to pick them as string then convert from string to date just like you are already doing with str2Date,

    So, the field type should be General, and not Date

  • Suggested answer
    GirishS Profile Picture
    GirishS 27,821 Super User 2024 Season 1 on at
    RE: Data Import from Excel to dynamics through X++ code

    Hi,

    Your excel input seems to be wrong. You need to have a separate date column in the excel with the hours field so that you can identify that this hour is against this date.

    Later you can use getDayOffset method to get the array elements number that the hours belong to based on the given date.

    ProjPeriodTable periodTable;
    
    periodTable = ProjPeriodTable::findByPeriodId(any2Str(range.get_Item(i, 7).value));
    //you need to pass the week start and date from the excel to the day offset
    dayOffset = TSPeriods::getDayOffset(periodTable.WeekStart, (range.get_Item(i, 12).value));
    timesheetLineWeek.Hours[dayOffset] = any2Real(day1);
    

    Thanks,

    Girish S.

  • rsingh62 Profile Picture
    rsingh62 53 on at
    RE: Data Import from Excel to dynamics through X++ code

    5807.MicrosoftTeams_2D00_image-_2800_2_2900_.png

    It is of type Date.

  • Komi Siabi Profile Picture
    Komi Siabi 12,759 Most Valuable Professional on at
    RE: Data Import from Excel to dynamics through X++ code

    Hello,

    Please what is the field type for Period start date & endate?

    Is it General, Date, Number, Text?

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

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Vahid Ghafarpour – Community Spotlight

We are excited to recognize Vahid Ghafarpour as our February 2025 Community…

Congratulations to the January Top 10 leaders!

Check out the January community rock stars...

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 292,162 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 230,962 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Product updates

Dynamics 365 release plans