none
Adding column to a dbf file

    Question

  • IDE: Visual Studio 2005
    Language: Visual Basic

    Question1:  "How would I alter a table (specifically add a column) that already contains data?

    Question2:  "If I am unable to add a column to a dbf file that contains data, then how should I approach this problem?"

    Question3:  "Should I use an alternitive way to read and write information to a dbf file?  (I am looking the quickest way to process data.)"

     

    Situation:  I am trying to find away to quickly process information that is contained in a dbf file.  Processing consists of removing records, adding records, and adding fields to a dbf file.  Most important processing must be exceptionally fast.  For example:  I am currenly working on a way to programmically remove records that we consider to be "companies".  Since, we do alot of mailing we do not want our information to be mailed to other companiies, we just want to mail to residential addresses.  So, in this particullar case, I need to filter through all the records and compare them to our company names database and then remove all records that match.  Another Situation:  On some of the mailing we do, I need to programmically add a field, such as "winning numbers" to and exsisting DBF file that already contains data.

    Problem:  One problem I am currently experencing is inserting records into a dbf file.  The processing time is extremely slow, It might take me 7 minutes or more to insert a few hundred records into a dbf file(using sql queries).  This is not exceptable, since we are dealing with a large amount of data.  The data files we usually work with are around 5,000 to 200,000 records long.  This could be a logical problem.  I backup the dbf file, just incase the program crashes, I also copy  the dbf file and remove all records (I just need header information) because I need a new file just for company names. (the files I will be working with are the orginal file and the company file, and the backup file). I am importing the dbf file into a  dataset then I run a few functions to see if the fullname field matches company names in our database.  If there is a match then I remove the name from the original file, then I add a record to the company file.  Everything processes fine, except inserting records into the companies dbf.  Its my assumption that the problem that I am having is because the file is in a dbf format.  I have never experienced a problem like this in any other file format.  Could it be my connection string?  Should I try an alternative way to connect to data, maybe odbc?  I thought about adding a field to the exsisting table and process it that way, however, the program I am working on throws an oledb exception, and will not allow me to alter a table ( add a column) to a dbf file that already contains data. And this will be a problem in future projects.  I need a way to add columns to a dbf fie.  I know that fox pro has this capabilities.   Can I use a fox pro connection string to add this capability.  Our company does not like fox pro, and will not use it for any future projects. So here I am stuck between asphalt and a rock.  Any suggestions?  I will display some code that I think is related to this topic, if I need to post any more I will. 

    'connection string

    cs = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FilePath + ";Extended Properties=dBASE IV;User ID=Admin;Password="

    'alter table

    Dim sfile As New FileProcessing
    Dim qy As String
    Dim path As String = \\path

    qy = "ALTER TABLE 20368 ADD COLUMN winning char(5)"
    sfile.Open_File(path, qy, "DBF")

    'error exception
    Operation not supported on a table that contains data.

     

    Private Sub Processing()
    Dim qy As String
    Dim temp() As String
    Dim sName As String
    Dim sfile As New FileProcessing
    Dim dr As DataRow
    Dim sTemp As String = ""
    Dim item As String = ""
    Dim iLength As Integer = 0
    Dim sFullname As String = ""
    Dim match As Boolean = False
    Dim dc As DataColumn
    Dim int As Integer = 0
    Dim qyCompanies As String

     

    'back up original file
    File.Copy(SourceFile, DestinationFile)
    temp = txtSearch.Text.Split(
    ".")
    sName = temp(0)

    For i As Integer = 0 To tbFilter.Rows.Count - 1
    If dgView.Item(0, i).Value = False Then
    qy = "delete from " + sName + " where fullname = @fullname"
    Fullname = tbFilter.Rows(i).Item(1).ToString
    sfile.UpdateDBF(SourcePath, qy,
    "DBF", sFullname)
    End If
    Next

    'create companes dbf
    DestinationFile = SourcePath + sName + "c.dbf"
    File.Copy(SourceFile, DestinationFile)
    qy =
    "Delete * From " + sName + "c"
    sfile.UpdateDBF(SourcePath, qy, "DBF")

    'create companies query statement
    qyCompanies = "insert into " + sName + "c("
    For Each dc In ds.Tables(0).Columns
    sTemp = sTemp +
    "[" + dc.Caption + "],"
    Next

    sTemp = sTemp.Substring(0, sTemp.Length - 1) + ")"
    qyCompanies = qyCompanies + sTemp + "Values("
    sTemp = ""

    For Each dc In ds.Tables(0).Columns
    sTemp = sTemp +
    "@" + dc.Caption + ","
    Next

    sTemp = sTemp.Substring(0, sTemp.Length - 1) + ")"
    qyCompanies = qyCompanies + sTemp

     For i As Integer = 0 To tbFilter.Rows.Count - 1
    If dgView.Item(0, i).Value = False Then
    Dim itemArray(0) As String
    Dim itemp As Integer = 0
    For Each dr In tbFilter.Rows

    For int = 0 To ds.Tables(0).Columns.Count - 1
    itemArray(int) = tbFilter.Rows(i).Item(int).ToString
    ReDim Preserve itemArray(int + 1)
    Next

    Next

    sfile.UpdateDBF(SourcePath, qyCompanies, "DBF", itemArray, ds)
    End If

    Next

    File.Move(DestinationFile, CompanyFile)
    MsgBox(Now)

    End Sub

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

    Dim cs As String
    Dim cn As OleDbConnection
    Dim dc As OleDbCommand
    Dim temp As String = ""
    Dim size As String
    Dim i As Integer = 0

    Try

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

    temp = "@FULLNAME"
    size = sFullname.Length
    dc.Parameters.Add(temp, OleDbType.Char, size,
    "FULLNAME")
    dc.Parameters(temp).Value = sFullname
    dc.ExecuteNonQuery()

    cn.Close()

    Catch ex As Exception

    Throw ex

    End Try

    End Sub

     

     

     

     

     

     

     

    Saturday, September 16, 2006 7:51 AM

