is possible to import a Time field using excell buffer

gorvega79 · 2010-01-21

i want to import a time field from a excel file using excell buffer table. if in excel the time field value is 09:30:00, when i import the file, in the excell buffer table value is 0,40486111111111 i think that excell buffer transform the value to a text value. but i need the time...is possible do something.

thank you

Angelo · 2010-01-21

try to convert TIME to STRING before you insert in excel buffer table

VarTime := TIME;
ReturnTime := FORMAT(VarTime,0,'<Hours24,2>:<Minutes,2>:<Seconds,2>');
IF ReturnTime = '' THEN
ReturnTime := '00:00:00';

gorvega79 · 2010-01-22

one more question, i have done this:

rExcelBuffer.OpenBook(FileRoot,rExcelBuffer.SelectSheetsName(fileroot));
rExcelBuffer.ReadSheet();

i don´t know where i have to put the code, if i have to put before readsheet how can i change the field format. finally i think that i have to change the function readsheet and put to code in....any observation please

thank yo so much

Belias · 2010-01-22

in these cases, it's easier to put a single quotemark before the time value in your excel column...this will make the data 100% text, and you can import it in nav and do an evaluate (be also aware of your windows decimal and thousand separator)

gorvega79 · 2010-01-22

yes but the problem is that we´re trying to automatize all the processes and is necesary not touch the excel file...i will search. if someone find something please tell me

thank you so much ](*,) ](*,)

Belias · 2010-01-22

can't the supplier automatically put the quotemark?
(before asking him, try if it solves your problem with a test file)

superjet · 2010-01-22

if excel file supplier can't change excel file field type to Text before export, than you can solve this problem using ADO (haha, i know it's not a trivial way to do this, but if there is no alternative, it helps you \:D/ )

gorvega79 · 2010-01-25

thak you for everything but nothing works...oooooohhhhh

regards

SPost29 · 2010-01-25

Excell stores the time as a decimal part of a day you just need to convert this from the decimal to the time
If you use the below function in excel and enter you number you will get your time

TIMEVALUE function
Show All
Hide All
Returns the decimal number of the time represented by a text string. The decimal number is a value ranging from 0 (zero) to 0.99999999, representing the times from 0:00:00 (12:00:00 AM) to 23:59:59 (11:59:59 P.M.).

Hope this helps
Steve

Belias · 2010-01-26

SPost29 wrote:

Excell stores the time as a decimal part of a day you just need to convert this from the decimal to the time
If you use the below function in excel and enter you number you will get your time

TIMEVALUE function
Show All
Hide All
Returns the decimal number of the time represented by a text string. The decimal number is a value ranging from 0 (zero) to 0.99999999, representing the times from 0:00:00 (12:00:00 AM) to 23:59:59 (11:59:59 P.M.).

Hope this helps
Steve

:shock: really??thank you, i'll test it out! =D>

rothor · 2010-07-23

YES! It is!!!!!!!!

Call the function below as this example for test:

IF TrasformaOreExcelInTime(0.382638889,VariableTimeToReceive) THEN
MESSAGE('%1',VariableTimeToReceive);

Create a function like this:

TrasformaOreExcelInTime(ValoreDecimaleParm : Decimal;VAR ValoreTime : Time) : Boolean

IF (ValoreDecimaleParm < 0) OR (ValoreDecimaleParm > 1) THEN
EXIT(FALSE);

IF (ValoreDecimaleParm = 0) THEN BEGIN
ValoreTime := 0T;
RisultatoEvaluateLoc := TRUE;
END ELSE BEGIN
TotaleMillisecondiLoc := ROUND((86400000 * ValoreDecimaleParm),1,'=');

OreLoc := TotaleMillisecondiLoc DIV 3600000;
Intero2Loc := OreLoc * 3600000;
MillisecondiMinutiLoc := TotaleMillisecondiLoc - Intero2Loc;

MinutiLoc := MillisecondiMinutiLoc DIV 60000;
Intero3Loc := MinutiLoc * 60000;
MillisecondiSecondiLoc := MillisecondiMinutiLoc -Intero3Loc;

SecondiLoc := MillisecondiSecondiLoc DIV 1000;
InteroOreMinutiSecondiLoc := (OreLoc * 10000) + (MinutiLoc * 100) + SecondiLoc;
TestOreMinutiSecondiLoc := FORMAT(InteroOreMinutiSecondiLoc);

if InteroOreMinutiSecondiLoc < 99999 then begin
TestOreMinutiSecondiLoc := '0' + TestOreMinutiSecondiLoc;
end;

RisultatoEvaluateLoc := EVALUATE(ValoreTime,TestOreMinutiSecondiLoc);
END;
EXIT(RisultatoEvaluateLoc);

Declare Local Variable as:
Name DataType Subtype Length
TotaleMillisecondiLoc Integer
MillisecondiMinutiLoc Integer
OreLoc Integer
Intero2Loc Integer
MinutiLoc Integer
MillisecondiSecondiLoc Integer
Intero3Loc Integer
SecondiLoc Integer
InteroOreMinutiSecondiLoc Integer
TestOreMinutiSecondiLoc Text 30
RisultatoEvaluateLoc Boolean

Declare Parameters as:
Var Name DataType Subtype Length
No ValoreDecimaleParm Decimal
Sì ValoreTime Time

Dont forget the return value as boolean!

Sorry, all the variabels are in Italian. I was too lazy to translate!

Bye, bye.
=D> =D> =D> =D> =D> =D> =D> =D> =D>

Slawek_Guzek · 2010-07-23

gorvega79 wrote:

i want to import a time field from a excel file using excell buffer table. if in excel the time field value is 09:30:00, when i import the file, in the excell buffer table value is 0,40486111111111 i think that excell buffer transform the value to a text value. but i need the time...is possible do something.

thank you

Here you have example of function reading DATETIME from Excel Buffer table:

calcDateTime(DatTimText : Text[100];DatTimText2 : Text[100]) : DateTime
ts := '  ';
ts[1] := FORMAT(1000.0)[2];

EVALUATE(dat,DatTimText);

DatTimText2 := DELCHR(DatTimText2,'<=>',ts);
EVALUATE(dec,DatTimText2);
i := ROUND(dec,1,'<');
tim := 000000T;
tim := tim + ROUND((dec-i)*(1000*60*60*24),1);

dt := CREATEDATETIME(dat,tim);

EXIT(dt);

The function is taken from Universal Excel Importer from mibuso. In Codeunit99991 you will find the function and how to use it to read DATETIME from Excel Buffer table.

is possible to import a Time field using excell buffer

Answers

Categories