FP 2.6 DBF file into Excel 2002 1

[wlpsyp]

Hello. I have a large (2Gb) 2.6 FoxPro database that contains several small databases and .dbf files within it. I have been instructed by the powers to be to have one of the .dbf's accessable using a hyperlink to my web page. What I am trying to get done and hope someone can help me with as I do not know much about this program is to maintain the FP database as is but have it also transfer over to and Excel Spreedsheet that can then be hyperlinked to. This database has changes done at least 5 times a hour and when the changes are made using Foxpro I need it to automatically/dynamically update the Excel file on the Hyperlink. Right now the way I have to do it and it is getting to lengthy of a process is to open the FP DB, save it as a Excel Sheet then put the Excel sheet on the web page. What this is is a DB that people schedule events and times and locations. The file does not have to be in Excel, it can be in any program that "normal" users will have installed on there machine that can access it i.e. Excel, Access(preferabley not), Word, Notepad etc. My back is against the wall on this one and not sure how to over come it. ANY and all help/advise is appreciated.

Thanks, Bill

 

[DSummZZZ]

There are several formats you can copy the file to, depending on your preference. I'm going to assume you don't know anything about Fox, so don't take it wrong if I go into too much detail.
Create a small routine named say, mycvrt.prg, by typing:

MODIFY COMMAND mycvrt

in the command window.

Enter the commands between the '*...' lines , substituting the correct file names and paths:

*....................................
USE MyTable1
COPY TO F:\SomeDir\MySheet TYPE XLS
CLOSE ALL
*....................................

Press Control+Enter to save and exit.
Now from the command window, say:

DO mycvrt

If you don't want to be prompted to overwrite the existing file, add this line at the beginning of the little program:

SET SAFETY OFF

Keep in mind, there is a record limit of around 16,000 for a Fox table exporting to Excel sheet. If you need more than that, you can export it to a text file which can be opened with Notepad, Word, whatever, by using:

COPY TO F:\SomeDir\MyFile SDF

in place of the other COPY TO command.
Dave S.

 

[wlpsyp]

Thanks Dave S.
Not sure I understand this fully as I am not programmer and do not pretend to be.... By doing this if I can figure it out, will it dynamically update the file every time there is a change to FoxPro DB? Also, there are 19 fields in this database, but I only want to "export" say 8 fileds to a Excel sheet can this be done as well?

In the FoxPro Database the file I am trying to dynamically update to Excel is called Request.DBF, it also has two files called Request.CDX and Request.FPT, these two files I am not sure what there function in life is....

So, to try and clarify (if needed) what I am trying to accomplish is....Have the 19 field Request.dbf file in FoxPro take 8 fileds and drop them into a Excel sheet (called say Scheduled.xls) while leaving them in FoxPro as well (CAN NOT alter or hender the real DB)and any time the Request.dbf file is updated through Foxpro 2.6 have it dynamically update the Scheduled.xls file. If I can get that done then the hyperlink/web page issue I can work out.

THANK you for all help and advice.

Bill

 

[DSummZZZ]

...will it dynamically update the file...
No, it doesn't establish any sort of connection to the Excel table. The easiest/quickest way to get it up and running may be to run it periodically, or put the program in a loop to update it automatically.

USE MyTable1
DO WHILE .T.
   WAIT WINDOW 'Waiting... <Esc> to exit, any other key to update now.' NOWAIT
   x=INKEY(300)   &&... 300 seconds = 5 minutes.  
   IF x = 27      &&... Escape key pressed
      EXIT
   ENDIF
   COPY TO F:\SomeDir\MySheet TYPE XLS
ENDDO
CLOSE ALL

...I only want to "export" say 8 fileds ...
Use this line for the copy command:

COPY FIELDS field1, field2, field3, ... ;
TO F:\SomeDir\MySheet TYPE XLS

Substituting the field names where appropriate, and separating each by a comma.
Dave S.

 

[wlpsyp]

Dave,

Thanks I am going to give this a try and see what happens. Hope I do not screw up the DB. I will post again after I try this. I have Visual FoxPro 6 (no, I do not know how to use it let alone use 2.6 as this is a program created by a governmental organization that is not longer around and I have to try and keep it working and functional) Would using FoxPro 6 make life any easier?

THANKS again,

Bill

 

[DSummZZZ]

For this little routine, no. They will behave the same.
However, VFP has a bunch of stuff that could take the file and send it out as HTML, XML, that sort of thing. And could check for table updates and export the file when they happen.
You could post your scenario in the "Microsoft: Visual FoxPro (versions 3.0 and higher)" forum but frankly, without having any Fox experience, you will have a lot of work on your hands getting the app to automate all that stuff.
Dave S.

 

[wlpsyp]

The file name is REQUEST.DBF, its location is G:\Requests

Excel sheet name is CURRENT.XLS located at G:\REQUESTS. I run the below file and get a "MEMO File is missing/Invalid" and can not go any further.


USE G:\REQUESTS\REQUEST.DBF
COPY to G:\REQUESTS\CURRENT TYPE XLS
CLOSE ALL

Ideas??

 

[wlpsyp]

Disregard that last post, stupid mistake, was trying to run a file outside of the FoxPro directory. It seems to work like a champ........

Have not tackled the Loop thing yet, but will give it a try. My next question of many (and hope no one minds, as I am really trying to learn this stuff, just above my head, never been a DB Guru) is I have the fields copying over, but is there a way to place the fields in a certain order when running th DO MYCVRT?

