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.. :'(



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

  while not eof do
    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;


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


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:

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

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.