asked on

Issue with opening dbf file with memo field

I am trying to open a dbf file with Memo field. The code works when the memo file is DBT extension and does not work if the memo file extension is FPT

if (oConnection == null || oConnection.State != ConnectionState.Open)
{
 
	oConnection = new OleDbConnection ("Provider=VFPOLEDB;Data Source=" + sDbaseFolder + ";Exclusive=Yes;Collating Sequence=machine;");
	oConnection.Open();
}
OleDbDataAdapter oDataAdapter = new OleDbDataAdapter("SELECT * FROM " +strBaseFile , oConnection);
try
{	
	DataSet oDataSet = new DataSet();	
	oDataAdapter.Fill(oDataSet);// errors here {@"Memo file d:\1r3o0om9.dbt is missing or is invalid." }
	oDataTable = oDataSet.Tables[0];
	return oDataTable;	
}
}

Open in new window

Pavel Celba

If the file is dBase IV DBF, the memo has extension DBT. FPT extension is used for FoxPro DBFs. The VFPOLEDB provider can recognize this detail. Structure of DBT and FPT file is different.

The first byte (byte 0) of DBF can tell what type it is:
0x02 FoxBASE
0x03 FoxBASE+/Dbase III plus, no memo
0x30 Visual FoxPro
0x31 Visual FoxPro, autoincrement enabled
0x43 dBASE IV SQL table files, no memo
0x63 dBASE IV SQL system files, no memo
0x83 FoxBASE+/dBASE III PLUS, with memo
0x8B dBASE IV with memo
0xCB dBASE IV SQL table files, with memo
0xF5 FoxPro 2.x (or earlier) with memo
0xFB FoxBASE
(source VFP Help file)

Just for information, what is your case?

Olaf Doschke

regarding pcelbas anser, this is normal and an indicator your DBF is not a foxpro DBF but an older format. Don't fix it, if it ain't broke, stay with the DBT extension.

Bye, Olaf.

adx007

ASKER

Guys,
The issue is that I have to process files where sometimes the memo is dbt file and other times its fpt. In the later case the application breaks. What can I do to solve the issue so that I can read dbf files with memo either in dbt or fpt format

Pavel Celba

You can rename the FPT file to DBT if it helps. Do you know the first byte value of the DBF?

adx007

ASKER

I tried renaming the file. Renaming did not work as these are two different kind of memo files...

I can check for the first byte, do you know if the connection string be different if the memo file is of one kind or ther other? I can certianing try with connection string being diferent

tusharkanvinde

Check sDbaseFolder to see if there is FPT or DBT file. In case there is FPT file, use the VFPOLEDB driver. Otherwise I guess you need a dBase driver but I don't know anything about that.

tusharkanvinde

Or open the DBF file and check the header first byte to decide which OLEDB driver to user. pcelba has given you the values you can expect there

Pavel Celba

The problem is the VFPOLEDB driver requests the DBT memo even if it works with FoxPro table. It also means it behaves like it should know dBase file formats... But that's probably not true and without some small sample files we cannot help. Could you provide them?

BTW, what is your VFPOLEDB.DLL version?

adx007

ASKER

I am using the VFPOLEDB (version 9 - latest) and then try to open the dbf file, if it fails than i retry opening the file with MS jet driver (version 4 - msjet40.dll). The file with fpt memo file does not work in either case.

What about DAO? Any experience. The issue is that the DAO on client machine may have different version and likely to get updated over the course of time anyways

I will prep a sample code snippet and associated files to try

Pavel Celba

It seems memo fields have problems in OLEDB. Some sources are saying "convert the memo to string" using the LEFT() but it is applicable up to 254 characters. The VFPOLEDB version should be 9.0.0.5815.

Looking at possible solutions, I've found several links:

This one reads DBF as binary file
http://stackoverflow.com/questions/428237/how-do-i-extract-the-data-in-a-foxpro-memo-field-using-net

Another (not so useful discussion) is here:
http://social.msdn.microsoft.com/Forums/en-US/visualfoxprogeneral/thread/547caf84-7279-4cef-8ab2-154dc699a6ae

And some hints could be here:
https://www.experts-exchange.com/questions/23099248/Why-do-I-get-Database-is-invalid-Errors.html

If everything fails then it should be possible to convert input data to another format. Visual FoxPro can do it easily.

ASKER CERTIFIED SOLUTION

Olaf Doschke

membership

This solution is only available to members.

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

Start Free Trial

adx007

ASKER

I have a break through finally... I am using a combination of of drivers that I am using to open/read the dbf data. Since this is a high volume (large number of files) that needs to be read, the minimal IO is the key. So reading the first byte to determine the choice of driver is what helped.

I am using by order of choice the following three drivers VFPOLEDB, MSJet40.dll and DAO ( for the cases where I have memo in FPT file). This was required as FPT file is a FoxPro table file and DAO is able to open the associated dbf file without an issue. DAO seems to perform okay so far.

Thanks for all the suggestions and any suggestions to make this approach work better, is appreciated.

I hate to have three drivers, but I guess there is no better wy around, unless I read the bytes and dig the data from the file. That would require intimate knowledge of the schema. And since I am consuming dbf files from thrid party applilcation (export), it would be a mainitenance nightmare if there is a change to the schema...

Pavel Celba

You should use Visual FoxPro next time. It reads all available DBF formats without problems for many years. All additional adapters, drivers, and libraries were created to suppress FoxPro usage for data processing... I don't think any other language can provide such easy and powerful tools for local data manipulations.

Olaf Doschke

I can second pcelba, because Foxpro is where most dbase and clipper programmers went and although it's a discontinoued product I think it handles most DBFs pretty well. There is old dbase stuff and clipper tables foxpro does not manage.

Such a bunch of different formats screams for a consolidation of the data, maybe put it to SQL server rather. The more valuable the data, the more valid that step would be, whatever that means in regard to applications working with all that data. It's not a situation that advances to the better in time, if nobody cares.

Bye, Olaf.