Solved

Convert date

Posted on 2014-03-03
6
398 Views
Last Modified: 2014-03-03
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?
0
Comment
Question by:QC20N
  • 4
  • 2
6 Comments
 
LVL 36

Expert Comment

by:Geert Gruwez
ID: 39900417
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 ???
0
 

Author Comment

by:QC20N
ID: 39900430
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

0
 
LVL 36

Expert Comment

by:Geert Gruwez
ID: 39900443
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.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 36

Expert Comment

by:Geert Gruwez
ID: 39900451
do you have some samples of correct and wrong filenames ?

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

Author Comment

by:QC20N
ID: 39900463
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. :)
0
 
LVL 36

Accepted Solution

by:
Geert Gruwez earned 250 total points
ID: 39900667
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

0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
This video discusses moving either the default database or any database to a new volume.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now