Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2007
    Posts
    4

    Unanswered: DBF file that changes daily

    Ok this may have been answered somewhere but to be honestly I don't even know what I need so its hard for me to search for it. Here is what I have going on lets see if anyone out there can answer this one. I have a 20 year old system that is tied to some lab equipment that we use for coal testing. The equipment runs back to an old win98 PC which is running a DOS program that has long since been abandoned by the manufactures. I can't upgrade the program because it will mean upgrading the hardware which is somewhere in the neighborhood of $100k at least. The program saves everything in what I believe is a dBase IV file (.dbf) and using Excel I can easily see the data inside. What I am trying to do, if I can figure out a way other than writing my own custom app, is to do a daily export/import of the data from the dbf file into a more usable database, then create some spiffy forms to mess with the data. Each day the dbf file is wiped out by the ancient program and a new one is created. My database experience is somewhat limited I have at best a rudimentary knowledge of SQL but I should be able to follow along even if I have to do a little research. Mainly what I am looking for is a way to import the dbf file into an already existing database (new or existing table) automatically via a script or something. I want to retain all the data from past days with out editing the the original DBF file. Can this be done or am I just asking the impossible?

    Thanks for your time,

    Donavan

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I believe DTS (Data Transformation Services) has a driver for DBase IV files. Especially if Excel is able to see the data inside. You should easily be able to set up a scheduled extract of all the data to SQL Server, Excel, flat files, or even another DBase IV file. The trick sounds as if it will be timing the extract to happen just before the wipe of the "old" file. I suspect there are historical files lying around, because a system with no history for reporting purposes is pretty rare. Still, if the system is 20 years old, they may have considered diskspace to be at a premium.

  3. #3
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    What are we talking about an IBM PC AT or IBM Model 50z ? I loved that Micro-channel architecture.

    I think they came stock with 20 or 30 MB hard drives.

  4. #4
    Join Date
    Dec 2007
    Posts
    4
    Mcrowely: Thanks for the insight any bits of suggested reading that would help me pull this one off?

    A little more explanation on the setup just in case this will help out any. The system was designed to run on the old IBM PC you know the dual 5 1/4" floppy drives with the whoppin 8088/8086 processor you know the old XT's. To the best of my knowledge the system works like this; there is a 16 bit ISA card that communicates "supposedly on a com port" but this is some proprietary craziness because it acts like a token ring over RS232. I have managed to get the card and program to work with Win98 but it totally craps out in Win2k or XP, I think this is because DOS directly accesses hardware where the NT kernel doesnt allow for this. The card is daisy chained to a number of other devices one of which is a proxanalyzer which is little more than a calculator with some RAM. Long story short this Prox stores and calculates all the data from the equipment in the lab. On the computer you run this old DOS program with a dBase backend (and I mean old it uses GWBASIC and access the .prg file directly [copyright 1986]) which downloads all the data from the prox. This program does little more than transfer the data and send it formated to a dotmatrix printer. The program was never meant to store data for long periods of time because it was designed to run from a floppy disk (lots of room there) and at the time networking machines was not something normally done. Normally you wouldn't need to store the data but someone in the highups decided that we needed to transfer it down to the other labs so they can monitor things. Through some batch file scripting I have managed to get the DBF to copy to a file thats name is created by the date. After this it wipes the old DBF file out and starts with a fresh one. The reason I have it do this is it far simpler to just kill the old file than to go through the program and purge all the records from the database.

    Basically what I am trying to do is take out all the time it takes to fill out all these extra forms we have to do every day and also give us a history of what we did. This way we don't have to go rifling through the filling cabinet if someone has a question about previous work we did. Plus I hate writing out all those forms thats why I learned to type.

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Sounds pretty torturous. It may be time to discuss replacing the whole ball of wax with the owners.

    Assuming that the owners take the usual line of reasoning (i.e. it is not bothering me too directly, so we will not spend money on it), then I would set up a new SQL Server box beside this existing pig. Windows 2003, SQL 2005, and all. I have no access to my test boxes, but SQL Server Integration services will be what you use from here on in (DTS was actually rewritten in the jump from 2000 to 2005). The concepts are largely the same, and I think they have retained the dBase IV driver. If not, you would have to retreat to SQL 2005, and DTS. Pick up a book on SSIS, and you should be well on your way. The nastiest trick may be accessing the dBase file from a remote machine. I forget if Windows 98 had the concept of a file share or not, but I strongly suspect it did. SSIS (and DTS) will be able to read a file across a network, provided the right permissions are in place. From there, the sky is pretty much the limit, as far as what you want to do with the data. This link should get you at least started, if not outright confused: http://msdn2.microsoft.com/en-us/library/ms141026.aspx

  6. #6
    Join Date
    Dec 2007
    Posts
    4
    Thanks for the info. I think I am going to take a slightly different route though I found a great little program (shareware) that will convert from DBF to SQL and can even be used in the command line. Only one real issue remains for me, thats the primary key. It is going to be a problem because from day to day there is no way to differentiate sample 100 on Monday from 100 on Tuesday unless I use the date too. I think I am looking at is a joined primary key (2 fields sorry if I get the name for this wrong). Is this going to be a problem when importing data? Seems to me once the relationships of all the table/records/fields are in place it shouldn't care.

    Here is a sample of the SQL that the previously mentioned program creates. The third value [7005] is the sample number and the next to last is the date [20071219]. Let me know if you can foresee any issues.

    INSERT INTO prox1_lab7 values ('02', '00', 7005, 28.6192, 29.6137, 29.6079, 30.5577, 31.5592, 30.7359, 0.0000, 0.0000, 0.0000, 1688.000, 2715.000, 2663.500, 0.0000, 0.9700, 0.9700, 5.01, 5.56, 16.90, 0.00, 0.00, 4.71, 11244.4, 0.58, 17.79, 0.00, 0.00, 4.96, 11837.5, 17.90, 0.00, 0.00, 4.99, 11907.0, 6.08, 14502.6, 0.00, 0.00, '20071219', 0);

    Also for anyone that may be interested the program that I found is called DBF to SQL and is by WhiteTown Software.
    I found it here http://www.exefind.com/dbf-to-sql-P5465.html

    And just for the record I'm not promoting it, I just found it to be useful for me.

  7. #7
    Join Date
    Jul 2007
    Posts
    96
    INSERT INTO prox1_lab7 values ('02', '00', 7005, 28.6192, 29.6137, 29.6079, 30.5577, 31.5592, 30.7359, 0.0000, 0.0000, 0.0000, 1688.000, 2715.000, 2663.500, 0.0000, 0.9700, 0.9700, 5.01, 5.56, 16.90, 0.00, 0.00, 4.71, 11244.4, 0.58, 17.79, 0.00, 0.00, 4.96, 11837.5, 17.90, 0.00, 0.00, 4.99, 11907.0, 6.08, 14502.6, 0.00, 0.00, '20071219', 0);

    I'm not a SQL Server guru, but from where I stand I don't see a problem with a 2 column primary key. Also, I was wondering why the values in bold are considered strings instead of a regular numeric datatype.

  8. #8
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    because the first 2 values would be 2 and 0 and not 02 and 00.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  9. #9
    Join Date
    Dec 2007
    Posts
    4
    Just for the record the '02' and the '00' seem to only be used by the originating program, they seem to have no correlation to the data that we use. As far as I can tell I don't even need it but I think it is going to be more trouble to get rid of it than it is worth.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •