Convert date

QC20N
QC20N used Ask the Experts™
on
Hi.

I have excel document where the name of the sheets is the date the sheet was created. But some of the sheets have a date format like this: dd/mm/yyyy and I want it like this: yyyy/mm/dd. The name of the sheets in a Listbox is added by using ADOConnection.GetTableNames(ListBox1.Items,false);

How can I convert the dateformat to the right one?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Geert GOracle dba
Top Expert 2009

Commented:
in delphi simply use FormatDateTime
FormatDateTime( 'yyyy/mm/dd', DateTimeValue)

can you actually use slashes in the file name, if not :
> FormatDateTime('yyyymmdd', DateTimeValue)

excel ???

Author

Commented:
Yes, but how? :)

If you see in line 15 I add TableName to the listbox.

procedure TFrmMain.BtnADOConnectXLSClick(Sender: TObject);
var strConn :  widestring;
begin
  strConn := 'Provider=Microsoft.ACE.OLEDB.12.0;' +
             'Data Source=' + 'C:\path\Project Pro_SE.xlsx' + ';' +
             'Extended Properties="Excel 12.0 xml;HDR=YES";';
  AdoConnection1.Connected:=False;
  AdoConnection1.ConnectionString:=strConn;
  ADOConnection1.CursorLocation := clUseServer;
  ADOConnection1.LoginPrompt := false;
  ADOConnection1.Mode := cmShareDenyNone;
  ADOConnection1.Provider := 'Microsoft.ACE.OLEDB.15.0';
  try
    ADOConnection1.Open;
    ADOConnection1.GetTableNames(listbox1.Items,false);
  except
    ShowMessage('Unable to connect to Excel, make sure the workbook exist!');
    raise;
  end;
  AdoQuery1.CursorLocation:=clUseServer;
end;

Open in new window

Geert GOracle dba
Top Expert 2009

Commented:
how did the tablenames get in there ?

if you don't change them at that level  you'll need:
A > a translation from the orginal format to your new display format
B > that filename won't exist anymore, so you can't do anything with it in the next step

change the original filename formats by setting up a rule when entering the file names.
Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Geert GOracle dba
Top Expert 2009

Commented:
do you have some samples of correct and wrong filenames ?

without those samples,  it's guessing at what the interpretation converter should do

Author

Commented:
http://www.experts-exchange.com/Programming/Languages/Pascal/Delphi/Q_28363030.html

Just below the accepted solution in the link there is file. If that is was you are asking. :)
Oracle dba
Top Expert 2009
Commented:
well that's basically swap-a-little
if the first '-' is at position 5 then it's ok, otherwise swap

besides your format is not with slashes but with minusses !!!

TempList := TStringList.Create;
try
  ADOConnection1.GetTableNames(TempList,false);
  ConvertDateList(TempList);
  listbox1.Items.Assign(TempList);
finally 
  TempList.Free;
end;

Open in new window


procedure TFormXXX.ConvertDateList(List: TStrings);
var I: Integer;
  Temp: string;
begin
  for I := 0 to List.Count-1 do
  begin
    Temp := List[I];
    if Pos('-', Temp) <> 5 then 
    begin
      Temp := Copy(Temp, 7, 4) + '-' + Copy(Temp, 4, 2) + '-' + Copy(Temp, 1, 2);
      List[I] := Temp;
    end;
  end;
end;

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial