Excel Buffer Import Date Issue

cnicola

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?

Reinhard

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

cnicola

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? :?

StLi

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

cnicola

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.

Reinhard

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 )

npaltice

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;

Reinhard

Nice! Yeah this is just what I was thinking of.

Qiang

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;

sVerme

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.

bbrown

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();

Bilbo007

TempExcelBuffer.SetReadDateTimeInUtcDate(true);
worked for me in BC SaaS

MichelDant77

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.