I need to extract data from foxpro

[DarwinIT]

I have attached my files to Access but some of the fields are throwing errors. I suspect it is the numeric issue
(In FoxPro NUMERIC(5,2) allows a range of -99999 to 99999. In SQL Server using the same precision the range is -999.99 to 999.99. This was the only data type in our schema that needed to be altered at the data type level. So in SQL Server we had to use NUMERIC (7,2) to get the same range.)
I thought perhaps I could go into the foxpro database files and change the schema. Does that make sense - and if so - what is the best way to do this? I have VP 5 in my Visual Studio 5 ensemble. It is not installed (and I've never used it) Of course I have to have this data converted by Friday.
Thanks!!

 

[Mike Lewis]

Hi DarwinIT,

Just to make sure I've got this right ... You want to change the data type in the original Visual Foxpro table. Is that right?

If so, you have a couple of options. First, install Visual FoxPro. Then launch it. Look for the command window, which is a rectangular editing area, probably in the top right corner of the main VFP screen.

The first option is to type the following command into the command window. Type the commands one at a time. Each command will execute as you press Enter. If you make a mistake, click on the command, edit it, and press Enter again.

Type this:

USE MyTable EXCLUSIVE
MODIFY STRUCTURE

where MyTable is the fully-qualified name of the table (that is, with the full path).

This will bring up a window in which you can interactively change the schema. This should be fairly obvious. When you have finished, press Enter and accept the confirmation request.

Alternatively, type in the command window an ordinary SQL ALTER TABLE command. I assume you are familiar with SQL, so this should present no problem. The only thing to remember is that you again need to provide the full path name with the table name.

Whichever method you choose, be sure to close the table within VFP before you try to access it by any other means. To do so, either type the word USE in the command window (and press Enter), or just quit VFP.

I hope this does the job. Give it a try, and come back if you get stuck.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[craigber]

You may be able to use VFP 5.0. Later versions of VFP added additional field types that are not compatible with 5.0.

Craig Berntson
MCSD, Visual FoxPro MVP,

http://www.craigberntson.com/blog

 

[DarwinIT]

Actually it was really simple. I installed Foxpro and created a new project - went into Data and added a table - and voila - I have visual access to the schema. I changed the numerics to integer where the decimal count was 0 and to character where the decimal count was 2. I saved the files and then tried to link the tables to Access. All of the tables I have not tweaked are available to link - but the ones I changed don't show up. Would it be easy to dump the contents of the dbf files to csv files?
Thanks so much for your timely response. I was afraid I might not get any help until it was too late.

 

[Mike Lewis]

DarwinIT,

Good that you solved it. There are always several different ways to do things in Fox. You discovered one that I hadn't mentioned.

Re dumping the DBF to a CSV: Click on the table in the project manager, and then click the Browse button (this is just to make sure the table is open). Then close the browse window. In the command window, type COPY TO Somefile TYPE CSV

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[DarwinIT]

That didn't work. I consulted help and came up with a command that did work

COPY TO file DELIMITED WITH TAB

Worked like a champ. Thanks so much my Scottish rescuer!!
Cheers!
Don

 

[DarwinIT]

Dang. Got this all working and then discovered that #1- memo fields don't even export an empty field, must less the data and the browser in Visual studio just shows Memo Field instead of showing the data. How do I get the data for memo fields?

 

[Stella740pl]

You can try to use COPY MEMO command to output them to a separate text file. This is very raw, but you can see how it works.

SELECT MyFile
SCAN
   StrToFile(STR(IdField,6)+CHR(13)+CHR(10), "c:\MyPath\MyMemo.txt", .T.)
   COPY MEMO MemoField TO ("c:\MyPath\MyMemo.txt") ADDITIVE
ENDSCAN

 

[JRB-Bldr]

Alternatively, depending on how big the data is within the Memo fields, you can create one or more new fields and move the memo data into it prior to your COPY TO command.

Something like...

USE MyTable EXCLUSIVE
SELECT MyTable
* --- Add New Fields ---
* --- Or Do It Manually via MODI STRU in Command Window ---
ALTER MyTable ADD MemoFld1 C(200)
ALTER MyTable ADD MemoFld2 C(200)
ALTER MyTable ADD MemoFld3 C(200)
ALTER MyTable ADD MemoFld4 C(200)

REPLACE ALL MemoFld1 WITH LEFT(ALLTRIM(Memo),199),;
   MemoFld2 WITH SUBSTR(ALLTRIM(Memo),200,199),;
   MemoFld3 WITH SUBSTR(ALLTRIM(Memo),400,199),;
   MemoFld4 WITH SUBSTR(ALLTRIM(Memo),500,199)

* < --- Now Do The Export --- >

Good Luck,
JRB-Bldr

 

[DarwinIT]

Thanks everybody!!!