Adding a field to a table though the command line

[Vatchunter]

We are writing a program to automate some setup files we use often. We would like to be able to add a field(s) to the table through the command line and accomplish the following:

Name the field
Set the data type
Set the field width.

Been trying to do this with an alter table command but it's not producing what we want.

SELECT 1
USE "d:\base.dbf" excl IN 0
ALTER table base add[column]Tommy c[20] null

Thanks for your suggestions!

 

[Mike Lewis]

Your aproach is basically correct, but there are a couple of minor syntax problems in your code. First, you don't wan't square brackets around the word "column". And you want round brackets rather than square ones around the field width.

So, instead of this:

ALTER table base add[column]Tommy c[20] null

do this:

ALTER table base add column Tommy c[b]([/b]20[b])[/b] null

In fact, you can leave out the keyword "column" completely and get the same result.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Vatchunter]

Thanks Mike! That's exactly what we needed!

 

[DSummZZZ]

While Mike has given the correct answer, let me elaborate a little.
The square brackets you are using you probably either in the 'Help' or the context tooltips when in the VFP command window. Those are square bracketed in both places to denote optional - not mandatory - command phrases in both instances.
However, when used in an actual VFP command, square brackets as are the same as using double " or single ' quotes. In other words, VFP considers whatever is contained in square brackets as a character string. That's why your command wouldn't work.



-Dave Summers-

Even more Fox stuff at:

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

 

[Vatchunter]

Dave you are correct we got our info on the ALTER function from the VFP Help, Thanks for the extra info concerning the use of square brackets!

 

[NasibKalsi]

Also, if you have to do it in a program, may be you will consider to verify the field(column) does not exist.

Nasib

 

[Olaf Doschke]

If you say the command line, do you use VFP itself, or just some tool like vFoxRun from ed leafe?

If you use the IDE you can CREATE COMMAND and then write a whole script. It's much easier to accomplish things this way, even just writing single commands, as you don't execute them by accident pressing ENTER, can write multi line statements, etc.

Bye, Olaf.

 

[Vatchunter]

The way we work is much different then most people would. Our main data is housed in a canned DB, and we have data that comes to us from outside sources. We have created programs in VFP that make it easier for us to evaluate and update our data. Once updated we import the data back to the canned DB. The purpose for this question, was to be able to add a field(s) to some source data in a setup program to prepare our source data for other programs to run. Mike's suggestion gave us the results we needed. I appreciate all suggestions to help us.

Thanks,

vatchunter

 

[Olaf Doschke]

So you repeatedly get DBF as files from outside sources and need to do the same ALTERation of these tables before further processing them? And those files might not all have all the fields you have? Then it would be even more important to not do this manually at the command windows, but add a further PRG in the processing chain or add this to the setup PRG.

Eg one way to always guarantee a certain data schema you need is have that schema as empty DBF as template and APPEND data from outseide sources. This way you catch a missing field, it'll just be filled with default values. What you can't automatically catch is non matching field names, additional fields you lack in your schema. You could at least easy the manual process of adapting the data to your needs by automatically finding differences. It's perhaps easiest to use AFIELD(laFields), LIST MEMORY LIKE AFIELDS TO FILE and then diff on the result text files of your input DBF and the DBF coming in from external.

Bye, Olaf.

 

[Vatchunter]

Olaf,

The support data comes to us in many formats, most we convert to a CSV file(s) to bring into VFP, they have pieces of the data we need, through the use ALTER TABLE statement we have been able to add the field(s) and format the data where needed with a program. This has saved time/work we had to do manually. We have talked about using templates and at some point I see us getting time to focus on that. You know how it goes, automate a process gives you time to focus on something else that needs done and then the light goes off that says I can do this with in my program...

Thanks,

vatchunter

 

[JRB-Bldr]

data comes to us in many formats, most we convert to a CSV file(s) to bring into VFP

I have had to do this numerous times for clients.

The way that I have found best was to do as Olaf has suggested and create a separate 'Conversion' program (or Form) that can be run by anyone and not requiring IT staff with specialized knowledge of data table structures to do the work.

For these situations I first created an Input file validation routine to confirm that the Input CSV (or TXT or DBF) file was legitimately formatted for an individual input source.
For CSV & TXT files this was most often done by validating the input file's Header row.
And if the input file were an XLS file, I'd use pretty simple Excel Automation to convert it to a CSV file first.

Then once validated it was easy to input the data into a standardized DBF file (a blank template - could be, if required, unique to each input source) with simple VFP commands such as APPE FROM (Inputfile) [whatever option needed].

Then with the data into a 'known' format (the previously blank template DBF), the data can be programatically moved and/or manipulated as needed.

Good Luck,
JRB-Bldr