Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2007-02-04
7
Medium Priority
?
7,020 Views
Last Modified: 2013-11-24
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.
0
Comment
Question by:lamvictor
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
7 Comments
 
LVL 6

Expert Comment

by:wimmeyvaert
ID: 18466116
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 ?
0
 
LVL 6

Accepted Solution

by:
wimmeyvaert earned 1500 total points
ID: 18466136
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.
0
 
LVL 6

Expert Comment

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

Is this what you were looking for ?
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:lamvictor
ID: 18466161
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;
0
 
LVL 6

Expert Comment

by:wimmeyvaert
ID: 18466193
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;


0
 
LVL 6

Expert Comment

by:wimmeyvaert
ID: 18466387
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.
0
 

Author Comment

by:lamvictor
ID: 18483083
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.

0

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
Viewers will learn how to maximize accessibility options in an Excel workbook for users with accessibility issues.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

704 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