Excel Buffer Import Date Issue

cnicolacnicola Member Posts: 181
edited 2014-08-11 in NAV Three Tier
Hi,

I am using the Excel Buffer functions to import data in NAV from an Excel Spreadsheet. The functions are ReadSheet and ParseCellValue.
The issue I am finding is that it reads the date column and somehow it always ends up 1 day earlier than the actual date. So if I have in Excel 05/08/2012 it will bring in 05/07/2012. I was using 2013 but just installed a standard 2013 R2 and I see the same issue there.
The Enumerator from ReadSheet seems to read the date value as that integer weird value (in this example 41037). ParseCellValue seems to take that value (which when put in Excel and then formatted to date does give me 05/08/12) and then uses a .NET control (System.DateTime.'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089') to convert that into a DateTime. At that point it seems it becomes 05/07/12 08 PM (no idea how or why). ](*,)
So obviously that ends up being 05/07/12 instead of 05/08.
I have replicated this behavior on client's server, my laptop and on a brand new cloud VM with 2013 R2.
Has anyone else seen this problem?
Apathy is on the rise but nobody seems to care.

Comments

  • ReinhardReinhard Member Posts: 249
    Hey there,
    Got a theory for why it's happening.
    Seems like .net treats dates as date times.
    Next, it does something to handle the time zone. since the "raw date" is at midnight, any adjustment for the time zone will set it back to the previous day.
    - Reinhard
  • cnicolacnicola Member Posts: 181
    Reinhard wrote:
    Hey there,
    Got a theory for why it's happening.
    Seems like .net treats dates as date times.
    Next, it does something to handle the time zone. since the "raw date" is at midnight, any adjustment for the time zone will set it back to the previous day.
    - Reinhard

    It does make a lot of sense. But this is pure standard NAV code ... Am I really the first to use this function with dates? Or am I the only one on the "wrong" timezone? :?
    Apathy is on the rise but nobody seems to care.
  • StLiStLi Member Posts: 83
    It's just a hunch but: do you have the same problem with other excel files? some which you've created your own? because i could imagine there is something going on in the cell format of the excel you're not aware of at the moment... also: any clue how it gets to "05/07/12 08 PM" maybe the excelbuffer works with an obsolete automation which has issues with some new date formats. :-k
  • cnicolacnicola Member Posts: 181
    StLi wrote:
    It's just a hunch but: do you have the same problem with other excel files? some which you've created your own? because i could imagine there is something going on in the cell format of the excel you're not aware of at the moment... also: any clue how it gets to "05/07/12 08 PM" maybe the excelbuffer works with an obsolete automation which has issues with some new date formats. :-k

    I used a clients file as well as one I created myself from scratch. So I don't think it is something in the spreadsheet.
    As for the obsolete automation, this is a similar comment as Reinhard. In my opinion it probably is a bug but it is the same bug in 2013 R2 as well so I would have thought someone would have noticed this by now.
    Apathy is on the rise but nobody seems to care.
  • ReinhardReinhard Member Posts: 249
    I've had a similar problem in other contexts in NAV before. It is pretty annoying.
    In those instances it was myself using a .net control, so I wasn't "blaming" NAV. But since this is part of the standard NAV code now we can blame MSFT :)
    In general I have been avoiding using the excel buffer since it was slow, although that seems to be much better now...

    One way you can resolve it is by adding 8 or 9 hours back to the date time in the function. Or there's probably a generic way to fix it based on the current time zone settings, but that's one work-around.

    But, ultimately, ideally something somewhere would be changed where .net recognizes that NAV is passing "just" a date to which time zone rules do not apply. It's no good to have dates be changing unpredictably during standard operations... (if my theory is correct :wink: )
  • npalticenpaltice Member Posts: 3
    The following code in the ParseCellValue() function in table 370 Excel Buffer causes the problem.
    // Excel Date is stored in OATime format
    DateTime := DateTimeHelper.FromOADate(Decimal);
    "Cell Type" := "Cell Type"::Date;
    "Cell Value as Text" := FORMAT(DT2DATE(DateTime));
    
    Replace it with the following code. DateString is a Text30 variable.
    // Excel Date is stored in OATime format
    DateTimeHelper := DateTimeHelper.FromOADate(Decimal);
    DateString := DateTimeHelper.ToShortDateString;
    EVALUATE("Cell Value as Text",DateString);
    "Cell Type" := "Cell Type"::Date;
    
  • ReinhardReinhard Member Posts: 249
    Nice! Yeah this is just what I was thinking of.
  • QiangQiang Member Posts: 1
    edited 2022-05-20
    In Business Central, Still got this Error. Managed to fix it by subscribing an event. here is the code:
    [EventSubscriber(ObjectType::Table, database::"Excel Buffer", 'OnBeforeParseCellValue', '', true, true)]
        local procedure OnBeforeParseCellValue(var ExcelBuffer: Record "Excel Buffer"; var Value: Text; var FormatString: Text; var IsHandled: Boolean)
        var
            DateValue: Integer;
            DateDuration: Integer;
            WorkDate: Date;
        begin
            if ((StrPos(FormatString, 'y') <> 0) or
                (StrPos(FormatString, 'm') <> 0) or
                (StrPos(FormatString, 'd') <> 0)) and
               (StrPos(FormatString, 'Red') = 0)
            then begin
                // Convert value from text to integer, the value is days after 1900.1.1
                Evaluate(DateValue, Value);
                // 43831 is the difference between 1900,1,1 to 2020.1.1
                DateDuration := DateValue - 43831;
                WorkDate := CalcDate(StrSubstNo(DayUnit, DateDuration), DMY2Date(1, 1, 2020));
                ExcelBuffer."Cell Type" := ExcelBuffer."Cell Type"::Date;
                ExcelBuffer."Cell Value as Text" := FORMAT(WorkDate);
                IsHandled := true;
                exit;
            end;
        end;
    
  • sVermesVerme Member Posts: 2
    Qiang wrote: »
    In Business Central, Still got this Error. Managed to fix it by subscribing an event. here is the code:
    [EventSubscriber(ObjectType::Table, database::"Excel Buffer", 'OnBeforeParseCellValue', '', true, true)]
        local procedure OnBeforeParseCellValue(var ExcelBuffer: Record "Excel Buffer"; var Value: Text; var FormatString: Text; var IsHandled: Boolean)
        var
            DateValue: Integer;
            DateDuration: Integer;
            WorkDate: Date;
        begin
            if ((StrPos(FormatString, 'y') <> 0) or
                (StrPos(FormatString, 'm') <> 0) or
                (StrPos(FormatString, 'd') <> 0)) and
               (StrPos(FormatString, 'Red') = 0)
            then begin
                // Convert value from text to integer, the value is days after 1900.1.1
                Evaluate(DateValue, Value);
                // 43831 is the difference between 1900,1,1 to 2020.1.1
                DateDuration := DateValue - 43831;
                WorkDate := CalcDate(StrSubstNo([b]DayUnit[/b], DateDuration), DMY2Date(1, 1, 2020));
                ExcelBuffer."Cell Type" := ExcelBuffer."Cell Type"::Date;
                ExcelBuffer."Cell Value as Text" := FORMAT(WorkDate);
                IsHandled := true;
                exit;
            end;
        end;
    

    Hi,

    Facing the same problem in a BC18 and wanted to try this solution.
    So I created this subscriber and added the above code. just not sure where the DayUnit comes from, or what it is.
  • bbrownbbrown Member Posts: 3,268
    edited 2022-10-18
    Call this function on the Excel buffer - ExcelBuffer.SetReadDateTimeInUtcDate(true); That treats the values as literal and does not do the timezone adjustments

    TempExcelBuffer.OpenBookStream(IStream, SheetName);
    TempExcelBuffer.SetReadDateTimeInUtcDate(true);
    TempExcelBuffer.ReadSheet();
    There are no bugs - only undocumented features.
  • Bilbo007Bilbo007 Member Posts: 6
    TempExcelBuffer.SetReadDateTimeInUtcDate(true);
    worked for me in BC SaaS
  • MichelDant77MichelDant77 Member Posts: 4
    bbrown wrote: »
    Call this function on the Excel buffer - ExcelBuffer.SetReadDateTimeInUtcDate(true); That treats the values as literal and does not do the timezone adjustments

    TempExcelBuffer.OpenBookStream(IStream, SheetName);
    TempExcelBuffer.SetReadDateTimeInUtcDate(true);
    TempExcelBuffer.ReadSheet();
    bbrown wrote: »
    Call this function on the Excel buffer - ExcelBuffer.SetReadDateTimeInUtcDate(true); That treats the values as literal and does not do the timezone adjustments

    TempExcelBuffer.OpenBookStream(IStream, SheetName);
    TempExcelBuffer.SetReadDateTimeInUtcDate(true);
    TempExcelBuffer.ReadSheet();

    This is correct, if it works.
    Thanks.
Sign In or Register to comment.