We help IT Professionals succeed at work.

How to read a dbf file from Delphi 5 or above?

I 've tried to use the Class TADOConnection and set the ConnectionString Value to achieve this:

procedure TForm1.FormCreate(Sender: TObject);
var ChannelCount:integer;
tmpStringList: TStringList;
begin
ADOConnection1.ConnectionString := 'Provider=MSDASQL.1;Persist Security Info=False;Extended Properties="CollatingSequence=ASCII;DefaultDir=C:\Documents and Settings\victorltl2\My Documents\Borland Projects\Ant\Data\00_ANT;Deleted=1;Driver={Driver do Microsoft dBase (*.dbf)};DriverId=277;FIL=dBase IV;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=600;SafeTransactions=0;Statistics=0;Threads=3;UID=admin;UserCommitSync=Yes;"';
ADOConnection1.Open;
ADOConnection1.GetTableNames(tmpStringList, True); // this line always has runtime error
.......

My dbf file is located in the directory of 'C:\Documents and Settings\victorltl2\My Documents\Borland Projects\Ant\Data\00_ANT'
The file name is '1018_1_save.ant.dbf'

When the program run to the line of GetTableNames, the error message is :
'Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available.
No work was done.'

What I want is to simply read  the table header and all the content from
the dbf file and store it in TStringList. Except TADOConnection, can I use TQuery to help to do this?
Which one is better?

Any quick and easy sample Delphi code for reference?

Thx for attention.
Comment
Watch Question

I tested it out with following code, and the GetTableNames-function works :

var
  tmpStringList: TStringList;
begin
  tmpStringList := TStringList.Create;
  //ADOConnection1.ConnectionString := 'Provider=MSDASQL.1;Persist Security Info=False;Extended Properties="CollatingSequence=ASCII;DefaultDir=Y:\Software\Clipper\PERSPROG\DATA;Deleted=1;Driver={Driver do Microsoft dBase (*.dbf)};DriverId=277;FIL=dBase IV;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=600;SafeTransactions=0;Statistics=0;Threads=3;UID=admin;UserCommitSync=Yes;"';
  ADOConnection1.ConnectionString := 'Provider=MSDASQL.1;Persist Security Info=False;Data Source=Q_22149181';
  ADOConnection1.Open;
  ADOConnection1.GetTableNames( tmpStringList );
  showmessage( tmpStringList.Text );
  FreeAndNil( tmpStringList );



Make sure you do the following things :
1) Create an instance of your StringList before you do anything with it ( tmpStringList := TStringList.Create ).

2) I created an odbc-datasource (Q_22149181) that links to my director where the dbf-files are.
    I then referred to thos odbc-datasource from my connectionstring-property.


Can you try out my suggestion ?
I searched www.connectionstrings.com for other ways than odbc to connect to dbf-Files, and I succeeded to connect to the dbf-files without using odbc.

Now my code looks like beneath and it is still working.
Maybe this is a better solution for you then.

var
  strDBFolder: String;
  tmpStringList: TStringList;
begin
  strDBFolder := 'Y:\Software\Clipper\PERSPROG\DATA';
  tmpStringList := TStringList.Create;
  ADOConnection1.ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' +
                                      strDBFolder +
                                      ';Extended Properties=dBASE IV;User ID=Admin;Password=;';
  ADOConnection1.Open;
  ADOConnection1.GetTableNames( tmpStringList );
  showmessage( tmpStringList.Text );
  FreeAndNil( tmpStringList );


Best regards,

The Mayor.
Just replace
    strDBFolder := 'Y:\Software\Clipper\PERSPROG\DATA';
by your dbf-files location and it should work.

Is this what you were looking for ?

Author

Commented:
I found the procedure ADOConnection1.GetTableNames can work if I
1) change the dbf filename from
'1018_1_changed.ant' to '1018_1c.ant' (i.e. file name with two '_' characters are not allowed, shit)

2) set the Driver={Microsoft dBase Driver (*.dbf)}, instead of
Driver={Driver do Microsoft dBase (*.dbf)};

But I have another problem currently,
I cannot obtain the table header with the ADOConnection1.GetFieldNames

My current code is like , can anyone help?

procedure TForm1.FormCreate(Sender: TObject);
var
tmpStringList, tmpStringList2, FileList: TStringList;
sTemp, InputFileName, filePath_changed: string;
iCol, i: integer;
begin

ADOConnection1.Open;
tmpStringList := TStringList.Create;
tmpStringList2 := TStringList.Create;
FileList := TStringList.Create;


