?
Solved

Issue with opening dbf file with memo field

Posted on 2009-04-28
14
Medium Priority
?
3,358 Views
Last Modified: 2013-11-24
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

0
Comment
Question by:adx007
  • 5
  • 4
  • 3
  • +1
14 Comments
 
LVL 43

Expert Comment

by:pcelba
ID: 24255558
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
 
LVL 30

Expert Comment

by:Olaf Doschke
ID: 24255680
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
 

Author Comment

by:adx007
ID: 24256087
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
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
LVL 43

Expert Comment

by:pcelba
ID: 24256123
You can rename the FPT file to DBT if it helps. Do you know the first byte value of the DBF?
0
 

Author Comment

by:adx007
ID: 24256415
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
 
LVL 14

Expert Comment

by:tusharkanvinde
ID: 24257355
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
 
LVL 14

Expert Comment

by:tusharkanvinde
ID: 24257376
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
 
LVL 43

Expert Comment

by:pcelba
ID: 24258520
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
 

Author Comment

by:adx007
ID: 24260359
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
 
LVL 43

Expert Comment

by:pcelba
ID: 24262730
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
 
LVL 30

Accepted Solution

by:
Olaf Doschke earned 1500 total points
ID: 24267534
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
 

Author Comment

by:adx007
ID: 24271320
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
 
LVL 43

Expert Comment

by:pcelba
ID: 24271637
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
 
LVL 30

Expert Comment

by:Olaf Doschke
ID: 24273910
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Lync meeting or Lync conferencing is what many organizations would like to deploy to allow them save money. But companies are now giving up for various reasons, one of which is that they cannot join external meetings (non-federated company meetings)…
Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

840 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