.NET Forum / Languages / VB.NET / November 2006
Altering a DBF File
|
|
Thread rating:  |
JimmyKoolPantz - 03 Nov 2006 21:20 GMT 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
kgerritsen - 03 Nov 2006 22:05 GMT 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
> IDE: Visual Studio 2005 > Language: VB.NET [quoted text clipped - 37 lines] > > End Sub JimmyKoolPantz - 04 Nov 2006 14:07 GMT 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.
> Hi Jimmy, > [quoted text clipped - 53 lines] > > > > End Sub tomb - 04 Nov 2006 16:12 GMT 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
>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 [quoted text clipped - 78 lines] > > JimmyKoolPantz - 04 Nov 2006 22:57 GMT 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.
> 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 [quoted text clipped - 89 lines] > > > > Tom Leylan - 05 Nov 2006 00:19 GMT 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
> 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, [quoted text clipped - 116 lines] >> >>> >> >>> End Sub JimmyKoolPantz - 05 Nov 2006 01:22 GMT 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.
> 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 [quoted text clipped - 142 lines] > >> >>> > >> >>> End Sub Tom Leylan - 05 Nov 2006 04:07 GMT 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
> Thanks Tom, > [quoted text clipped - 199 lines] >> >> >>> >> >> >>> End Sub Cindy Winegarden - 05 Nov 2006 18:26 GMT 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.
 Signature Cindy Winegarden MCSD, Microsoft Most Valuable Professional cindy@cindywinegarden.com
> .... 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. > .... Cindy Winegarden - 05 Nov 2006 18:22 GMT 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)"
 Signature Cindy Winegarden MCSD, Microsoft Most Valuable Professional cindy@cindywinegarden.com
> qy = "Alter Table " + fname + " ADD MILES INT NULL"
Free MagazinesGet these publications absolutely FREE for up to 12 months. There are no hidden fees and no obligation. Simply choose a title, complete the application form and submit it. Read more ...
|
|
|