Answers

  • Jan,

    Thanks for the advice.  The way I ended up doing it was using  the .net binary reader and writer.  If the bytes that contained fullname information matched keywords that I have in my company database, I marked the record for deletion.  after processing the entire file I would back up the file (naming it companies dbf) then I would pack the original file ( which would remove all the records marked for deletion), and then I would processes the new companies file.  I un marked all records that were marked for deletion.  and then all the residents I marked for deletion, and then Packed it (which would removed all the resident records from the companies dbf file)  This process was very quick I was amazed how fast it was.  Basically what I ended up with was a file the contained all residents and then a file that contained all companies and of course a back up of the original file. 

     

     

    Saturday, November 04, 2006 1:07 AM

All replies

  • Hi Mike,

    Have you tried using the FoxPro and Visual FoxPro OLE DB data provider, downloadable from msdn.microsoft.com/vfoxpro/downloads/updates?

    Please realize that altering a Fox table requires exclusive use of the table.

    I love VB.NET as much as anyone, but it's a shame that your company doesn't like FoxPro. The type of data manipulation you're doing is about 100 times easier in FoxPro.

    Monday, September 18, 2006 2:10 AM
  • When adding a new column to a table that has data in a SQL or Ms Access you must specify that the new column is nullable.

    You may then populate data based on a query and then ammend the column definition.

    Monday, September 18, 2006 3:38 AM
  • Hi!

    Good thought. While Visual FoxPro format tables have the capacity to hold null values, earlier DBF versions can't accept null values. They all have a default value of zero, empty, etc. Since the OP has been using Jet to access his DBFs then he is using older-format files and specifying that a column is nullable is not allowed.

    Monday, September 18, 2006 10:01 PM
  • Cindy,

    I'm sure you are correct, I've seen the previous programmer at our company that used foxpro parse data quicker than a chef could slice an apple.  I think the biggest problems that we have experience with foxpro are a) the previous programmer b) the amount of stress the foxpro application put on our server.  c) Lack of foxpro experienced programmers in our area.  I can't go in depth on all three issues.  I just go by what I hear in our company.  When I introduced my my vision of what our dataprocessing department needs, our IT superviser applauded my ideals.  And no matter how many times I meantioned foxpro, IT responded with the same answer "They don't like it".  A good portion of the programs that are developed for our company are programmed using visual studio.

    I was even willing to learn foxpro, but my major concern is:  Where will foxpro be in the future?  Will I have just waisted my time learning a language that could be distinct?  Now I'm not an MVP or even a paid programmer.  I went to school for programming got a degree, studied an online course on VB.NET and read a few books on other programming languages, and I'm presently just trying to earn a few stars.  My point is, I can only analyse foxpro from the outside by looking in.  I hear what people say in our company, I visually looked at some foxpro code which some what looks out of date, and the amount of information available on foxpro is not ready available (Went to 3 different book stores in our community and did not find one book on fox pro).  I'm a trendy type of person, and I forsee foxpro being on a downtrend. 

    I'm sure you have a few comments like there are books available online, foxpro has a built in data engine, and so on.  But tell me, is it really worth leaning?  With the capability of the newer languages, an the empowering vision microsoft forsee's in the near future?

    Finally, and most important thanks for all the help.

     

     

    Wednesday, September 20, 2006 11:04 AM
  • 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

    Friday, November 03, 2006 12:50 PM
  • I have no experience with foxpro and .NET.

    1+2. alter a table that already contains data?

    • To avoid getting stuck in this problem: I assume it is not the table from which you delete all the records (create companies dbf), otherwise i would suggest adding the column after having deleted the data.
    • But if it is not that table and you must add a column but cannot because the table contains data: maybe add a second table with a one-to-one relationship to your first table. This table has the same primary key and the new column. then you could quiclke execut and insert statement with a select from clause. insert into tableB (field1, field2) select field1, field2 from tableA. After that you make select statements where the tables are joined.

    3. for quicker data processing:

    • instead of copying the table and then deleting the records, after having made the backup, maybe create a new blank table with same columns. Deleting records in dbf files maybe slow certainly when files are over network.
    • inserting records from other table in a loop: why not try to let the query statement do the insert as in my example in statement 1+2. Inserting data with select from another table will be handled faster by the database engine than by code loading all the records in memory and processing them one after the other.
    • Do not use redim preserve in your inside loop: this itemArray is not efficient, use a stringbuilder instead. and use the stringbuilder.Tostring method to pass it to UpdateDBF.
    • use .NET for stringconcatenation: instead of 'qrycompanies=qrycompanies+stemp', write 'qrycompanies &=stemp' , but stringbuilder might be faster again.

    Hope this has given you some food for thought.

     

    Friday, November 03, 2006 1:53 PM
  • Hi Mike,

    As I posted earlier in the thread, older FoxPro tables do not accept null values. They also do not have an Integer data type. When you specify that the column is an Integer data type and should accept nulls then the table structure changes from one that is able to be opened natively by Excel to one that is not. That's why you get the "not in a recognizable format" error message. Your users can open the tables in Excel or Access via ODBC. They could also open the tables in a third-party DBF viewer. A search on "DBF Viewer" brings up several that are not too expensive. 

    Packing the table has nothing to do with altering it. In FoxPro tables when rows are deleted they remain in the table but are filtered out. Packing physically removes the rows. Most Fox developers don't Pack their tables, except possibly as part of a weekly off-hours maintenance routine for very large tables.

    Friday, November 03, 2006 2:01 PM
  • Sorry, I miss read the posting.

    I don't know what is considered older Foxpro table (I'm assuming versions 3, and 4), but the DBF's that I am working with are generated from Microsoft access 2003.  Maybe I am using the wrong type of driver?  For the last 3 weeks I've been learning about the structure of a DBF file, is there something I can do in binary mode to solve the problem I am having?

    I really need this functionality because the user will need to quickly add a a few columns to a table for processing.   They also need the capability to open the dbf using Microsoft excel.

    I recently created a utility that reads a dbf file in binary mode and writes to it, and removes records,  but I do not have a problem opening dbf's using excel.

    Since, the column does not really need to be numberic I will try a different type.  Anyway, just like every weekend, I will be stuck behind the computer learning something new.

    Thanks,
    Michael

     

     

    Saturday, November 04, 2006 12:43 AM
  • Jan,

    Thanks for the advice.  The way I ended up doing it was using  the .net binary reader and writer.  If the bytes that contained fullname information matched keywords that I have in my company database, I marked the record for deletion.  after processing the entire file I would back up the file (naming it companies dbf) then I would pack the original file ( which would remove all the records marked for deletion), and then I would processes the new companies file.  I un marked all records that were marked for deletion.  and then all the residents I marked for deletion, and then Packed it (which would removed all the resident records from the companies dbf file)  This process was very quick I was amazed how fast it was.  Basically what I ended up with was a file the contained all residents and then a file that contained all companies and of course a back up of the original file. 

     

     

    Saturday, November 04, 2006 1:07 AM
  • Michael,

    You know the application best. I can only read and make some suggestions coming from my own experience on struggling with different database formats. So I am glad you have found a way to make it work and to make it work acceptably fast as well.

    Monday, November 06, 2006 11:53 AM
  • can any one pls help me this.

    i want to create dbf file with numeric datatype filed width 25 and decimal width is 8 means numeric(25,8)

    i used jet.oledb,vfpoledb  connection string but it not created this size.


    can anyone has any idea about this pls reply it's urgent..my client requirement is only this..

    if any one help me out this topic then i will help him any time and also inoney too..

    thanks and regard
    Wednesday, June 03, 2009 11:51 AM