How to import actual value, not formatted one from excel

I'm struggling with importing a date value from an excel file. Importing part works fine! The only problem is its format.

In the excel file, all the date values are typed in 'yyyy-mm-dd' format but the cell itself displays it as 'dd-mm-yyyy'. When my program imports values from the excel file, it imports dates as 'dd-mm-yyyy' not the format users actually typed in 'yyyy-mm-dd' - basically, formatted dates.

Well, I can manipulate 'dd-mm-yyyy' to 'yyyy-mm-dd' but would like to avoid this solution unless I really have no other ways to do it.

Is there any predefined delphi function to reverse 'dd-mm-yyyy' to 'yyyy-mm-dd'? Or are there any special ways to bring the actual unformatted raw dates, not the formatted ones?

Unfortunately, changing cell format in excel is not an option here.. :'(

Thanks.

Update:::

  ConnectionString :='Microsoft.ACE.OLEDB.12.0;'+
                     'Data Source='+filename+';'+
                     'Extended Properties="Excel 8.0;'+
                     'Imex=2;HDR=NO;;';
   }
  // IMEX = 0: Export mode, 1: Import mode, 2: Linked mode (full update capabilities)
  ADOQuery1.SQL.Clear;
  query := 'SELECT * FROM [sheet1$]';
  ADOQuery1.SQL.Add(query);
  ADOQuery1.open;

  i:=0;
  while not eof do
  begin
    i:= i+1;
    Cells[0,i] := IntToStr(i);
    Cells[1,i] := Fields[1].AsString;
    Cells[2,i] := Fields[2].AsString;
    Cells[3,i] := Fields[3].Value; // This is the date value I'm trying to import.
    Cells[4,i] := Fields[4].AsString;
    Cells[5,i] := Fields[5].AsString;
    Cells[6,i] := Fields[6].AsString;
    Cells[7,i] := Fields[7].AsString;
    Cells[8,i] := Fields[8].AsString;
    Cells[9,i] := Fields[9].AsString;
    RowCount := RowCount+1;
    next;
  end;

Answers


Instead of Field.Value, which returns a Variant representation of the date as a string (because you're assigning it to a string in Cells[]), use it as a TDateTime and convert it to the format you want it in.

Cells[3,i] := DateToStr(Fields[3].AsDateTime);

or

Cells[3,i] := FormatDateTime('yyyy-mm-dd', Fields[3].AsDateTime);

Based on your comments to my answer, it appears that the field doesn't actually contain a date, but contains a text value. In that case, you can try something like this:

try
  Cells[3, i] := FormatDateTime('yyyy-mm-dd', VarToDateTime(Fields[3].Value));
except
  raise Exception.CreateFmt('Unable to convert %s to DateTime', 
    [Fields[3].Value]);
end;

Need Your Help

C, unix and overwriting a char with write(), open() and lseek()

c file seek

I need to replace the a character in a text file with '?'. It's not working as expected.