foxpro 2.6 Windows 2

[richmes]

I'm an admitted newbie to programing. I'm trying to create a table using the program, because I have several text files with numerous fields to convert. I have the field names, type and length ready to be pasted into a program, but need the beginning and ending syntax... ok I need it all. Anyone please?

 

[OnePunchMickey]

Ok, say you have

name (30 chars)
age (numeric, no decimals)
salary (numeric, 12 long with 2 decimals)
date of birth (date)
other details (memo)

then you might use:

create table mytable (name C(30), age N(3,0), salary N(12,2), dob D, other M)



***************************************
Need help running those old FoxPro apps on modern systems?

http://www.hentzenwerke.com/catalogpricelists/painlesse.htm

 

[JRB-Bldr]

While OnPunchMickey has exactly what you need to create the data table which will hold the field values, you mention a more complex issue -- "I have several text files with numerous fields to convert".

You will need to study the Foxpro Low-Level utilities in order to Open the text file, GET the data from the file into a string buffer, and process the data from the string buffer.

Use Foxpro's Help file to study how to use FOPEN(), FCLOSE(), FGETS(), FREAD(), FSEEK(), etc.

Good Luck,


JRB-Bldr
VisionQuest Consulting
Business Analyst & CIO Consulting Services
CIOServices@yahoo.com

 

[DSummZZZ]

You can create the table as mentioned earlier:
create table mytable (name C(30), age N(3,0), salary N(12,2), dob D)

Then use:
APPEND FROM MyFile.TXT SDF

Where MyFile.TXT is the name of your text file.
See if that throws your data in the table in the proper format. If not, maybe change data types, and try again.

MODIFY STRUCTURE MyTable

Change the structure, then issue the command:
ZAP

ZAP will totally remove all records though so use it carefully.


-Dave S.-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[richmes]

What a great site, I'm going to use all recommendations. Thanks to all!

 

[richmes]

All recommendations were great. Have a snag however, seems to max at 60 fields (total 242 bytes). Is that possible? Is there a work-around?

 

[rgbean]

No, the maximum number of fields is 255 and 65,000 is the maximum total characters in a record. However, the maximum size of a command string is 2,048 - this is probably the limit you are running into. In this case you can't use the SQL CREATE TABLE syntax - you have to resort to CREATE TABLE FROM ARRAY syntax. While it's a bit more "wordy", at least you can create tables with more fields.

So instead of:

create table mytable (name C(30), age N(3,0), ;
  salary N(12,2), dob D, other M)

* you need 
DIMENSION aMyTable[5,4]
aMyTable[1,1] = "name"
aMyTable[1,2] = "C"
aMyTable[1,3] = 30
aMyTable[1,4] = 0
aMyTable[2,1] = "age"
aMyTable[2,2] = "N"
aMyTable[2,3] = 3
aMyTable[2,4] = 0
aMyTable[3,1] = "salary"
aMyTable[3,2] = "N"
aMyTable[3,3] = 12
aMyTable[3,4] = 2
aMyTable[4,1] = "dob"
aMyTable[4,2] = "D"
aMyTable[4,3] = 8 && doesn't really matter
aMyTable[4,4] = 0
aMyTable[5,1] = "other"
aMyTable[5,2] = "M"
aMyTable[5,3] = 10 && doesn't really matter
aMyTable[5,4] = 0
CREATE TABLE myTable from ARRAY aMyTable

Rick

 

[infinitelo]

if your approaching the limits, you should reconsider you overall approach to dbf's. you most likely have information that should be in a seperate table and lots of repeated values. instead of city,state, you should have a zip code table seperate, and look it up.
when dealing with text files i found. it they are less than 255 char wide ill append them into a general dbf
line = 255 then parse out data and insert it into what ever destination is appropriate.


if it is to be it's up to me

 

[richmes]

ONEPUNCHMICKEY AND RGBEAN, AMONG THE OTHER SUGGESTIONS, ARE THE APPROACHES I AM ATTEMPTING FOR MY SCENARIO. BUT I WONDER, GIVEN THE STRING LIMITATIONS I AM FACED WITH, IF THERE IS A WAY TO APPEND FIELDS. IN OTHER WORDS, CAN I CREATE A TABLE WITH THE MAX FIELDS ALLOWABLE, THEN IN A SEPARATE STEP APPEND THE REMAINING FIELDS INTO THIS TABLE?

 

[OnePunchMickey]

No ... you can't cheat like that! You may need to look at how your data is organised.

***************************************
Need help running those old FoxPro apps on modern systems?

http://www.hentzenwerke.com/catalogpricelists/painlesse.htm

 

[rgbean]

Richmes,
If you really can't live with the "table" approach I detailed above, there are a couple alternatives.
1) Upgrade to VFP 8.0
a) It has a limit 4 times greater (8,192) for the command line.
b) It supports an APPEND fields to table and ALTER field definiton syntax (like most larger databases!).

2) I've used a technique to add fields to the beginning and/or end of table:
e.g.

SELECT *, ;
   SPACE(20) as newChar, ;
   {  /  /  } as newDate, ;
   0000.00 as newNum, ;
   .F. as newLogic, ;
 FROM OldTable ;
 INTO TABLE NewTable

This takes all the current fields and adds new fields with the specied values as defaults. Obviously you can add enough additional fields as a 2,048 command line will hold. Be careful to specify the exact size you want as the constant - you can't use the more straight forward C(10), D, N(7,2), L, etc. To create a memo field takes a bit more work but it can be done (hint: create a temp cursor and use the UNION option!).
Note: You'll need to close the files and rename all the required file type (.DBF, .CDX and/or .FPT) to get the table names correct.

Rick

 

[richmes]

Great rgbeab and all thanks again!