CODEWITHSUNDEEP
exceldelphiado
Raccoon
Raccoon

Reputation: 1427

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;

Upvotes: 2

Views: 2641

Answers (2)

Ken White
Ken White

Reputation: 125708

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;

Upvotes: 2

No'am Newman
No'am Newman

Reputation: 6477

I've always solved this problem using automation after all the data has been written

 xlApp.Columns['C:C'].Select;
 xlApp.Selection.NumberFormat:=  'yyyy-mm-dd';

Incidentally, I recently made the startling discovery that it's much faster to write the values to a stringlist, save that stringlist as a csv file then import that csv file into Excel instead of assigning values to excel cells as you have done in your example. Csv files obviously don't allow any formatting but you can do that after the data has been imported into Excel.

(I write startling as this may turn out to be something which is well known - but I've never seen it documented). I wrote about it here.

Upvotes: 1

Related Questions