Search code examples
delphiadodelphi-xedbase

Change column name of a dbf file

I have this items in my dbase file (.dbf)

INDICE  NOME    COR ESTILO  ESCALA
100     SAOJOAO      18      0,00

I need to change column name of INDICE to ID, so I use this code:

  while not ADOQuery1.Eof do
    begin
      Adoquery1.Edit;
      ADOQuery1.FieldByName('NOME').TEXT:= 'ID';
      Adoquery1.Post;
      ADOQuery1.Next;
    end;

When I run the above I get these results:

INDICE  NOME    COR ESTILO  ESCALA
 ID     SAOJOAO      18      0,00

Connection string used: Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277;Dbq=C:_workspace\projects\DBFEditor\te‌​mp

I have a system that need import dbf file and only recognize a file which have id column name.

Solution

  • The demo project below shows a way to do what you seem to want. I don't claim that it's the most efficient way or the best way, but it's probably as simple as you're likely to get.

    If you were wanting just to change the displayed name of a field in a Delphi application, for example in the column header of a DBGrid, you could do that by changing the DisplayLabel property of the field in question (AdoQuery1.FieldByName('INDICE').DisplayLabel := 'ID'), as I said in a comment earlier. But in your latest edit, it seems that what you actually want to do is to change the name of the INDICE column as it seen by a program reading the datafile to ID. To do that, you have to make an alteration to the on-disk structure of your .DBF file. This is what my code below does.

    It uses a User DSN set up for the MS ODBC driver for dBase files as the target of the AdoConnection's connection string.

    Ideally, I would have liked the find a flavour of the ALTER TABLE Sql statement which would simply rename the INDICE column, but the MS dBase driver doesn't seem to support that, because it generated an exception when I tried. So instead, my code works by making a copy of the table and its contents, with the INDICE column renamed to ID.

    In short, the program

    1. Creates a table MATest with a first column named INDICE and a couple of other columns and inserts a single row into it. This is just to set up a table to work from.

    2. Creates a second table MATest2 with the same structure as the MATest one, except that the first column is named ID rather than INDICE.

    3. Populates the MATest2 table by copying all the rows from MATest, using an INSERT INTO Sql statement.

    The important steps for what you want to do are carried out in the btnCreateTableCopyClick procedure. Note that you will have to comment out the first two lines, which drop the table MATest2 the first time you run the app, otherwise it will complain, cryptically, that MATest2 can't be dropped because it doesn't exist.

    I leave it to you to adapt the code as necessary to your data.

    Code:

    type
  TForm1 = class(TForm)
    ADOConnection1: TADOConnection;
    btnCreateSrcTable: TButton;
    ADOQuery1: TADOQuery;
    btnOpenSrcTable: TButton;
    DataSource1: TDataSource;
    DBGrid1: TDBGrid;
    DBNavigator1: TDBNavigator;
    btnDropTable: TButton;
    btnCreateTableCopy: TButton;
    procedure btnCreateSrcTableClick(Sender: TObject);
    procedure btnDropTableClick(Sender: TObject);
    procedure btnOpenSrcTableClick(Sender: TObject);
    procedure btnCreateTableCopyClick(Sender: TObject);
  private
  protected
  public
    procedure CreateSourceTable;
  end;

[...]

procedure TForm1.btnCreateTableCopyClick(Sender: TObject);
var
  Sql : String;
begin
  Sql := 'drop table MATest2';
  AdoConnection1.Execute(Sql);

  Sql := 'create table MATest2(ID int, AName char(20), AValue char(20))';
  AdoConnection1.Execute(Sql);

  Sql := 'insert into MATest2 select INDICE, AName, AValue from MATest';
  AdoConnection1.Execute(Sql);
end;

procedure TForm1.btnCreateSrcTableClick(Sender: TObject);
begin
  CreateSourceTable;
end;

procedure TForm1.btnDropTableClick(Sender: TObject);
var
  Sql : String;
begin
  //  Sql := 'drop table MATest';
  //  AdoConnection1.Execute(Sql);
end;

procedure TForm1.btnOpenSrcTableClick(Sender: TObject);
begin
  AdoQuery1.Open;
end;

procedure TForm1.btnCreateTableCopyClick(Sender: TObject);
var
  Sql : String;
begin
  Sql := 'drop table MATest2';
  AdoConnection1.Execute(Sql);

  Sql := 'create table MATest2(ID int, AName char(20), AValue char(20))';
  AdoConnection1.Execute(Sql);

  Sql := 'insert into MATest2 select INDICE, AName, AValue from MATest';
  AdoConnection1.Execute(Sql);
end;

procedure TForm1.CreateSourceTable;
var
  Sql : String;
begin
  Sql := 'create table MATest(INDICE int, AName char(20), AValue char(20))';
  AdoConnection1.Execute(Sql);
  Sql := 'insert into MATest(INDICE, AName, AValue) values(1, ''aaa'', ''vvv'')';
  AdoConnection1.Execute(Sql);
end;

    Obviously it would be better to generate your data with the ID fieldname in the first place and avoid all this, but presumably there is a good reason why you can't.