InputFileName := 'C:\Documents and Settings\victorltl2\My Documents\1018_1c.ant';
filePath_changed := ChangeFileExt(InputFileName, '.dbf');

if not RenameFile(InputFileName,filePath_changed) then
  ShowMessage('process ant file error');

  try
  ADOConnection2.ConnectionString := 'MSDASQL.1;Persist Security Info=False;Extended Properties="CollatingSequence=ASCII;DefaultDir=' +
              Copy(ExtractFilePath(InputFileName),1,Length(ExtractFilePath(InputFileName))-1) + //'C:\Documents and Settings\victorltl2\My Documents' +
              ';Deleted=1;Driver={Microsoft dBase Driver (*.dbf)};DriverId=277;FIL=dBase IV;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=600;SafeTransactions=0;Statistics=0;Threads=3;UID=admin;UserCommitSync=Yes;"';


ADOConnection2.GetTableNames(tmpStringList, True);
ADOConnection2.GetFieldNames(tmpStringList[0], tmpStringList2); //this line do not work
 ShowMessage(tmpStringList2.commaText);//  this line should return 152 column table header, tmpStringList2.commaText is empty  string

   ADOQuery1.SQL.Text := 'select * from ' + tmpStringList[0] + '';
    ADOQuery1.Open;
    iCol := ADOQuery1.FieldCount;  // strangefully, The program can detect the column count is 152 here

    while not ADOQuery1.Eof do
    begin
      for i := 0 to iCol-1 do
      begin
        sTemp := sTemp + ADOQuery1.Fields[i].AsString + ''#9;
      end;
      FileList.Add(sTemp);
      sTemp := '';
      ADOQuery1.Next;
    end;

  finally
    RenameFile(filePath_changed,InputFileName);
  end;

 
end;
This code works for me :

var
  strDBFolder: String;
  tmpStringList, tmpStringList2: TStringList;
  intX: Integer;
