none
Can I modify the .dbf file (DOS)

    Question

  • We have one program using FOXPRO for DOS version unknown written in the mid 80s.

    (yes we're looking for a replacement)

     

    BTW, the ONLY programming I have ever done is VBA for Excel, and the only thing I know about FoxPro is how to spell it.

     

    meanwhile we need to change a lot of the data

    my thought was to open the .dbf file in Excel 2000 or 2003, change the necessary data and resave as .dbf

    (apparently there are some long characters in FoxPro that Excel can't read, so may this isn’t an option)

     

    when you open the program the files are listed in alphabetical order

    when we opened the program using the modified .dbf file they were scrambled.

    And we received some error messages listing formulas.

    The data that we changed were NOT data that was created via a formula, but this data is used in a formula to calculate the total.

     

    Is there any option for changing the data in the database outside of FoxPro?

    Or is there a program I can buy to enable me to do this?

    (My other option is to manually open each record in the program change the data and initiate recalculating the total.)

     

    Thank you

    Wednesday, March 26, 2008 2:12 PM

Answers

  • Well nice theory but you can't be sure.

     

    1) Storing as 2 fields (Price,Markup). Getting new price doing a simple calculation like (your theory):

     

    Price * 100 / (100 + Markup) * m.newMarkup

     

    and storing the new Price along with new Markup back to table.

    Looks like:

     

    12.30, 23 (stored)

    25 (new value given and recalculate selected)

    12.50 , 25 (stored back)

     

    2) Or as many database would do, storing as 2 fields (Price,Markup) and storing only the new markup back to table.

    Looks like:

     

    10,23 (stored)

    25 (new value given and recalculate selected)

    10, 25 (stored back - no replacement on base price)

     

    IOW always base price and markup is stored and whenever needed it's displayed via a calculation at runtime:

     

    Price * (100 + Markup) / 100

     

    Old foxpro tables can be seen and updated in Excel. However when doing that if you use a wrong driver than you may end up with a corrupted table. If you would do that then use drivers from msdn.microsoft.com/vfoxpro downloads section. For Foxpro 2.5 you may either use the ODBC or OLEDB driver (with OLEDB driver create a udl file in Excel using External data source).

    Be sure you're not storing the formulas but values.

     

    PS: Only the OLEDB driver + notepad is sufficient to edit foxpro 2.x tables but that requires some programming knowledge. There are many easy ways but all are for programmers. For non programmers there was a utility somewhere (but where). As I remember it was named something like dbfviewer. Search for it. It is an easy to use tool and better than processing with Excel.

     

    Wednesday, March 26, 2008 9:43 PM
  • Then Advantage Database driver would doSmile It works with even VFP9 tables (I'm still investigating what it offers in general as a C/S solution using DBF).
    Thursday, March 27, 2008 11:02 AM
  • Sunday, March 30, 2008 2:43 PM
    Moderator

All replies

  • It depends. Do you have a program written in FoxPro DO, probably v2.5 or v2.6, (just compiled FXP files ) or do you have Foxpro itself? Is it FoxPro for DOS or is it FoxBase? (even older)

     

    The order of the data you see is because the program uses index files (either CDX or IDX extension) besides the data (DBF extension and possibly other files like FPT for memo fields that hold variable-sized data.

     

    If you have FoxPro (or FoxBase) itself then it is easy and wen can give you some pointers. If you don't then it is more difficult as you have to open them with an external program and as you saw with Excel this may not work. Whatever you do or try, always remember to work on a backup of the files.

     

    Wednesday, March 26, 2008 3:52 PM
  • With only VBA for Excel as programming experience my suggestion is not to touch the data.

    You may try Access on a backup, it's easier and though limited is a database oriented tool.

     

    Wednesday, March 26, 2008 4:19 PM
  • my understanding is this is a custom program written in FoxPro v2.5

     

    I don't really want to program anything, all I want to do is change a couple fields in the database

     

    This database is a list of costs & prices.

    The first price ever calculated is the base for all future pricing.

    The way we increase the price is to add a % to the "markup" field

     

    What appears to happen is the "price" is divided by (1+ the previous markup), to get back to the base price and then mulitplied by (1+ the new markup).

    Consequently we have to compound the markup every time.

     

    For example:

    1990 price $10

    1991 price at 5% = $10.50

    1994 price at 12%   (or 7% more) = $11.20

    1998 price at 23% (or 11% more) = $12.30

     

    In order to get the new price to recalculate you must open each file and select "recalculate".

    which leads me to believe there is a recalc flag somewhere.

    And if there's not it probably doesn't matter because the math is still divide by the old and multiply by the new....unless the old is stored in duplicate somewhere else and why would you do that!

     

    Here's my theory:

    All the flags are currently set to "recalculated", as the user would never access & change a file without recalculating before closing.

    If I enter the calculated new price and the corresponding new markup directly into the database, all files should show the correct new price and the recalculate flag should still be set to "recalculated" so I will have updated thousands of prices directly in the database, rather than accessing and changing each one manually.

     

    I tried to test my theory by opening the .dbf file in Excel & changing it, but something went wrong.

    So I'm looking for another way to change the data directly in the database.

     

     

     

     

    Wednesday, March 26, 2008 6:22 PM
  • Well nice theory but you can't be sure.

     

    1) Storing as 2 fields (Price,Markup). Getting new price doing a simple calculation like (your theory):

     

    Price * 100 / (100 + Markup) * m.newMarkup

     

    and storing the new Price along with new Markup back to table.

    Looks like:

     

    12.30, 23 (stored)

    25 (new value given and recalculate selected)

    12.50 , 25 (stored back)

     

    2) Or as many database would do, storing as 2 fields (Price,Markup) and storing only the new markup back to table.

    Looks like:

     

    10,23 (stored)

    25 (new value given and recalculate selected)

    10, 25 (stored back - no replacement on base price)

     

    IOW always base price and markup is stored and whenever needed it's displayed via a calculation at runtime:

     

    Price * (100 + Markup) / 100

     

    Old foxpro tables can be seen and updated in Excel. However when doing that if you use a wrong driver than you may end up with a corrupted table. If you would do that then use drivers from msdn.microsoft.com/vfoxpro downloads section. For Foxpro 2.5 you may either use the ODBC or OLEDB driver (with OLEDB driver create a udl file in Excel using External data source).

    Be sure you're not storing the formulas but values.

     

    PS: Only the OLEDB driver + notepad is sufficient to edit foxpro 2.x tables but that requires some programming knowledge. There are many easy ways but all are for programmers. For non programmers there was a utility somewhere (but where). As I remember it was named something like dbfviewer. Search for it. It is an easy to use tool and better than processing with Excel.

     

    Wednesday, March 26, 2008 9:43 PM
  • Cetin,

     

    The downloads page at msdn.microsoft.com/vfoxpro no longer has a link to the ODBC driver. Just a note saying that it is no longer supported.

     

    FYI.

     

    Dan
    Wednesday, March 26, 2008 10:38 PM
  • Then Advantage Database driver would doSmile It works with even VFP9 tables (I'm still investigating what it offers in general as a C/S solution using DBF).
    Thursday, March 27, 2008 11:02 AM
  • Sunday, March 30, 2008 2:43 PM
    Moderator