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

Possible MySQL authentication issue with python package (PyMySQL3)

python mysql authentication python-3.x pymysql

I actually found a blog post that pretty much sums up my problem. Its at

How to get started with game programming on the Zune

xna zune xna-3.0

My zune just updated to 3.0 (didn't even realize they were releasing something new!) and the update came with two games, but the Zune marketplace does not have games.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.