Importing Excel using Excel Buffer + Time Value Changes

(0) Share

Report

Posted on by Rajasekhar@MS

5,569

Hi All

I am importing the data using Excel Buffer Read sheet Function, The data imported everything as it should except the Time Values, for example in the sheet Time value is 06:14:10 AM, the excel buffer cell value it is 11:44:10 having difference of around 6 hours, any ideas?

Thanks

*This post is locked for comments

I have the same question (0)

All responses (8)

Answers (1)

Suggested answer

Mohana Yadav 61,005 Super User 2025 Season 2 on at

Like (0)

Report

Did you check whether it may be because of Time Zones?

Was this reply helpful? Yes No
Rajasekhar@MS 5,569 on at

Like (0)

Report

Hi Mohana

Thanks for your quick response, yes it's because of time Zone, but if we want to import the Time Value given in the Excel irrespective of Time Zone, any thoughts?

Thanks

Was this reply helpful? Yes No
Suggested answer

Saurabh Shah 4,560 on at

Like (0)

Report

Hi,

This is due to the time zone difference between SQL server and Application server. Please make sure that you have correct time zone specified in NAV service as well as on client machine from where you are importing the files.

You can follow below link to understand the timezones in NAV 2013.

msdn.microsoft.com/.../dd983801.aspx

Regards,

Saurabh Shah

Was this reply helpful? Yes No
Verified answer

Jatin Patel 2,641 on at

Like (0)

Report

Hi

It Was resolved in Cumulative Update Roll up 9, Please see the below for the changes

+------------------------------------------------------------------------------+

| OBJECT Table 370 Excel Buffer |

+------------------------------------------------------------------------------+

---------- Before (BEFORE) ---------- Table 370 ---------- Function ParseCellValue

LOCAL PROCEDURE ParseCellValue@40(Value@1000 : Text;FormatString@1001 : Text);

VAR

DateTime@1002 : DateTime;

Decimal@1004 : Decimal;

BEGIN

---------- After (AFTER) ------------------------------------------------------

LOCAL PROCEDURE ParseCellValue@40(Value@1000 : Text;FormatString@1001 : Text);

VAR

DotNetDateTime@1006 : DotNet "'mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.DateTime";

DateTime@1002 : DateTime;

Decimal@1004 : Decimal;

BEGIN

--------------------------------------------------------------------------------

---------- Before (BEFORE) ---------- Table 370 ---------- Function ParseCellValue

// Excel Date is stored in OATime format

// Decimal integral component is number of days since 30-12-1899

// See: msdn.microsoft.com/.../system.datetime.tooadate.aspx

// FromOADate can't be used because it is utc dependent

DateTime := CREATEDATETIME(DMY2DATE(30,12,1899) + ROUND(Decimal),0T);

"Cell Type" := "Cell Type"::Date;

"Cell Value as Text" := FORMAT(DT2DATE(DateTime));

EXIT;

---------- After (AFTER) ------------------------------------------------------

// Excel Date is stored in OATime format

// Decimal integral component is number of days since 30-12-1899

// See: msdn.microsoft.com/.../system.datetime.tooadate.aspx

DotNetDateTime := DotNetDateTime.FromOADate(Decimal);

// Convert it as it was parsed in UTC and not localtime as we would expect.

DateTime := DotNetDateTime.ToLocalTime();

"Cell Type" := "Cell Type"::Date;

"Cell Value as Text" := FORMAT(DT2DATE(DateTime));

EXIT;

--------------------------------------------------------------------------------

Regards

Jatin Patel

Was this reply helpful? Yes No
Julie Bergholdt 15 on at

Like (0)

Report

Hello,

I have a situation where the excel file is importing 12/31/2014, but ends up in NAV as 12/30/2014. I don't understand why.

DotNetDateTime := DotNetDateTime.FromOADate(Decimal); The value of DotNetDateTime is 12/31/2014

// Convert it as it was parsed in UTC and not localtime as we would expect.

DateTime := DotNetDateTime.ToLocalTime(); the value of DateTime at this point is 12/30/2014

"Cell Type" := "Cell Type"::Date;

"Cell Value as Text" := FORMAT(DT2DATE(DateTime));

EXIT;

Am I suppose to comment out the standard code? It seems like it should be a setting that needs to be changed. Help?

Was this reply helpful? Yes No
Suggested answer

Rajasekhar@MS 5,569 on at

Like (0)

Report

Hi

This was resolved in CU 9 , apply the cu9 changes to the excel buffer table

for this you need to create the below function

ConvertDateTimeDecimalToStringInLocalTime(DateTimeAsOADate : Decimal) : Text[250]

DotNetDateTime := DotNetDateTime.FromOADate(DateTimeAsOADate);

DotNetDateTimeWithKind := DotNetDateTime.DateTime(DotNetDateTime.Ticks,DotNetDateTimeKind.Local);

DateTime := DotNetDateTimeWithKind;

EXIT(FORMAT(DT2TIME(DateTime)));

with the below variables

Name DataType Subtype Length

DotNetDateTime DotNet System.DateTime.'mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'

DotNetDateTimeWithKind DotNet System.DateTime.'mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'

DotNetDateTimeKind DotNet System.DateTimeKind.'mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'

DateTime DateTime

and in the ParseCellValue apply the below change

NumberFormat := COPYSTR(FormatString,1,30);

IF FormatString = '@' THEN BEGIN

"Cell Type" := "Cell Type"::Text;

"Cell Value as Text" := Value;

EXIT;

END;

EVALUATE(Decimal,Value);

IF STRPOS(FormatString,':') <> 0 THEN BEGIN

// Excel Time is stored in OADate format

"Cell Type" := "Cell Type"::Time;

"Cell Value as Text" := ConvertDateTimeDecimalToStringInLocalTime(Decimal);

EXIT;

END;

Regards

Rajasekhar.Y

Was this reply helpful? Yes No
Julie Bergholdt 15 on at

Like (0)

Report

Thank you for responding. I discovered the server is set at UTC -5. My service was set to UTC. I changed it to "Server Time Zone". I think the issue is that my service didn't recognize the -5. I will try your suggestion. Do you think my service should be set differently?

Was this reply helpful? Yes No
Rajasekhar@MS 5,569 on at

Like (0)

Report

Hi Julie

No, it's not depends on the server Time Zone, it has to depends on Clients Time zone(where users will import excel), so modify the excel buffer with the above code or with the latest rollup and try to import the excel

Regards

Rajasekhar.Y

Was this reply helpful? Yes No