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

adx007Asked:
Who is Participating?
 
Olaf DoschkeConnect With a Mentor Software DeveloperCommented:
If I understand you correctly you also have cases of DBFs with with FPT and DBT files, then one of them surely is wrong. You should now really take the advice and read the first byte of each DBF to determine it's type. and see what is the right memo field of each DBF. The OLEDB provider is capable to adress different DBF types/versions with the same connection. This is something it does based on a per file access and does not need different connection strings for each type. But if you have FPT files in DBFs that should have a DBT then that's what's wrong and not the driver.

There are two types of connections you make, one to a folder of free tables, one to a DBC (vfp database). A DBC is is a free fox table and you can adress it as such with VFP, but it does have other file extensions and should rather be used by connecting as a database (see www.connectionstrings.com). a DBC also has a DBT and DCX file, and that DBT is also a memo field, but in FPT file format. Maybe you have a partly as DBF renamed DBC. The chek of the first DBF bytes will help find that out.

From here http://msdn.microsoft.com/en-us/vfoxpro/default.aspx go to the download named "Microsoft OLE DB Provider for Visual FoxPro 9.0 (SP2 update)" in the list of available downloads.

Use any hex editor to open the DBF files and be able to look at the first byte.

Bye, Olaf.
0
 
pcelbaCommented:
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?
0
 
Olaf DoschkeSoftware DeveloperCommented:
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.
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
adx007Author Commented:
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
0
 
pcelbaCommented:
You can rename the FPT file to DBT if it helps. Do you know the first byte value of the DBF?
0
 
adx007Author Commented:
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
0
 
tusharkanvindeCommented:
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.
0
 
tusharkanvindeCommented:
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
0
 
pcelbaCommented:
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?
0
 
adx007Author Commented:
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
0
 
pcelbaCommented:
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:
http://www.experts-exchange.com/Microsoft/Applications/FoxPro/Q_23099248.html

If everything fails then it should be possible to convert input data to another format. Visual FoxPro can do it easily.
0
 
adx007Author Commented:
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...



0
 
pcelbaCommented:
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.
0
 
Olaf DoschkeSoftware DeveloperCommented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.