asked on

Reading DBase 3 file with Visual Basic .net

My dbf database files are decoded correctly using the software "DBF Converter." However, when opening the files with MS Excel and MS Access, some of the fields of all the records are clobbered. The problem seems to be the format of some of the fields which Visual Basic is not decoding correctly. Question 1) is there software out there that will display all the details of the dbf file header and field descriptors? This should identify the problem format. 2)The files are good because DBF Converter converts them correctly to excel format. Is there a way to fix this problem with a different connection string? Or how? There has to be a way if DBF Converter understands the files.

Attached is a sample dbf file. The file extension has been changed to txt because ExpertsExchange does not allow upload of dbf extension. Save the attached file, then rename to class1.dbf Thanks in advance for your help!!

Private Sub buildLocalDB(ByVal connect As String)
        Dim cbCommandBuilder As New OleDb.OleDbCommandBuilder
 
        cnConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                "Data Source=" & sDb & ";Extended Properties=DBASE III"
        cnConnection.Open()
        daAdapter = New OleDb.OleDbDataAdapter(connect, cnConnection)
        cbCommandBuilder = New OleDb.OleDbCommandBuilder(daAdapter)
        dtTable.PrimaryKey = New DataColumn() {dtTable.Columns("NEWMLSNUM")}
 
        ' Create the UpdateCommand.
        Dim command As New OleDbCommand(connect)
        Dim updateComm As String = "UPDATE " & sName
 
        command = New OleDbCommand("UPDATE " & sName, cnConnection)
        command.CommandText = updateComm
        daAdapter.UpdateCommand = command
 
        daAdapter.Fill(dtTable)
 
etc

Open in new window

CLASS1.txt

Pavel Celba

Answers to your questions:

1) Yes, the software exists and its name is Microsoft Visual FoxPro
2) The file content is corrupted but it does not avoid its conversion to Excel format because the heading info is moreless correct but not as much as it should be.

The invalid contents seems to be starting at "floorcvr" column which in fact contains data visible split into three columns. similar problem is visible in several other columns. Let say these problems are acceptable.

BUT starting at "state" column the table contains data which neither correspond to column meaning nor to column data type:

State Sizetotal Zipcode
----------------------------------------
Co ntemporary
Co ttage/Bunga low
Ra nch

The reason is in column "Propstyle" which has zero length and is preceeding the "state" column. Many successive columns have incorrect data contents.

Also the problem could be in file type. The record size is 13000 bytes and I am not sure if dBase III allows such large record size.

So, you cannot solve this problem by different connection string, you cannot solve this problem by FoxPro, but you have to fix the DBF file itself. You have to create it again and correctly from its source.

Pavel Celba

The file attached to your question is definitely not dBase III file. The dBase III and IV maximum record size is 4000 bytes but your file structure shows cca 13000 bytes. Also the used OLE DB provider (Microsoft.Jet.OLEDB.4.0) is not the best one available. You should split your DBF file to several smaller files or you could define some larger columns as memo columns.

I would recommend to use Microsoft Visual FoxPro OLE DB provider but you should test its compatibility with your conversion programs.

First of all you have to fix your corrupted data.

BruceMendenhall

ASKER

P.C. I am back on ExpertExchange. When you access the file with FoxPro are you seeing the same problems? If so, then scratch using FoxPro. The converter program (copyrighted) is attached which never fails. I tried converting to dbase 4 with dbf converter, but same problem. I think the zero length fields are the problem based on your last email. opening dbf file with MS Access now and checking the data design for "propstyle"
dbfcnv.txt

Pavel Celba

The converter does not check DBF structure and column contents so strictly otherwise it would refuse your DBF as Access is doing when trying to import the table. If the converter does not recognize field size greater than 254 characters and leaves it unchanged then it is laying about conversion into dBase III/IV/FoxPro format.

Ask the DBF file vendor to follow several simple rules which are necessary for successful DBF file creation.

BUT it seems the invalid DBF structure is not a problem for your hack because it does not process the DBF as data table. Somebody who knows your data structures and who knows which info has to be placed where should look at the new DBF and change the offsets appropriately. If you are able to tell which columns from your CLASS1.DBF should be placed to which columns in your dtTable structure then it is feassible. I have not enough info to do it.

BruceMendenhall

ASKER

Attached: XBASE (DBASE) structure. (change .txt to .htm)
P.C., Possible breakthrough. DBF Converter says that there are several fields with width > 255 char. Run DBF Converter, then click the Columns button on the second (#2) screen, toward lower left corner. Check out all the wide (>255) fields toward the end of the list. DEC =1 if 256 to 511 and DEC =2 if over 512.
QUESTION: DBF Converter figures this out correctly. How can we modify the file so that MS VB understands it? What is the standard way to specify Wide fields?

Xbase-Data-file---dbf-.txt

Pavel Celba

Standard way of processing texts longer than 254 characters is MEMO field in dBase world.

You have several options now:
- Ask for DBF having long texts stored in MEMO fields
- Ask for text file instead of DBF
- You can write a simple conversion program in VB
- Some xBase compatible systems (I would say Clipper) recognize text fields up to 1024 bytes. You could convert the DBF in such system (theoretically).

Pavel Celba

Bruce, do you have some old DBF file? (Means the old structure which is acceptable by your VB and VB hack.)

BruceMendenhall

ASKER

P.C. here is in the old format, hacked and ready to go. The file has been truncated (full file is 74mb) This file worked fine with my software, even though the field descriptors were NOT changed by the hack routine, only the data portion of the file. Thanks, Bruce

class1old.txt

BruceMendenhall

ASKER

P.C. Please ignore the class1old.txt file sent above. I am working on getting you a good file. It may take 24 hours or more to recover a backup file, but I do have several. Thanks, Bruce

BruceMendenhall

ASKER

Here you go P.C. This is truncated to a shorter file from over 70 mb. NOTE! The hack routine only moved the aactual data. No change was made to the header or field descriptors. But it worked ok this way. Hope this helps.
class1.dbf.txt

ASKER CERTIFIED SOLUTION

Pavel Celba

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

BruceMendenhall

ASKER

P.c., I have decided to abandon this approach and forget about old dbase 3 formats and vendors who change their output formats every year or so. New approach will be to export MLS data to xls which most MLS's and public records providers already do legally. My new software will 1) input the two data sources, 2) create an exception file which details differences between the two data sources, 3) merge the sources based on some priority rules (tbd), 4) format the merged data into the requred appraisal forms formats.

Many thanks for your help P.C. The old approach is obviously too complicated and depends on faulty formats of others. The new sofware is going to kick a**. Kind Regards, Bruce

Pavel Celba

It will require some effort to finalize it but I can just say, your decision is logical and clever.

P.C.