THANKS Dave S.

 

[DSummZZZ]

In answer to your previous question:

In the FoxPro Database the file I am trying to dynamically update to Excel is called Request.DBF, it also has two files called Request.CDX and Request.FPT, these two files I am not sure what there function in life is....

Request.CDX is an idex file used for rapid access of the .DBF records.
Request.FPT is memo file, one that stores variable length data from a "Memo" field. Now in order to use your 'Request' table, without getting into why, those files need to be located where the .DBF file is. Of course, there are ways around it but suffice it to say that for what you need, they need to be there. So if you're getting that error, are you by chance working with a backup of your original?

Now, on to something else. I'm feeling alittle silly right now, as I forgot all about a little routine in VFP. It's call GenHTML. If you open up VFP 6 where your Request table is, you can issue the following:

SELECT Field1, Field2, ...., Field8 INTO CURSOR tmp
DO (_GENHTML) WITH "C:\MyDir\OutFile.HTML", "tmp"

It will produce a browser ready html file.
Dave S.

 

[wlpsyp]

AND..... is there a way to rename the field during the DO MYCVRT from say Unit_Name to Organization?

 

[DSummZZZ]

You can arrange them in what ever order you want in the copy to line.
Renaming though is not doable.
You do have this option. Run a query on the table in the loop:

USE MyTable1

DO WHILE .T.
   WAIT WINDOW 'Waiting... <Esc> to exit, any other key to update now.' NOWAIT
   x=INKEY(300)   &&... 300 seconds = 5 minutes.  
   IF x = 27      &&... Escape key pressed
      EXIT
   ENDIF

   SELECT Unit_Name AS Organization, Field1, Field2 AS Whatever, ..., ;
      FROM Request INTO CURSOR tmp
   SELECT tmp

   COPY TO F:\SomeDir\MySheet TYPE XLS
ENDDO
CLOSE ALL

But, think about the other suggestion using VFP 6. You can still do the loop and have it directly export to HTML.
Dave S.

 

[wlpsyp]

Dave,

I would love to be able to use VFP 6. Because the HTML is exactely waht I am looking for at a end state, I have it installed, but that is it. I know nothing about it and do not want to pretend I do.

The sort order may be a challenge. What this is is a program to schedule ranges and Airspace for the U.S. Army. I need to place some of the information from when a unit schedules a piece of ground or air to show the powers-to-be in a HTML format or.... what is scheduled on a particular day and time and location and what that unit is doing with that piece of ground/air. The feilds MUST stay together for a particular record. What shows when I do the MYCVRT in Excel is Tra_Area (thats the ground/Air), STARTDAY (Day of starting), ENDDAY (Day it ends), TRAINING(what they are doing). If I sort this all of the fields for that day MUST coiencide. Do not see how to sort one feild and maintain its relationship with the other 4 or 5 fields.

 

[DSummZZZ]

Excel doesn't make it easy to do. However, Fox lives for that kind of stuff. You can create an index on a field, which effectively sorts the entire record based on the sequential order of the field you specify. In other words, say you have a record with Tra_Area, STARTDAY, ENDDAY and TRAINING, and you create an index on STARTDAY, the entire table will appear sorted on STARTDAY, with the corresponding fields in the correct places. I state this to contrast Excel, which as I'm sure you know, when you sort a column only that column gets sorted while the rest of the data stays put.
Since you said you already have a .CDX file, the index has probably been created for the table you are using. But right now, we don't really need to get into that.
Another way is to take the above mentioned query and gather the data sorted on the fly. For example, back to the mycvrt program:

USE G:\REQUESTS\REQUEST.DBF

DO WHILE .T.
   WAIT WINDOW 'Waiting... <Esc> to exit, any other key to update now.' NOWAIT
   x=INKEY(300)   &&... 300 seconds = 5 minutes.  
   IF x = 27      &&... Escape key pressed
      EXIT
   ENDIF
   SELECT STARTDAY, ENDDAY, TRAINING, ;
      Unit_Name AS Organization, Tra_Area ;
      FROM Request ;
      ORDER BY STARTDAY;
      INTO CURSOR tmp
   SELECT tmp
   COPY to G:\REQUESTS\CURRENT TYPE XLS
ENDDO
CLOSE ALL

As you can see, I selected the fields, and ordered them by startday. Then it gets copied to the output file.

Now I know I am criss-crossing forums here, but to do this same thing with VFP 6, fire it up and issue a

MODIFY COMMAND MyCvrt

just like with Foxpro 2.6, from the command window. Then add this code:

USE G:\REQUESTS\REQUEST.DBF

DO WHILE .T.
   WAIT WINDOW 'Waiting... <Esc> to exit, any other key to update now.' NOWAIT
   x=INKEY(300)   &&... 300 seconds = 5 minutes.  
   IF x = 27      &&... Escape key pressed
      EXIT
   ENDIF
   SELECT STARTDAY, ENDDAY, TRAINING, ;
      Unit_Name AS Organization, Tra_Area ;
      FROM Request ;
      ORDER BY STARTDAY;
      INTO CURSOR tmp
   
   SELECT tmp
   DO (_GENHTML) WITH &quot;G:\REQUESTS\CURRENT.HTML&quot;, &quot;tmp&quot;
ENDDO
CLOSE ALL

Dave S.

 

[wlpsyp]

Dave S. Thanks for this information. I am giving it a go right now. Since I want the final outcome to a HTML and have to use VFP, I will post a thread in the proper forum.
THANKS,
Bill