begin
  tmpStringList := TStringList.Create;
  tmpStringList2 := TStringList.Create;
  strDBFolder := 'Y:\Software\Clipper\PERSPROG\DATA';
  ADOConnection1.Close;
  ADOConnection1.ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' +
                                      strDBFolder +
                                      ';Extended Properties=dBASE IV;User ID=Admin;Password=;';
  ADOConnection1.Open;
  ADOConnection1.GetTableNames( tmpStringList );
  for intX:=0 to tmpStringList.Count-1 do
  begin
    ADOTable1.TableName := tmpStringList[ intX ];
    ADOTable1.GetFieldNames( tmpStringList2 );
    showmessage( 'Fields for table : ' + UpperCase( tmpStringList[ intX ] ) + #13#10 + tmpStringList2.Text );
  end;
  FreeAndNil( tmpStringList );
  FreeAndNil( tmpStringList2 );
  ADOConnection1.Close;


I made a little application which has following components on the form :
1 TADOConnection
1 TADOQuery
1 TButton
1 TEdit
1 TStatusBar

I only have code in the OnButton1Click-Event.
I made an export-function to easily export a dataset to a csv-file. Here is de declaration :
    function  ExportDataSetToCSVFile( DataSet: TDataSet ; ExportFileName, Delimiter : String ; AllowOverwrite, IncludeHeaderRow: Boolean ): Integer;



Here is my code for the OnClick-Event of the button :
procedure TForm1.Button1Click(Sender: TObject);
var
  strDBFolder: String;
  tmpStringList, tmpStringList2: TStringList;
  intX: Integer;
begin
  Screen.Cursor := crSQLWait;
  tmpStringList := TStringList.Create;
  tmpStringList2 := TStringList.Create;
  strDBFolder := Edit1.Text;
  ADOConnection1.Close;
  ADOConnection1.ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' +
                                      strDBFolder +
                                      ';Extended Properties=dBASE IV;User ID=Admin;Password=;';
  ADOConnection1.Open;
  StatusBar1.SimpleText := 'Getting TableNames ...';
  ADOConnection1.GetTableNames( tmpStringList );
  for intX:=0 to tmpStringList.Count-1 do
  begin
    Try
      StatusBar1.SimpleText := 'Getting FieldNames for Table ' + tmpStringList[ intX ] + ' ...';
      ADOConnection1.GetFieldNames( tmpStringList[ intX ], tmpStringList2 );
      StatusBar1.SimpleText := 'Exporting Data for Table ' + tmpStringList[ intX ] + ' ...';
      ADOQuery1.Close;
      ADOQuery1.SQL.Text := 'SELECT * FROM ' + tmpStringList[ intX ];
      ADOQuery1.Open;
      ExportDataSetToCSVFile( ADOQuery1, ChangeFileExt( tmpStringList[ intX ], '.csv' ), ';', True, True );
    except
      MessageDlg('Error on table : ' + UpperCase( tmpStringList[ intX ] ), mtError, [mbOK], 0);
    end; { Try Except }
  end; { for intX:=0 to tmpStringList.Count-1 do }
  FreeAndNil( tmpStringList );
  FreeAndNil( tmpStringList2 );
  ADOConnection1.Close;
  Screen.Cursor := crDefault;
  StatusBar1.SimpleText := 'Done.';
end;


function TForm1.ExportDataSetToCSVFile( DataSet: TDataSet; ExportFileName,
                                        Delimiter: String; AllowOverwrite,
                                        IncludeHeaderRow: Boolean): Integer;
var
  F         : TextFile;
  i         : Integer;
  strCsvRec : String;
  wrdRc     : Word;
  bmTmp     : TBookmark;
  blnIsOpen : Boolean;
begin
  { If ExportFile already exists, ask for overwrite-comfirmation. }
  if not FileExists( ExportFileName ) then
    wrdRc := 6 { 6 = mrYes }
  else if AllowOverwrite then
    wrdRc := 6 { 6 = mrYes }
  else
    wrdRc := MessageDlg('Er bestaat al een Export-File. Mag deze overschreven worden ?', mtConfirmation, [mbYes, mbNo], 0);

  { If no ExportFile exists or the user comfirmed the overwrite, start exporting }
  if ( wrdRc = 7 ) then  { 7 = mrNo }
  begin
    Result := -1;
  end
  else
  begin
    { The Result = Amount of exported records. }
    Result := 0;
    AssignFile( F, ExportFileName );
    Rewrite( F );
    Try
      With DataSet do
      begin
        {
        Keep Active-Status of DataSet. If it was closed when this fucntion was
        called, it should be closed too after the export.
        }
        blnIsOpen := Active;
        if not blnIsOpen then
          Open;
        bmTmp := GetBookmark;  { Save a bookmark to return to after exporting. }
        First;
        DisableControls;  { Be sure no visual controls are updates during export. }
        if IncludeHeaderRow then
        begin
          { Get Column-Name for each field and write CSV-Record (Header) }
          strCsvRec := '';
          for i:=0 to Fields.Count-1 do
            strCsvRec := strCsvRec + Delimiter + Fields.Fields[i].DisplayLabel;
          strCsvRec := Copy( strCsvRec, 2, 999 );
          Writeln( F, strCsvRec );  { Add the CSV-Record to the exportfile. }
        end;
        { For each record of the dataset, create a SCV-record an write it to ExportFile. }
        While Not Eof do
        begin
          {
          Build the Export-Record by adding each field of the DataSet and use the
          given Delimiter.
          }
          strCsvRec := '';
          for i:=0 to Fields.Count-1 do
            strCsvRec := strCsvRec + Delimiter + Fields.Fields[i].AsString;
          strCsvRec := Copy( strCsvRec, 2, 999 );
          Writeln( F, strCsvRec );  { Add the CSV-Record to the exportfile. }
          Inc( Result ); { Uodate Export-Record-Counter }
          Next;
        end; {While Not Eof }
        { Return to bookmarked record. }
        GotoBookmark( bmTmp);
        FreeBookmark(bmTmp);
        { Recover the Active-State of the dataset to its original state. }
        if not blnIsOpen then
          Close;
        EnableControls; { Re-enable all visual controls, linked to the dataset. }
      end; { With DataSet }
      CloseFile( F );
    Except
      Result := -2;
      CloseFile( F );
    end; { Try Except }
  end; { if not FileExists( ExportFileName ) }
end;


the application lookup all dbf-file in given dbfolder (Edit1.Text). It makes a csv-file (containing headerrow) for every found dbf-file.

On my system it works like a charm.
If it is not working for you, I guess it must have something to do with the file-names of your dbf-files.
I think you better avoid using '.' in the names of the files.

Author

Commented:
Thx for your help.
But I 've raised another question about dbf file reading.
http://www.experts-exchange.com/Programming/Programming_Languages/Delphi/Q_22152615.html

Read if you can help.