DateTime import to Excel Buffer table

MatStephensSandA

Hi clever people..

I'm writitng an import process for a customer.
Basically they get a load of excel sheets from their suppliers and they import them into Nav. I have created a process that does this and all works well.
However they now want to include a previously ifnored field from the sheets. Teh field in question is a DateTime on the spreadsheets.

The trouble is the import process (as used in the Readsheet function in Excel Buffer) appears to dropp off the time part of the date time field.

My processing report calls the follwoing

ExcelBuf.OpenBook(File,Sheet);
ExcelBuf.ReadSheet;

This then inturn calls the following code

Window.OPEN(
  Text007 +
  '@1@@@@@@@@@@@@@@@@@@@@@@@@\');
Window.UPDATE(1,0);

DELETEALL;
XlRange := XlWorkSheet.Range(GetExcelReference(5)).SpecialCells(11);
Maxi := XlRange.Row;
Maxj := XlRange.Column;
i := 1;
REPEAT
  j := 1;
  VALIDATE("Row No.",i);
  REPEAT
    VALIDATE("Column No.",j);
    "Cell Value as Text" := DELCHR(FORMAT(XlWorkSheet.Range(xlColID + xlRowID).Value),'<',' ');
    IF "Cell Value as Text" <> '' THEN BEGIN
      INSERT;
    END;
    j := j + 1;
  UNTIL j > Maxj;
  i := i + 1;
  Window.UPDATE(1,ROUND(i / Maxi * 10000,1));
UNTIL i > Maxi;
XlWorkBook.Close(FALSE);
XlApplication.Quit;
CLEAR(XlApplication);
Window.CLOSE;

As far as i can see the
"Cell Value as Text" := DELCHR(FORMAT(XlWorkSheet.Range(xlColID + xlRowID).Value),'<',' ');
is dropping the date part of the data.

I dont know if the Format is dropping this or if the actual "value" being passed is missing the time??

any Ideas??

vijay_g

The problem is in Time data type and even if you have separate column for time the problem still same i don't know why, but if you will convert your datetime in text(add ' single quote in starting) in excel then system accept it.

Did you try to copy the cell that has datetime value in excel and get the value to paste this in text file.

MatStephensSandA

Hi Vijay.

Thanks for the reply..
Copying from excel to Notepad creates the desired result ie. 11/06/2012 06:02.
Reformatting the sheets is not really a possibility as they are generated from many vendors all over the world (using a none Navision systems). So to get my customer to reformat over 100 excel sheets each day is somewhat time consuming.

Thanks
Mat