Altering a DBF File

  • Thread starter Thread starter JimmyKoolPantz
  • Start date Start date
J

JimmyKoolPantz

IDE: Visual Studio 2005
Language: VB.NET
Fox Pro Driver Version: 9.0.0.3504

Problem: I currently have a problem altering a DBF file. I do not get
any syntax errors when running the program. However, after I alter the

table and open microsoft excel to look at any changes; I get the
following error: "This file is not in a recognizable format" If I do
open the file in excel it looks like its not formatted.


Further Information: I am not sure if I need to pack the DBF after
altering it (add a column), however, I did pack it to see if it would
solve my problem but that did not work.


Why am I trying to view the file in microsoft excel?
More than likely the user will be periodically open the DBF in excel
for viewing.


Code: (note I did not paste all the code just code I felt was needed)


cs = "Provider=VFPOLEDB.1;Data Source=" + FilePath + ";Mode=Share Deny
None;Extended Properties="""";Exclusive=ON"


qy = "Alter Table " + fname + " ADD MILES INT NULL"


Public Sub UpdateDBF(ByVal FilePath As String, ByVal qy As String,
ByVal FileExt As String)


Dim cs As String
Dim cn As OleDbConnection
Dim dc As OleDbCommand


cs = Connection_String(FilePath, FileExt)
cn = New OleDbConnection(cs)
cn.Open()
dc = New OleDbCommand(qy, cn)
dc.ExecuteNonQuery()
cn.Close()


End Sub
 
Hi Jimmy,

I assume that if you open the target dbf in Excel before this code is
run, there are no issues; and that if you open the dbf in Foxpro after
this code has been run, there are no issues? Will the "after-dbf"
become Excel readable if you make other changes and save/save as from
within Foxpro?

It's been a while since I've used Foxpro; can you confirm that Foxpro's
Alter syntax doesn't require the COLUMN keyword:

qy = "Alter Table " + fname + " ADD COLUMN MILES INT NULL"

Regards,
Keith
 
I used the key word "COLUMN" and I get the same results.. the program
processes however, I am not able to view the dbf using excel when
finished processing.. it looks like unformated text.

I frequently have a hard time explaining myself and with not much
programming experience this could be very misleading.

Basically, what I am trying to do is to add a column to a dbf file that
already contains data. The dbf files were generated from microsoft
access 2003. I will not be using foxpro to process these dbf files.
However, through research, and try and error. It's my assumption that
the only driver that I can use to add a column to a dbf file is the
foxpro driver. I've tryed oledbf, odbc drives that are not foxpro and
I get an error. However, I did not use the keyword "Null" in my querry
statement. I might just try and see if a non foxpro driver will work
with the "Null" keyword.

Any advice would greatly be appreciated.
 
It could very well be that .net is causing the dbf to be unreadable by
excel, but .net and foxpro can still read it. I like Keith's idea about
altering the table from within foxpro, then see if it is readable by excel.

Which version of foxpro is the original table from? And which version
of excel are you using? It could be that an older table that is
readable by your version of excel is altered to become a newer version
that excel can no longer read. If you are using a new version of
foxpro, then it may also cause excel to fail in the read.

Just my 2 cents.

T
 
The DBF that I am working with was not generated from foxpro. We are
not using foxpro at work. We get our files in different formats (text,
xls, csv) and we import them into a microsoft access table, then run a
query in access, and then export them as dbf files.

I think I am going to put this project on hold for a few days. Does
anyone have any advise? If I can not add a column to a dbf file that
contains data then what are my alternative options?

Things I do know/need:

1. I need to add a column to a dbf file
2. The user needs to be able to open the file using microsoft excel
after the column has been added.
3. Creating the program in foxpro is not an option.
4. Processing needs to be fast.
5. The files need to be a dbf file not any other type of file.

The only solution I can think of now is to create a new dbf file in
binary mode with the added column and then write every record to the
file using the .net binary writer. However, I just don't see the logic
behind it. All that needs to be added is a new column. I'm looking
for professional advice on what I need to do now, I don't have the
experience to determine what is best, all I am doing now is basically
running in circles. Just to let you know I am not getting paid for
this program I am doing this for self knowledge. Nor, I am not asking
anyone to code this program, I am just looking for guidence.
 
As you may know there isn't a single ".DBF" format. Rather there are a few
slightly differing ones with the greatest difference probably being the
FoxPro versions. In order to add new features the various vendors who used
..DBF files would change the header record here and there. Other languages
and utilities (like Excel) check the format to various degrees and if it
can't recognize it then it declares it isn't dBASE or in some cases that it
is corrupt. What probably happened in the FoxPro case is that it created a
"FoxPro" .DBF file by adding a number of new fields in the header record.
This can be supressed (in FoxPro itself it is done by adding TYPE FOXPLUS or
TYPE FOX2X.

It could be that you can control the type and perhaps add the column when
you export it from Access, you might look into that. The other possibility
is that the FoxPro driver you are using in .Net supports the "unmodified"
..DBF format (check for a setting). Most probably the NULL option won't be
allowed as standard .DBF files have no support for NULL values. This is one
of those features added to FoxPro .DBF's that made them non-standard.

Lastly if you just want to open the file in Excel you might consider
outputting CSV files from Access. Is it important that it end up a .DBF
file? If so consider calling a short utility program (written in Clipper,
XHarbour, FoxPro or any other dBASE-compatible language) that will add your
column for you without modifying the header record unnecessarily.

Tom
 
Thanks Tom,

Yes, it is important that the file is a DBF file. The reason being is
that the primary application that we use to process files uses DBF file
format. So, the file format must be a DBF file.

I'm sure there are 3rd party applications that do what I want to do
such as xbase, however I dont have the money to go out and buy their
product. On the other hand, I have the mentallity if someone else can
do it then I can at least try and do it. I just haven't found out how
yet :).

Whats strange is I downloaded a dbf viewer, just to look at the file,
and I can view it after the column was added. Then I exported the file
as a dbf file just to see if I can open it in excel. I was able to
open it in excel, however, I noticed there was an additional column
added when I viewed it. The name of the column is "_NullFlags". Do
you know anything about this column? I looked at a DBF file structure
and did not see this mentioned anywhere.

Also, I just wanted to mention that adding a field to the dbf file
before exporting it from Microsoft Access is not an option. I know it
is easy that way, but the users would not go for it. Basically the
columns that I need to add are towards the end of processing.

Is there a specific postion that microsoft excel looks at to detemine
if the file is a dbase file? Is so then I could go out and modify that
position. Iol, Im sure its not that easy. I'm really starting to hate
dbf files. They have cost me so much time, and stress. I hope I
figure this out one day.
 
Not 3rd party applications... if you can write in VB.Net you can write a 20
line program to modify the structure of a .DBF file using say XHarbour
(which is free) and it will compile into a 32-bit .EXE or borrow a copy (or
have somebody write it) using FoxPro which will also result in an .EXE.
Come to think of it in either case you could generate a .DLL file instead.
There is also a scripting language version of XHarbour (XBScript I believe
it is called) which conforms to the MS scripting language conventions which
would work as well. That way you wouldn't even compile it, you can
view/edit the script with any text editor. None of these solutions cost
money, just time.

Of course it would be cleaner to do it in .Net directly. The _NullFlags
column is an indication that it isn't standard DBF format. As I mentioned
..DBF files don't understand the concept of NULL, empty fields are always the
empty equivalent of their datatype, e.g. "", 0, False. FoxPro introduced
all sorts of things to beef up the .DBF format I assume to confirm to SQL
and eventually .Net. I don't know which .DBF viewer you dl'd but it is
quite possible it just doesn't checks the header for the presence of things
it requires and ignores things it doesn't. In other words it won't display
the content of the _NullFlags column (if you display the structure) but it
doesn't care that it is present. I have no idea what Excel does to
determine if the .DBF file is intact but it could do a little or a lot.

If I had your problem I would begin by trying to determine if the DBF driver
has some sort of version or compatibility flag which you could set. If it
could produce a bare bones (dare I call it "standard") .DBF then you're home
free. Second I'd opt for the idea of passing it through a process I wrote
in a dBASE-compatible language and as I mention you could produce a .DLL so
you could it call it directly from your .Net program.

Third I might opt to produce the .DBF file manually which you mentioned in
one of your earlier messages. It isn't a binary format BTW. It's a text
file with a special header at the top followed by a series of fixed length
records. The structure is well documented and public knowledge.

Oh there is another easy solution. Is the resulting .DBF file a constant
format, in other words do you export a .DBF with a common layout and then
add a particular extra column to it? You can create an empty .DBF with the
structure you want (probably using the viewer you dl'd and if not then using
any number of free tools). This empty .DBF would have the structure you
want to end up with including the extra column(s). You export the one
without the column using Access and then open the template using .Net and
import the data from the Access .DBF. When you append records this way;
fields that aren't present in the source are just left empty in the
destination. At that point your data has the proper structure and of course
you can fill the new field under program control once the import is done.

I wouldn't be too hard on the .DBF format. It isn't ideal but then it was
designed in the late 70's and nobody figured it would be opened using .Net
and Excel at that time :-) There is no central authority to improve it so
everybody went their separate way but unfortunately they kept the same
".DBF" extension. The dBASE index files and memo files have undergone
customization as well but in those cases the companies actually did change
the extension so there aren't quite so many problems.

Tom
 
Hi Jimmy,

Neither an Integer data type nor Null support is compatible with the older
DBase IV DBF format that is compatible with Excel. This is the same format
that is used in FoxPro 2.6 for Windows.

These features were added to the FoxPro DBF format in Visual FoxPro 3 and
are only accessible in Excel via ODBC.

Use a numeric data type: "Alter Table MyTable Add Column Miles N(6, 0)"
 
Hi Tom,

This is exactly the case. Only FoxPro 2.6 and earlier tables are readable
natively by Excel. The Integer data type and Null support were added in
VFP3, and adding them changes the table structure to a Visual FoxPro "free"
table. Tables in this format are only accessible via ODBC.

To go further, even more data features were added in Visual FoxPro 7 and
above. Tables with these features are no longer ODBC compatible; they must
be accessed via OLE DB.
 
Back
Top